Displacements with a flowing water texture, water/waterfall. There are settings in displacements to turn off physics, hull and ray collision, which you'll want to do.
There's also a waterfall prop you can use, too, but the size of it makes it hard to fit in for smaller waterfalls. Make it non-solid if you use it, and probably turn off shadows, too.
The splashes are done with an info_particle_system. The particle you'll want is waterfall_rocksplash, though there are a couple of others that might be useful, too. I forget the names but they're in cp_powerhouse if you want to grab that vmf from the decompiled maps thread and take a look around.
I also made a custom blend texture for flowing water, so that you can have a little better transistion when waterfalls hit normal water. Just put the displacement on top of the surface of the water (if it goes below, it won't show up) and blend the texture like any other displacement. Texture name is water_darkblend and you can find it in the
Egypt Sphinx assets.
You can't put actual water textures on multiple levels, so you'll have to fake one of the surfaces. Also in the Egypt Sphinx assets is a texture called water_sekhmet_fake, which is just a refractive texture. It'll make a splash sound if someone walks on it, but it will NOT work as normal water so it's best to keep players from reaching it, or make it very shallow.
Freyja did a lot of work on getting the waterfalls in cp_sulfur looking amazing, so she may have some tips for you that I didn't mention.