I just registered only to say Trine.
Technologically, I don't think it uses much fewer tricks than fancy shooters and whatnot. More importantly, the designs are absolutely breathtaking, be it backgrounds you only see once, or the unique feel of each area. It's honestly the best-looking game I have ever played. Screw Crysis, why do you eat hardware if all you do is render some leaves? CryTek wouldn't know the word "design" if it bit them in their German behinds.
I agree with the others who mentioned Assassin's Creed, but that was mostly because it had been the first time I saw all the new technology - the game came free with my then-new graphics card, after having to endure a GeForce 6800U for years. It was a joy to see how far graphics had gotten.
Probably a strange example, but Star Wars Galaxies definitely had me in awe all the time. It has an unfair advantage insofar that the unique look of the Star Wars universe is just very appealing to a, duh, SW fan; but the engine was also ahead of its time. No, it was - when it came out, nobody could run it well. I played the SWGEmu earlier this year and it held up awesomely. There is only one place that can beat Tatooine...
... and that's Hyrule. But only after it's buried under a flood. I totally fell in love with Wind Waker's art style - and this wasn't when it came out, it was last christmas. Maybe it was the bright greens and blues in this greybrown FPS-era, but I spent hours just looking at all the places.
And nobody complain that it's too kiddy. Most of the characters are kids and kid-friendly, but damn it knew how to create atmosphere. It's called contrast, look it up. (Oh yeah, and adorable kiddie-Link murders big bad Ganon by shoving a sword in his face...)
Still, Trine beats them all. I still visualgasm just thinking about it.