I think things like heat waves, droughts, blizzards etc. that affect your city and industries in various ways are definitely more interesting (and balanced) than some alien invasion, meteorites or giant killer robots. I always saw highly destructive disasters like this more as a gimmick that you do before reloading your save. Because which city would get constantly struck by highly devastating causes like this? People would simply move to a safer region instead of living in ground 0 of all bad things.
It's nice to see the physics engine at work and take some cool screenshots, but yeah what a total pain it would be to have to rebuild after a flood or something. Maybe some people like that though.
I used to leave them off till my cities got too successful, and the game got boring. Then I'd pull out the meteor tool and drop a half dozen. Rebuilding after was always fun. This was in Sim City 4
Flooding could cause non destructive damage that the city services repair & clean up on their own. I think that would be okay, especially if the game offers maybe ways to reduce flood risks.