What a coincidence. Just yesterday I saw a video about how Mario Galaxy works from a technical perspective, and water was one of the topics.
As it turns out, the water effect was done by manipulating the floor texture with a noise pattern to make it squiggly, and then putting a transparent layer above. It looks pretty good and doesn't need much processing power.
I just thought that was interesting.