The visual glitz factor in Leopard is definitely high, with traditional tools like search being augmented with alternative views such as CoverFlow. There's already been some discussion about whether this is merely eye-candy or has some real value to the power-user, but you can decide for yourself with SlashGear's video from the WWDC Keynote.
Undoubtedly live previews of documents, presentations, video and music direct from the Finder pane are impressive, but I can understand some people doubting its real usefulness. Personally I think it's just a further example of how integrated search can be into a system, and of how Apple are attempting to cater for more than the traditional style of browsing; more choice is seldom a bad thing.
Make sure to check out the rest of SlashGear's WWDC 2007 coverage.