As described above, a 'waterfall' is defined by mist, which you can identify by examining the square (Mist appears in pink text). Mist is created when water falls multiple Z levels.
A screw pump allows you to control the flow of water, as you can control pumps via manual labour, or power (preferred) - remember you should have at least one gear assembly in the source-to-target chain, which you can attach to a lever.
Things that will help you:
- Screw pumps lift water one level up; channels bring water one level down.
--> A screw pump will take water from the square next to it (you can define the source direction), 1-Z level down. So if you have water at level Z=0 and a pump at level Z1, when active the pump will lift it to it's level, (1 up) and deliver it in front of it.
----> That means that several pumps can bring the water multiple levels up
- Water will flow, so water has to drain. My personal preference is to use another pump to deliver it back into the river, one level above, so it spills inside easily. An easier solution is to have the "target" area mined out so the water eventually reenters the river.
- Water in a river, a brook, or from a pump has pressure, meaning it can overflow upwards.
- If you're using a brook, you actually have to 'channel' (b-h) it's top tiles to have access to the water, whether it's pumping out or pour in.
EDIT: This is not necessarily true, actually. A brook top acts like a floor grate, letting water pass in and out over top. I'd still do it...
The idea behind a self-reliant waterfall is a closed system that drops the water, then brings it back up with pumps. You open the system to pour water in, close it with a floodgate attached to a remote lever when you have enough, and let the water circulate.