The first time I felt a large moment of awe as you described was in Sonic Adventure.
The whole game was a rollercoaster of amazing set-pieces.
I'm willing to bet everyone was staring agape in awe the first time they saw sonic speeding down the side of a building, or outrunning an avalanche on a snowboard, or countless other moments of greatness.
The second time was Shadow of the Colossus. From first riding out into that open world, and seeing the first colossus for the first time, scaling him, and taking him down. Just moments of pure awesomeness...
And don't even get me started on Bioshock. Sure I give it a lot of crap for its gameplay and hilariously black and white endings, but dammit the story was one amazing experience...
EDIT: Can't believe I forgot, but Power Stone 2 left me in awe countless times.
Every level changes drastically while you play. For example, one level is set on an airship. And as you fight on that level, the ship gradually deteriorates, eventually exploding, and sending everyone falling to the ground. While your falling, you have to collect umbrellas to break your fall, but you can also fly into people, causing them to drop their umbrellas. You eventually land, and where you land has catapults, and a tank that you can use on the other combatants. Its just amazing, and almost every level is equally dynamic and ever-changing.
Edit #2: I also remember the first time booting up Battlefield 1942. It was the first time I played online. I remember joining a game on El Aliman. I was on the British side. I looked around, and saw other people. There were other people playing, and they didn't even need to be near me! Sure this sounds laughable by todays standards. But I was just amazed by it. I was playing with actual people, from different cities, and from around the world. It was amazing, and it was my first online game.