The computer I normally use to play DF is dead so I'm using a computer that doesn't have any existing forts, so it's hard to check this for myself, so I decided that I'd ask. Also, depending upon the answer, this question may benefit others.
A waterfall in a fort can be created by dropping water through a hole in the ceiling, at which point it passes through a grate or something in the floor. This can be continued for as many Z-levels as desired, obviously. At the bottom, water can flow out of the fort, into the caverns, or be brought back up via a pump stack.
Regardless of the system used, it would be trivial to make a catch basin at the bottom, which would result in standing water at the bottom of the waterfall. The catch basin could be two Z-levels high, with drainage on the upper level, kind of like the drain near the top of a bathroom sink, so water wouldn't flood the fort, but wouldn't immediately drain out if the watefall was turned off, and would always be about 7 units high.
These days I'm building forts on aquifers in cold biomes, so it's trivial to set this kind of thing up -- I just build a ramp into the ceiling below the aquifer (it seems to have to be a ramp in natural rock -- a constructed ramp doesn't work) and water pours out.
What I'm wondering is if rather than putting a grate on the floor of each level the waterfall passes through, could I put a well there? Can you have a waterfall flowing over a well without interrupting the work task when a dwarf goes to get water from the well? This would be pretty cool, since a well can be a high-value piece of architecture, and it could share the same space as the waterfall.