Agreed with the COD4 nuke scene - Also AE's ending.
I could probably think of several of these, here's a few for now. Perhaps the most epic GTA mission ever:
Portal 2's ending
(WARNING: MASSIVE SPOLIERS). I mean, just... Wow. I hadn't felt that blown away by a game ending in such a long time.
Oh and don't forget the vault door:
Half-Life 2 when you're carried through the citadel - It's a lot better in context as you play through the game:
And speaking of Half-Life, HL2 and episodes had some pretty decent moments - Valve really do know how to make good games. What's notable though are the
first 2 chapters of Half-Life 2, Episode 1 were pretty damned amazing, when you return to the citadel - I found myself replaying those 2 chapters several times.
Hmmm... Oh yeah. The human ending FMV in Warcraft III blew me away too during 2004:
Okay sorry I'll stop now. :P I'd love to post stuff from Beyond Good and Evil and Psychonauts here, but since I found the entirety of both those games to be epic, no one part of either of them really stands out for me. :S