Experiment No. 1 involved seeing if a Sim would actually swim from one level to another, in a standard pool, built in steps using CFE.
Three pools, with a bit of horizontal separation, on three descending levels.
Turned off CFE, and built more pool tiles between them, to connect the three.
The result is kind of cool, and yes, my test sim did swim from the first pool down to the third, no problem.
Experiment No. 2 was placing animated water overlays on the three pools, which did make the water look like it was flowing, but only when viewed from above... getting the appearance of an actual waterfall is going to be tricky. I have some sort of translucent curtains that I use to hide modern things; if they came in a convincing waterfallish recolor, it might help.
BUT... the Sim does appear to be leaping off a rock into the pool, thanks to an invisible diving board recolor and a deco pond rock. Since the pond rocks were found in the gardening section, maybe they're not actually coded as sculptures, which might conceivably block a Sim from using the diving board. Anyway... more work to be done. I just moved this new Sim onto the bare lot, it'll save time because I don't exactly know what I'm doing, and will probably have to go through several strategies before I find one that works. I was actually coming up with interesting ideas as I fell asleep last night... it's just a matter of remembering what I was thinking.
My beard grows to my toes; I never wears no clothes.
I wraps my hair around my bare,
And down the road I goes. -Shel Silverstein