Here are a couple of things that I thought were interesting, if not overly flashy.
I seem to recall Portal 2 used some sort of flow maps for its water. Here is a blog entry about animating water using flow maps:
http://graphicsrunner.blogspot.com/2010/08/water-using-flow-maps.html
And here is some work done for a dissertation, involving dynamic weather effects with shaders: http://www.youtube.com/watch?v=8YPgVlBbtcs
I seem to recall Portal 2 used some sort of flow maps for its water. Here is a blog entry about animating water using flow maps:
http://graphicsrunner.blogspot.com/2010/08/water-using-flow-maps.html
And here is some work done for a dissertation, involving dynamic weather effects with shaders: http://www.youtube.com/watch?v=8YPgVlBbtcs