I think this thread is worth reviving.
Currently (according to the wiki and the gamasutra interview) water never rises above the level of its source. In order to have realistic floods, dams, and other effects, this should change. If the wiki water pressure is correct, then DF already tracks water tile pressure. Instead of stopping when it reaches its source height, water should stop rising when it reaches 0 pressure. So far, nothing's different.
One other thing is water sources should have a pressure rating, in z-levels of water. The source could pressurize the water output tile up to the pressure rating, but no higher (if you let the rating be zero, this is the same as the current model as far as I can tell).
For screw pumps, this wouldn't have to change anything, but realistically screw pumps should be able to provide pressure. Maybe an option when you build, at a higher power cost. This lets you pump water up several levels with only one pump, but the power should probably still be 10 units per level (or maybe slightly less, or maybe it depends on part quality). If the water has nowhere to go and the output has reached the pressure rating, the pumping animation should stop. Maybe it should stop drawing power? That could create interesting logic possibilities.
For a flood, all that needs to happen is for the water source at the edge of the map to output pressurized water faster than it drains out the other side. Then the water will naturally overflow the banks and follow existing contours in the land.
To make dams work, rivers/brooks/streams should have a pressure rating above 0, but normally the flow would be fast enough that it never fills up. If you blocked the river, though, it would quickly pressurize and flow up.
Another cool thing would be if pressurized water started to eat away at soil/sand, so you wouldn't just get overflow, the banks would actually spread outward and eventually form a natural lake. To reach equilibrium (lake stops spreading) you'd probably have to deal with evaporation rates though. Maybe take away 1/7 from each exposed tile every so many ticks, based on temperature? Or have a temp-based chance of losing 1/7 every time you check for static/non-static?
But why stop there? I'll throw out an even more ambitious/useless/difficult idea. If you're pressurizing water in a cistern, particularly one with dwarf-made and/or one-tile-thick walls, there could be a pressure limit based on material before one of the walls bursts and water goes everywhere. This would segue in nicely with new cave-in code, since presumably material strength will be handled then somehow (and pressure is probably the most convenient way to quantify it). And if somehow you got pressurized water to freeze in such a vessel with nowhere to expand, you'd have an ice bomb.
TL;DR: Water should attempt zero pressure, not equalize height. Cake will be served. Thoughts?