Water pressure is awesome. Yes, magma is fine and all, but the way water moves under pressure makes for big fun. At least to me; I'm a nerd.
I've lately adopted a sort of standard habit for all my forts; not sure if it'll last or I'll get bored with it or what. But I've been embarking near streams or minor rivers (or in a couple cases, a major river), damming them, and digging out a fairly large pressure column just downstream of the dam. Say a 3x3 or 5x5 pipe going straight down from the riverbed to the bottom of the map. I do this by digging up stairs on the lowest z-level, then up/down stairs the rest of the way. The inside of the pipe gets smoothed and fitted, then I channel out the stairs. The miners stand on the z-level below to channel out the stairs above them. It works well.
On each level I'll install a door or four, and just leave them. Once the dam is opened again and the pipe is filled (which takes like two seconds), I can come back later and dig horizontal pipes up to those doors, rig them with levers elsewhere, and open them to bring water to wherever I want it. Since it's under the pressure of the whole column, it fills pipes really quickly, and water can be piped upwards without pumps. I've had a little problem lately remembering to install pressure baffles where they need to go, but there haven't been any fatalities yet. I'm wimpy enough to built a floodgate-valved emergency release at the bottom level; I have a pipe that runs horizontally from the bottom of my water column to the nearest map edge, closed off with floodgates. Smooth and carve the map edge, pull the floodgate lever and the whole plumbing system drops out the bottom. It can take quite a while (i.e., a season or more) for elaborate horizontal plumbing networks to fully drain, but the point is the pressure is released immediately, so overflows can be interrupted before they become catastrophic.
The natural extension, of course, is to use a pump stack and a constructed pipe to extend this idea to a water tower that reaches all the way up to the sky. Given that a 15-level subterranean water column creates dramatic results when tapped on the bottom, a column that rises 15 levels into the sky would presumably produce dramatic results when tapped at ground level.
Of course, due to the way water flows vertically (it teleports rather than actually moving from tile to tile) it wouldn't be possible to blast orcs away from the fort with tons of pressurized water. More's the pity. But still, it would let you deliver a large amount of water to an enclosed space in a short amount of time, for whatever fun or amusing uses you might have for it.