This is the crux of it!
If water mechanics tie into other mechanics, not always as essential parts of the game but giving players a lot of opportunity to play with different combinations, then it will be fun. If they’re just a “chore”, they won’t be fun. Finding a balance which makes water important without making it too important/overbearing on the player’s attention is the key here, but I think you’re already heading in the right direction @Deggial.
For example, perhaps there are some plants which satisfy a part of the hearthlings’ water needs? That would reduce the stress of providing water for the town if the player is already focusing on farming and cooking; so they don’t need to spend a lot of effort on wells and water carriers… but at the same time, a little bit of effort digging irrigation canals could allow them to improve their farm yields. It would make sense if the RC had more access to these plants (e.g. gourds and cacti), whereas the Ascendancy have less of them/less access (corn perhaps? Of course they have berries too… they provide a good “rough and ready” early-game solution, although the player would probably need more than just berry bushes in the late game) because the Ascendancy are used to the idea of using abundant local freshwater whereas the RC are used to the idea that they have to provide their own.
One other thing I’ve brought up in other threads about water is the idea of using it in “public amenities” – e.g. bathhouses – to promote hearthling wellbeing. Such structures will require advanced engineering (at the very least they’ll need aqueducts and high-level crafts like mason and weaver items, probably pumps as well), and provide the town with a boost across most or all of the population (depending on access to the bathhouse), so it’s more of a late-game “quality of life” project rather than part of the bare essentials of starting out. But it’s the kind of project which brings together elements from across the game, and gives the player a sense of achievement at having this advanced structure which only the most developed towns can support.
Once again, it’s all about tying everything together. If water feels like it belongs, it will be fun; if it feels like it’s just poured in to make everything a bit prettier then it won’t be much fun except where it’s just conveniently in the background. At the very least, there should be some tangible benefit to learning how to move water around and use it in construction; even if it’s just a defensive bonus such as enemies being unable to attack while they’re swimming, or a boost to growth speed for any plant growing near water, or something simple like that.