There are several mechanisms at work that try to simulate water pressure in game. While on the whole amazingly accurate, there are several unexpected quirks concerning speed and displacement. Never think you are on the safe side, especially when trying to trick the game.
A technical explanation by Kanddak from the Bay12 Forum outlines in detail what is known of the in-game fluid mechanics from player testing.
 Hydrostatic water pressure
Dwarf Fortress attempts to replicate hydrostatic water pressure.
This is probably one of the most prominent components, as Toady discussed it at length in an interview with gamasutra.
In layman's terms, if you have a body of water that is higher than an open space below it (such as a tall tower full of water and a hose from it, or a lake over a mine), and an open route between them, then the water at the lowest z-level will be 'pressed' by the weight of the water above it.
As a simple model, think of a pipe shaped like a "J". If you pour water in the taller end of the pipe, it will come "up" out of the lower end until the water levels on both sides are equal. If you put your thumb over the lower end and fill the taller end, then release your thumb, the water will move with remarkable speed, and water will continue to come "up" out that lower end until all the water in the taller part is at the same level as the lower part. This is one part of Dwarf Fortress "water pressure" - that if the source is higher, water can come up stairs, up ramps, and over channel, and will continue flowing until it runs out of space or runs out of water above it.
Note that DF water pressure does not exactly match natural hydrostatic water pressure - it fills to a z-level one level lower than the source. (This is for reasons of CPU time-saving, as stated by Toady; the game stops not when all ends of the system are on the same level, but when the far levels are one-lower than the source.) The above behaviour does only apply to finite water sources like murky pools, artificially created reservoirs and any body of water connected to an infinite water source only diagonally. More to the point, it applies always, but is in many cases not the final mechanism causing equilibrium.
 Pressure from infinite water sources
Different from the above, a river that pushes water into a tunnel system will fill it up to the z-level of the river itself, but not higher (again, unless only connected by diagonal flow, see below).
When the water source is a river which is allowed to drain off the edge of the map, the final Z-level will never fill - however, if a dam prevents the river from draining, it will continue to fill up to its own Z-level.
 Other/Missing mechanisms
