I put a waterfall on the edge of a hallway that leads to either the dining hall, or the statue garden, or the well, or Boozehalla -- some place the dwarves all want to go, which means they have to pass the waterfall along the way.
In my initial attempts, I tried setting up a pump stack which pulled water directly from a large underground reservoir. This was not a good idea. The pump stack spewed so much water that it couldn't drain back into the reservoir as fast as it was flying out of the pump. The result: a flooded hallway (or worse).
So I modified the design: instead of a large reservoir for the pump stack, I gave them a ONE-tile reservoir. The water falls out of the top pump directly back into the reservoir, and there's no overflow.
With such a small reservoir, I don't even need complex engineering for filling it. I just designate it as a pond and let dwarves fill it to 6/7 with buckets, then un-designate it, and then start the pumps.
This doubles as a shower, too. Dwarves will happily walk on the floor grate when there is no water currently blocking that tile, and will then be drenched as the water falls on them. This removes all contaminants from the dwarf and his clothing and puts them on the floor grate. When dwarves see this, they sometimes feel a wish to Clean it, which is a good thing. Unfortunately, Cleaning takes longer than the microscopic time interval between water dumps, so they spam me with job cancellation messages. Therefore, I make sure there's a gear assembly somewhere between the power source and the pump stack, hooked to a lever. When the shower's contaminated, I turn it off, wait for someone to Clean it, and then turn it back on.
+ Constructed floor, to prevent tree growth
XD% Pump (bottom of stack) and access stairwell/door
=======%== Wall + pump
+++++++#++ Hallway + floor grate over channeled reservoir
++++++++++