There have only been a few gaming moments that I have been totally blown away. I will list the game and describe why it blew me away. Please feel free to share some of your WOW moments as well.
Dragon Warrior I: NES: 1989: This was my first RPG (Other than Dungeons of Daggorath which I was too young to get past the 2nd level) and I was blown away by this huge world that I could walk around in, talk to people, buy items, etc. I had never played anything like it.. and it made me go.. wow… :-P
Out of this world: PC: 1991: I must have watched the cinematic intro a thousand times and told my parents it was like watching a movie on my computer (a 16mhz 386sx).
Tie Fighter: PC: 1994: OMG… After reading the novel then strapping into the cockpit to play this game.. I truly felt like a fighter pilot in the imperial navy.
Donkey Kong Country: SNES: 1994: Music, Sound and Graphics… I know it was a 3D trick, but I used to sit any just stare at the sharks on the water levels.
Wing Commander III: PC: 1994: Was like watching a movie and game graphics were great. Never saw anything like it before. I thought it was amazing.
WarCraft II: PC: 1995: You mean I can play on a network against my friends… OMG!!! Unreal!!
Tomb Raider: Playstation: 1996: You could look all around in amazing 3D detail at these huge environments (not just closed hallways). I thought it was amazing.
Super Mario 64: N64: 1996: Just when I thought Tomb Raider blew me away, I got a N64 and loaded SM64… I used to just stand around looking at things and jumping off trees… I literally could not believe how amazing it was.
Fallout: PC: 1997: They make violent adult games like this? I loved it!!
PlaneScape Torment: PC: 1999: Blown away by depth of the story… First/Only game that actually shocked/upset me by a plot point because I was so involved in the story (don’t want to give away any spoilers, but a vague hint was that it had something to do with what was written on your back and when you find out)
HomeWorld: PC: 1999: Amazing graphics, sound, 3D movement, story… I was blown away by this game in every way. (last game I played on PCI graphics before the move to AGP)
Half-Life 2: PC: 2004: Doom 3 did not blow me away but HL-2 did.. The first time I got chased into an apartment then saw a TV, ripped it off the wall and the power cord sparked… Then threw it out the window where the glass broke and the TV fell outside down 2 stories and bounced on the ground.. This is when I realized the “Next Generation” was upon us.
I guess that pretty much sums it up.. I have played TONS of cool games since HL-2.. Oblivion, The Witcher, Empire: Total-War, Mass Effect… I could go on… but nothing has since totally blown me away that I could not even believe what I was playing.
Any experiences like that for anyone else here?

Ack.. I spelled my subject wrong.. Can a mod fix that for me?? Lol....

For me, the biggest "wow" moments have been with Metal Gear Solid 4. The massive mech battles that take place, with the game looking as good as it does, are truly a sight to behold. I had a much smaller "wow" moment with Killzone 2, where I was taken aback by how good it looked, but I quickly forgot about that aspect and went on with the killing.
Portal was pretty great, too. The idea of opening rifts in space to move from one point to the other like that still has me flabbergasted, and sometimes I'll just load the game up to marvel at how awesome that is.

The interactive movie-ness of it actually bugged me. But that's getting off topic...
I was amazed the first time I played Ultima 7, it was just such a well done story, with good music too.
Also, when I got the CD version of Alone in the Dark. The voice acting was amazing, first time I'd heard that much VA. Every note and book was spoken.
Dune II - my first taste of RTS gaming.
Wolfenstein 3D - first FPS.
Basically, I've been awestruck often until more recently (I'm becoming a grumpy older gamer now!).
I watched games go from 4 colours upwards, from the PC speaker to top quality surround sound. The advancements in the past were huge.
Just watching the improvements made in each installment of the Sierra Quest games was amazing for me.

The end of Planescape: Torment; many other moments in the game too but the endind stands out the most for me.
Meeting Kelemvor in Mask of the Betrayer.
The battle against Yaga-Shura in Throne of Bhaal, with the complete Ascension mod installed.
The frozen whale in Icewind Dale.
The water in Morrowind.
Finding out I could carry chests in Divine Divinity, as long as my character was strong enough, and keep them in a village until I raised my lockpicking skill.
King of Dragon Pass in general.
First time I played Doom.
First time I melted the skin off of some ruffian in Fallout with my plasma-rifle.
First time my Valkyrie got Mjollnir in NetHack.
Discovering that the enemy actively tried to flank me in Outcast.
Hong Kong in Deus Ex.
First use of the rope arrow in Thief.
SHODAN in System Shock 2!
Many moments in Vampire - The Masquerade: Bloodlines but I vividly remember the werewolf ...
The very first Serious Sam demo and its huge number of monsters.
And last but not least: the first time I saw all those dots on the Daggerfall map and realised each was a place I could visit.

Monkey Island - not the 1st adventure I played, but the first 1 I really liked and the 1 that got me into adventures for a while.
Dune 2 - gave birth to a genre, no need to say more
Fallout (1+2) - only true RPGs I played and definitely belong to the best games I've ever played
X-Com/UFO Defense: the fear and frustration of losing your toughest members ...
Jagged Alliance series - keep going back to those
Pango and Digger - 2 games on my very 1st back in the good old DOS days. Still play them from time to time. Pango is currently sitting on my pc
Return to Castle Wolfenstein: Enemy Territory - my 1st online game ever and the game that got me into a clan. Played it about an hour ago
Civilization 2 - for the gameplay and the civilopedia
Full Throttle - brilliant adventure

