I upgraded to Lion and so far have had no issues what so ever.
Aperture 3 runs fine. Photoshop CS4 runs fine. Final Cut Pro 7 runs fine.
I have had no issues with upgrading, applications working, or general OS use. That being said I have never had an OS upgrade destroy anything and I understand that I'm probably in a minority of the "geek crowd" so you should back up.
As for all of the "little" changes such as inverse, ahem, excuse me 'natural' scrolling, invisible scroll bars, version control, auto-save, etc. the OS does make a lot of really big improvements. Someone mentioned Mail and iCal: amazing. Plus the system integration of online accounts like Google make things a lot easier.
And might I add one of the biggest reasons to upgrade now or in the near future. In a couple years when we're on the verge of getting OS XI and you're still using Snow Leopard and complaining your stuff doesn't work remember that just because you don't find it a worth while upgrade doesn't mean that everyone is going to keep you in mind.
If you don't incrementally upgrade your OS and computer hardware then you're going to get stuck spending a bunch of money all at once to get up to date. So while Snow Leopard may do everything you want, you may want to consider getting Lion as soon as you know all of the software you rely on will work.
Don't be the Mac equivalent of someone still using Windows 98 and IE 6 complaining that your computer doesn't work anymore
