ive actually done this, though quite differently to you people. it was my first megaproject, as the only constant source of water was an underground river. so i used blocks of 3 pumps stacked on top of each other from the middle of the underground area right up to the to of a huge tank like tower, which had floodgates that would let a waterfall spill over my entrance. a couple of levers would retract the golden drawbridge, open the floodgates and close an area underneath in the fortress, allowing the waterfall to spill down through the centre of the fort and eventually back into the chasm.
the fort also has a system whereby the water from the tower can be flushed through the entrance hall into the same passages that take the water out to the underground chasm, though despite my best efforts ive never actually got an invading army inside, there are always too many importand dwarves being chased around the map or present in the hall when an invasion arrives. that system also has floodgates and drawbridges that seal the passage from the rest of the fort, including covering the fortifications that the ballistas fire through. ballistas and narrow hallways are hilariously awesome.
biggest things ive learnt are first the thing about water spillage that someone mentioned - water goes a few squares outwards as it falls, and in many cases wont drain back into where it should, but oddly just keep filling up the area around the drainage point that the main waterfall is hitting. you cant have engraved floors anywhere near an area that has a waterfall. they allways get wet eventually, and it makes your legendary engravers tantrum when tower cap mushrooms or shrubs and trees sprout out of their masterpeices. third, if you have an underground water feature, any passages that are not going to be filled with 7/7 water all the time need a paved road floor. otherwise tower cap mushrooms grow in them and block the water off. which can cause catastrophic backflows in other parts of your fort. and dont have any fortifications in any cliff faces near the waterfall, as it somehow finds its way in and builds up and then you get plants growing everywhere.
the big water tower i have also has aqueducts leading off it that pour water into several places in a moat around my fort. the moat flows pretty fast and drains back underground into the chasm i have. tests throwing kittens into said flowing moat seem to indicate that most creatures in the flowing water get swept underground and into the chasm, which is awesome. there are a few difficult to get to places in my fort that utilize this, as now ive had my fun with it and the population is epicly large, im setting it up for adventure mode then abandoning.
and pumps do seem to create more water then they drink up when they draw from a water source, so be really careful and unless you have a good place like my chasm to drain into then use a closed system or your entire map will get filled with water. seriously, the power of pumps was actually increased in the 40d update, it broke my system and i had to change my design a bit to make it work properly again.