Water to me is like just about any hazard. It can certainly be used effectively and in an interesting manner, but if it appears too often it becomes repetitive and loses its interest.
Water is best used like other hazards when presented as an angle, and most of the time creeks work better than ponds or lakes from what I've experienced.
I like the risk/reward aspect that water hazards provide when utlized a couple times a round. There is a finality to it that provides a different type of hazard than a bunker or rough. Personally I prefer to have most hazards be something that can be recovered from (with some degree of difficulty), and the water provides a change from that.
I never like blind water hazards though. Or at least none that I can think of certainly.