Cycles: create water using smoke / volume
I looked at your solution another time @LukeD and it worked. I am sorry, I must have got it wrong the first time.

I think you can get acceptable results with this solution:



Now, the quality depends on the density of the smoke (and, of course, the smoke resolution)


Material for the smoke domain:


However, if you what to create a slow waterfall, like this:
https://www.youtube.com/watch?v=pTjtHFeZ1zM
with clear water, there must be another method.

I fiddled around with the fluid sim and noticed, that the volume of the inflow object is crucial. I increased it´s size, compared to the example above. There must be enough water flowing into the scene! Thus, fluid sim could do it as well:



Render time for fluids is way shorter than for smoke, if you have a good grafics card, btw, because smoke doesn´t support GPU computing yet.