I just spent the last few (RL) days constructing, de-constructing, puzzling, planning, frustrating, tweaking, hair-pulling and waiting for my waterfall system to be built.
The water is pumped from the nearby brook and falls several Z-levels through the center of my fort. I carefully planned this so that it would have a clear path down the middle, unhindered by any expansion efforts I might make in the future.
At the resevoir at the bottom, I have three water wheels linked to a stack of pumps that are supposed to draw the water back up to a dumping location.
After several game years of trying and failing to get the waterwheels and pumps to power themselves properly, I was gradually getting closer to success each time.
Finally, after what must have been at least five entire re-designs of the system, it was working -- intermittently. Hey, partial success is still success, amirite?
So I happily sat there and watched a small amount of water work it's way up the pumps and towards the dumping location. Unfortunately, I had forgotten one very important... uhh, detail. The game suddenly paused, and I saw the message: "A section of cavern has collapsed!"
But that was no cave-in message... it was my water falling into the magma pipe and becoming falling blocks of obsidian. I had planned to dump the water into the magma pipe, evaporating it, as I had no bottomless pit to get rid of the excess with.
Due to this small oversight, my budding six-year fortress has been a complete waste of time.