Metal Gear Solid Series: Sniper Wolfs death, the pervading creepiness of Arsenal Gear, and the finale of the third game. Simply masterful.
Daggerfall: Realizing just how big the world is, after getting out of Privateers Hold.
Fallout 1: The ending. This should hopefully not need explanation.
Planescape: Torment: A game that actually lives up to every expectation story-wise. Intelligent meaningful dialogue, without ever getting so mind screw-y that you don't know what's going on, yet always having some deeper secret.
Mechwarrior Series: The pure feeling of power you get from loading up a 100-ton assault mech with the latest in high-powered weaponry.
X-Com UFO Defense: Too many to mention. It's hard not to get attached to your characters as they rank up, and it's hard not to feel sad when they die, which they will.
KOTOR II: Learning just how much Kreia manipulated you. (This, despite all the cut content which would have made it more amazing.
Freelancer: Taking on an entire fleet singlehandedly.
Tie Fighter: The (attempted) betrayal, where you're sent in to take out a minefield in a basic TIE Fighter with two not-so-friendly wingmen.
Wing Commander III: Finally taking out Kilrah.
Final Fantasy VI: The Opera scene, Cele's attempted suicide, most of the sidequests in the World of Ruin, all culminating in the confrontation with Kefka.
Suikoden Series: Taking out the emperor in I. Jowys betrayal, Luca Blights death, Neclords death, and any of the endings in II. Lulus death in III, along with the choosing of the Flame Champion.
Bioshock: 'Would you kindly?'
Homeworld: I'm sure noone needs me to elaborate on how good the beginning to this game is.
I'm sure there's a few more, but I forget sometimes what games I've played.

ending of hl2 ep2.
mass effect in general
world of goo's crazy cyberspace turnaround

Lemme think about that...
1. TES 4: Oblivion; That battle in which I had to run through a Daedric portal and stop a siege engine on the other side was an experience I won't soon forget, mostly because the thing nearly fell on me as I was making my escape. That added a touch of drama to the experience...
2. Jade Empire: When Master Li hit me with a touch of death mere moments after I rescued him. In spite of the Water Dragon's warnings, I didn't see that coming...
3. Freespace: The First time I saw the Lucifer, I was awestruck by its size. The first time it blew up a destroyer with its death ray, I was even more awestruck.
4. The end of Metroid: Prime was a major drama moment for me, not so much because I thought the ending was well done (and it was), but because I really expected to die fighting the final Phazon monster. That was one of the most intense boss battles I've ever had...
5. The cinematic endings of Fallout 1 and Fallout 2 always struck me as being really cool.
6. Grim Fandango was just so amazing, in general, that I'm not sure where to start pointing out particular cool moments.
7. Ditto for Psychonauts, although I particularly remember looking at the twisted geography of Boyd's mind with some fascination...
8. Half-Life 2 had more than its share of intense moments, mostly involving striders and/or massive explosions.
I've played many games, and seen many amazing things. These 8 are just the tip of the iceberg.

The whole first (realtime) days worth of gameplay from Ocarina of Time.
Getting Mew via the download thingy on Pokemon Blue! (sorry)
Getting the Megadrive out of the box, marvelling at it's aesthetics and playing Super Hang-On... If I could go back and relive that moment, I would do, over and over.

Actually the most recent was playing red faction: Guerrilla when I figured out you could litterly break everything. everything.
And playing fallout 2 for the first time after growing up in a lifetime of FPS. I remember thinking, holy s@*t I can do whatever I want.

Another big "wow" moment for me happened when I saw Command and Conquer 3's FMV sequences in HD. Before then, I had only dreamed of how awesome it would be if games included FMV sequences with better technology, and the results didn't support. By the way, Michael Ironside is the man.

Somewhat related, littering a city in a terror mission with thousands of incendiary rounds.

I have to echo what everyone's said about PS:T, VtM:B, Tie Fighter, Psychonauts (OMG Boyd was great, but I have to go with the Matador), etc.
Some others:
Metal Gear Solid: Psycho Mantis. I was baffled by the Castlevania bit (if you had a SotN save he says something like "I can see you like Castlevania") and it took me a week to figure out you had to use the other controller port.
Clock Tower (ps1) - so many great moments in that one. I dust it off every Halloween.
Shadow Hearts: Covenant - the part where you visit your psyche to unlock Neo-Amon. I hadn't felt that way about a JRPG since FF7-9.
Morrowind - again, too many to list but advancing through the guilds was great - especially becoming Archmage. The last dungeon of the Tribunal expansion is the greatest piece of level design I've ever seen. Just such an epic game.
Max Payne - I remember my jaw dropping at the first graphic novel cutscene and thinking huh, great idea. Probably my favorite PC action game.
KotOR. Probably the most addictive RPG I've ever played. Pazaak stands out though. The guy in the cubicle next to me and the 3 other guys I'd lent the game to in the office used to have pazaak tournaments when our boss was out.
NWN:HotU. The first time I was in the Reaper's Domain.
American McGee's Alice - Chessboard level. Still one of the oddest things I've ever seen. Amazing game.
7th Guest - OMG! Actors on the PC. FMV was so outrageously amazing back then. No, really. Does anyone else remember the first time they saw that game?