The water mod added spawners and drains on top of activating the water (which wasn’t generating in the world at the time), and also added interaction between hearthlings and water through a thirst system.
In the vanilla game, water can be “picked up” or “detected” by items, and the methods to add or remove water are there but not active (that is, there’s nothing to make use of those features).
As for moving water in the game currently, it’s mostly a question of giving it the space to flow without it spilling over the sides. Waterfalls tend to splash water all over the place, but you can create a vertical “pipe” to cleanly move water between levels. Water will even equalise between two containers/holding ponds, and it’s possible to create a fountain with these mechanics.
However, water tends to spray out the end of any open channel; hence why it gets so messy to work with. There are also issues with having large volumes of water trying to equalise; especially if you have a “face” of water (like a cliff-face, or think of a tidal wave) which makes contact with other water flowing near it.