Aquifer.
Seriously... Learn to punch through them and they give you all the water you could want to play with. I found a nice aquifer in rock and carved bedrooms above it so that every room has a well
Same thing a few levels up provided me with a waterfall in the dining room.
You dispose of water afterwards by just dumping it into another aquifer
That was actually one of my biggest questions about plumbing was how to dispose of water after piping it around for power and waterfalls and all that. If I dumped it into the caverns it'd eventually just fill the cavern and someday flood my fortress, probably killing my FPS in the process, right?
When you say "dump it into another aquifier" are you actually talking about playing on a map with aquifiers on multiple levels? Most maps I've seen only have one aquifer layer reported on the embark screen -- sometimes I see maps with two or even three aquifier layers listed but they're almost always stuck right on top of eachother. I thought that just meant one big multiple-Z layer aquifer, and I figured that would be near impossible to punch through.
Are there maps that have other aquifer layers not listed on the embark screen much deeper down? Otherwise how do you dump back into an aquifer that's way up near the surface after piping the water deep underground for wells and waterfalls and mist generators and things? Use pumps to bring the waste water back up to the surface I guess? Sounds complicated.
Is there any other way to dispose of waste water safely without having it accumulate other than dropping it back into an existing river/brook to flow off the map? You can't create your own off-the-map disposal flow because you can't dig out the edge map tiles, right?
--nomad_delta