The Services menu was a confusing mess in Leopard, but it finally comes into its own in the 10.6 release. You used to see every Service on the system in the Services menu ("Chinese Text Converter?" WTF), but now it only shows what Services are available in the application you're running. You can also get a Services submenu in the secondary-click (i.e. ctrl-click, right-click, two-finger-tap) menu of any actionable item. What's probably most helpful for workflow, though, is the ability to create your own Services using Automator. If you haven't explored the power of Automator, it's an excellent time to start if you've upgraded to Snow Leopard.
As opposed to the old Leopard days - when every application installed items in the Services menu willy-nilly - you, the user, now have complete control over what is in the Services menu, using the Keyboard Shortcuts tab of the Keyboard System Preferences panel. If you click on Services, you can see every service installed on your Mac. Tick the checkboxes to enable the ones you want or disable the ones you won't use. This is all in the Keyboard Preferences pane because you can assign keyboard shortcuts to Services - it's a bit of a non-intuitive place to find it, but fortunately there's a "Services Preferences" link in the Services menu to point you in the right direction.
Apple has kicked things off by providing some Services for free download. Take a look at the Mac OS X Automation
page, where you can download services for Terminal, Finder, the media picker, PDF files, photographs, movies, the Internet, etc. You can install them (Apple provides them in .pkg format so it loads them automagically in your ~/Library/Services or /Library/Services folder, depending on whether you choose "All Users" or "Current User") or open them in Automator to see how they work. This is the genius of Automator and modular Services in my opinion: the ability to open the hood and tinker around.
As an old Unix jock, I like the how Automator has brought the concept of command output redirection
to the desktop, and in a nice, unintimidating, graphical way. A cool example of this is shown in the Paragraph Sorting Service
tutorial at Mac OS X Automation. This Service allows you to select lines of text, dump them to command-line sort, and then pastes the result back into your application.
Services did exist in Leopard, and Automator could edit them, but 10.6 brings the innovation of making Services system-wide, so that you can reuse Services in different applications. This is classic Unix hackery, and opens up the Mac OS architecture to innovation in an extremely powerful way.
I'm always looking for ways to make my workflow smoother, and I like playing with the capabilities of my system. Apple's created a way for mere-mortal users to add real functionality across applications, and I'm sure that we'll see a real proliferation of downloadable services in the days to come.