Dwarf Fortress does not model surface friction nor air pressure, so the water will not slow in transit nor will 'trapped air bubbles' form. Unless pumped, magma does not have pressure (it cannot flow up, and doesn't appear to move at greater speeds).
As can be expected, if water is continuously pushed into a room, either by an unlimited water source like a river or by means of a pump, the water will not stop when the room is filled, but search for an outlet, even on higher z-levels. If there is an outlet, but it can not take all the water coming in, the water will look for further outlets.
It's possible for dwarf-built pumps to pick liquid up and lift it higher, possibly back to the source and thus creating a closed cycle. Beware that operating pumps obey the same pressure rules as infinite water sources, capable of pushing both water and magma down through tunnels and back up to the original Z-level of the pump's output tile.
It is easy to flood your fortress accidentally by not accounting for water pressure. For example:
- It is safe to dig out a cistern one level below a murky pool, and to channel above a few tiles of the cistern so that your dwarves can get water from it without having to go outside.
- It is safe to refill a murky pool with water from a pump or brook/river/etc on the same level.
- It is not safe to do both to the same pool! The water from the pump/brook/river/whatever will fill the pool to 7/7, and will then pressurize the water in the cistern, which will then flow up out of your channels and flood your fort.
Waterfalls are of special concern. If you tap river section downstream from a waterfall, the water will be under additional pressure as it is coming from above the river's surface. It is absolutely critical to reduce the pressure in such a system if you do not wish flooding, the easiest way being diverting the water diagonally - although if used solely for a complex drowning trap or other purpose, flooding may be desirable.
 Mitigating dangers
 Diagonal Flow
Obviously the game treats water connected only by a diagonal tile as not connected in terms of "pressure" but only in terms of "diffusion". A common adaption of this behaviour is feeding water through a diagonal tile "to take the pressure out":
Pressure cannot push water through diagonal gaps between tiles - instead, it will merely flow through if the water level on the other side is low enough.
###### #########≈≈≈≈######## Direction-> ≈≈≈≈#≈≈≈≈≈≈ -> of -> ≈≈≈≈≈#≈≈≈≈≈ -> Flow -> ≈≈≈≈≈≈#≈≈≈≈ -> ###########≈######### ###
# = wall, constructed or undug ≈ = pressurized water ≈ = neutral/normal water pressure
This does not work on a vertical basis - water only travels vertically to a different z-level, never diagonally.
A result of this is that a tunnel system that spans several z-levels, but is connected to a river only by a diagonal tile, will fill only to one z-level lower than the river, but if an orthogonal connection is created, it will fill up another level.
If you wish to maintain the rate of flow after de-pressurizing, it's recommended that you have more diagonals than water tiles - that is, if the source is 3-tiles wide, you may wish 4 or more diagonal passages.
Water pressure does not propagate through pumps, so it is possible to fill a pool using a screw pump without it having the same pressure as its source. Of course, there is a downside - you still have to run the pumps and due to the source water's pressure, the pump must be powered instead of run by a dwarf, as the tile the dwarf needs to stand on is filled by water. Furthermore, the pump will likely need to be powered from above or below (as water would simply flow around a gear or axle placed next to the pump), though creative setups are still possible by using additional screw pumps to transmit power.
Your vertical axles or gear assemblies need to be placed above the unwalkable tile of the pump, and there must not be a channel over the walkable pump tile. (Water can only flow straight upward, not up and to the side at the same time.) Multiple adjacent pumps will also transfer power between themselves automatically.
Side view Power Water Key ↓ ↓↓↓↓↓ # = Normal wall ######║####≈≈≈≈≈ # = Wall that pressurised water would flow into if it were to be dug out ######║#####≈≈≈≈ ≈ = Regular water _ ___#║#######≈≈ ≈ = Pressurised water #≈≈≈≈≈%%≈≈≈≈≈≈≈≈ %% = Pump ########≈####### ║ = Axle ################ _ = Floor
Do note that the water output from the screw pump will be pressurized according to the "infinite water source" behavior, but said pressure will be independent of the source and can be subsequently 'reset' by additional pumps or diagonal gaps.
 Overall behavior
- If a tile contains water which is floating on top of another tile of 7/7 water (and apparently only if there is no water above itVerify), the water in the upper Z-level will be pushed downward and moved to the nearest orthogonal (not diagonal) tile on the lowest available Z-level, up to the Z-level just below the top. Each tile of liquid performs this check once every few steps. This type of pressure applies only to water, and is what causes large bodies of water multiple Z-levels deep to rapidly drain when opened.
- If a liquid source (river/brook source, underground river waterfall tile, map edge, or screw pump output) attempts to create liquid in its output tile but cannot due to it being full already, the liquid will be created in the nearest orthogonal (not diagonal) tile on the lowest available Z-level, up to the same Z-level as the source. This applies to both water and magma, and can be observed by damming a river.
- Liquids adjacent (both orthogonally and diagonally) to non-full tiles will flow into them and average their depths, pushing lightweight objects and creating flow (which will power water wheels) if the depth difference was 2 or more.
 Movies of pressure experiments
-  - Showing that pumps output 0-pressure water even from a high-pressure source, that water will not flow up and to the side at the same time (has to flow straight up), and a few other things
-  - Showing that pressure is not transmitted through non-7/7 tiles.
-  - Pump turned into infinite water generator, but still provided useful information on how overpressure causes upward flooding. The infinite water generation behavior has since been fixed.
-  - Uses three pumps connected to different tunnel layouts to test a few of these rules: One tunnel has three accessible z-levels. The second tunnel has one accessible z level and periodic shafts up. The third has only one accessible z level with no shafts. The bottom level of all three filled first, and the shafts did not fill until the bottom was filled. The second level of the three-high tunnel did not begin filling until the first was full. They did not all fill the bottom at once, but this is believed to be due to the order in which their pumps are placed on the river.
 Possible future experiments
- Have a pump pumping water into a 3-wide tunnel with a 1-wide tunnel below it. Have another pump pumping water into a 1-wide tunnel with a 3-wide tunnel below it. Observe whether the bottom tunnel's water spreads faster in both cases or just in the smaller tunnel.
- From a pump, fill a cistern which is several levels lower. Shut off the pump and the higher level tiles with hatches once the whole thing is 7/7. Open other hatches above the cistern, combine water with unpressurized water, and see what happens.
- Determine how much "drainage" is required to handle the output of a single, fully flowing pump.
- Confirm whether water "flow" is slowed by diagonal vs. orthogonal passages. Develop some numbers for portioning water flow into smaller amounts, that can be handled by single-tile sized drains.
 See Also