I think the key to striking that balance is to give a few non-trivial but non-intensive alternatives; and make working with water directly a more work-intensive option with a higher payout.
So, for the thirst example, prepared foods could give some thirst satiation, particularly fresh fruits and veg (cactus fruit for RC, berries for the Ascendancy); and maybe a fruit smoothie which gives us a use for larger quantities of those fruits once farms are established.
On the other hand, setting up a drinking fountain provides a supply of drinks/hydration where each serving has a trivial work cost (the hearthlings simply walk up and grab a drink when thirsty); but the initial cost to build the fountain is much higher than setting up for fruit smoothies would be. Building a fountain would require pumps (from the Engineer), laying pipes, storing water in a reservoir… not something you can set up on the first day, probably not even the first month.
As for crops, I like the idea that higher-tier crops grow better with irrigation and that some late-game crops such as rice might even require it. Again, it’s a question of work requirement vs reward. For example, rice could give a huge amount of food supply, but require more prep-work to establish and maintain the fields (not just creating a field with irrigation ditches; I imagine that the fields would need to be flooded and drained during different growth stages, again requiring pumps or valves somewhere)… other crops might take advantage of sprinkler systems or simple drip lines for increased yields; or need to be planted within X distance from water which in itself becomes a limiting factor – either you grow them near a natural water source, or you have to create aqueducts and irrigation ditches in order to keep your crops healthy.
I’ve played a lot of games where any body of water will work for fishing; sometimes there’s a minimum size but even then it’s not hard to dig out a pond in the middle of town (usually right next to the kitchen, lol!)… it’s just not particularly interesting. I’d love it if Stonehearth had fishing zones like trapping zones – the fisher class can put down crab pots, nets or fish traps; and at higher levels they might gain the ability to fish directly (i.e. with a line or rod, or a net) in between checking their traps/nets as a way to catch rarer or higher quality fish. Then, bodies of water either contain fish or they don’t; if the player wants to introduce fish to an artificial pond they either have to give the fish access through some kind of channel; or else catch fingerlings and prepare their new home with suitable food supply and so on until the fish population can sustain itself.
As for floods… as long as they naturally drain, and don’t approach with no warning, I’m all for them! Building in a hollow should come with its down-sides… of course, a well-designed drainage network/sewer system eliminates that problem. Flash floods would be rather obnoxious; but if it has been raining for a few days then the player should be aware that there’s a risk of rivers rising, and maybe it’s a good time to move perishable items to high ground.