If you have a lot of older programs hanging around on your Mac, chances are some of them may not work correctly (or at all) after upgrading to OS X Lion. Most programs put out in the last few years should at least launch in Lion, though it may be some time before they run well. However, there is a certain older class of application that won't even open anymore once you install OS X Lion: PowerPC-only apps. Before upgrading to Lion, it's important to know if you have any of these apps still hanging around, because they'll be useless piles of ones and zeros afterward.
In 2009, Mac OS X Snow Leopard moved to Intel-only hardware and dropped legacy support for PowerPC Macs. Wisely (for the time), Apple kept support for PowerPC software as an option for Mac users running Snow Leopard. The PPC-enabling code translation technology, Rosetta, was an optional install for users who still needed the ability to turn PowerPC instructions into something the newer Intel processors could execute.
It's been six years since the announcement of the PowerPC to Intel transition, and with OS X Lion Apple has basically said, "Enough is enough," and relegated Rosetta to the software dustbin. In fact, one of the reasons Lion is a smaller install than Snow Leopard is the absence of all that PPC compatibility code in system libraries (which were shipping as 'fat binaries' before, and are now slimmed down to Intel-only).
The upshot for Apple is that's a lot less legacy code to worry about -- and unlike Microsoft's traditional approach, Apple is all about ditching backward compatibility for both hardware and software in the name of progress and streamlining. For a certain subset of users who are still running legacy PowerPC programs, however, it means transitioning to Lion may be inconvenient at best and financially ruinous at worst.
Retro Techs has a list of software that won't run on Lion anymore because of its PPC-free pedigree, and there are some pretty big (though to be fair, pretty old) names on it. Adobe CS2 and earlier are dead programs walking in OS X Lion, which is why I said upgrading might be financially ruinous for some users -- updating to the latest version of Creative Suite can be defined as "cheap" only if you're one to complain about having to tool around town in your BMW because your Jag is in the shop. Quicken for Mac won't run on Lion either, and there really isn't a one-size-fits-all alternative to the program.
Microsoft Office 2004 and earlier won't work under Lion, and neither will AppleWorks (remember him? Ha). Fortunately, Apple's iWork suite is a powerful (and inexpensive) alternative to both software suites.
Unfortunately, if you had some classic games like Starcraft and Diablo II still hanging out on your Mac, you're out of luck under Lion. If running classic games like those are critical to your "workflow," you might be best served by tracking down Windows versions of them (and a version of Windows) and running them in Boot Camp or in a virtual PC. If that sounds like a huge pain (it certainly does to me), you might be better off keeping an older Mac around with Snow Leopard running on it so you can still run PowerPC apps, or if you're geekily inclined you can partition the hard drive on your current Mac and dual boot into Snow Leopard (unless you're buying a new mini or MacBook Air that ships with Lion -- they won't boot 10.6). Partitioning and running Snow Leopard on another partition will also work for applications like Office 2004, but the question to "Is it worth the effort to do that?" is almost certainly going to be "No."
The easiest way to tell if you're still running any PowerPC applications on your Mac is to select "About this Mac" from the Apple menu, then go into System Profiler (now known as "System Information" in Lion, accessible by clicking on "System Report...") and check out the "Applications" list in the sidebar under "Software." Sort this list by "Kind" and take note of any apps you find that say "PowerPC" -- those apps won't run in Lion. Anything that says "Intel" or "Universal" is good to go.
Alas, poor MarbleBlast, I knew him well...
If you've been using Macs since OS 9 (in other words, for years and years and years), you might see another kind of app described as "Classic." Those apps haven't been executable on a PowerPC Mac since Mac OS X Leopard (10.5) and have never run on Intel Macs anyway, so chances are you found alternatives for those apps around four or five years ago.
Hopefully all that made sense. One thing worth asking yourself is whether the advantages of upgrading to Lion outweigh the disadvantages of discontinued support for your legacy programs. If you find yourself in a situation where you're financially dependent on Adobe CS2, Office 2004, Quicken 2007, or Starcraft (it could happen), then you may want to keep Snow Leopard as your OS of choice.