Here's one I did a month or two ago.
Set up a new hospital near the entrance of the fort and stocked it. My dorfs kept going outside for water, so I figured I'd dig a small reservoir by the hospital and link it to the river via a pump station and some fortifications. Since dorfs always complain about wells, I figured I'd drill it down through a few levels and create a well system that both the hospital and dining room could utilize. Got it set up to be a nice size reservoir, adding some fail-safes to keep dorfs from ending up in the reservoir and contaminating it. Added a pressure plate to make sure the reservoir would only fill up so far and then stop the pump. Added a side drain down to the caverns if I ever needed to flush the system.
Turned it on and moved to check out a different project while it filled. A few minutes later the first dwarf drowns followed by a few others.
As it turns out, I wasn't paying attention when I was building the cistern. I'd channeled out the cistern but didn't stop high enough. Basically the whole thing was just a water express-way to my residential complex, which it happily flooded.
By the time I got somebody up there to pull the lever, it had started flooding my lower crafting stations.
That's the fort where I learned to build your main stair down to the caverns and then grate or fortify the bottom of it for drainage instead of just walling it off.