So I upgraded to Snow Leopard today. I pre-ordered it right as it became available on Monday and it was waiting for me when I got home from work today. I popped the CD in, clicked the upgrade button, typed my password, watched an episode of Damages and by the time it was done my computer was all set (so less than an hour). Talk about painless...
As far as the OS itself goes, it's basically as if nothing had happened. Everything is where it was, all of my applications still work and Time Machine is backing up the 6 or so new gigs as I'm writing this (in reality Snow Leopard gives you space back, but it's still new data as far as Time Machine is concerned).
I haven't really noticed any speed difference yet but then again I wasn't expecting to: most applications haven't yet been built to take advantage of Snow Leopard's new capabilities and I haven't run many heavy programs simultaneously. I definitely like the tweaks to expose though, and the new QuickTime is pretty slick.
Of course none of this was unexpected: Snow Leopard's improvements are supposed to be all under the hood and Apple has been pretty upfront about that from the get go. All in all the upgrade was a non-event (I say this in a good way), and well worth the $29.
If you want more info on Snow Leopard, here are the links to the reviews from Engadget, Gizmodo and MacWorld.