Metal Gear Solid 4's final boss (And ending), Uncharted 2's final stretch, and The World Ends With You had several moments.
However, there is one moment in video games that truly stands out in my mind. (Maaaaajor Knights of the Old Republic spoilers here) In Knights of the Old Republic, when Bastila turned to the dark side. It was just so shocking, seeing the person I had traveled, bonded, and romanced with throughout my entire journey. I did everything I could to convince her to change back, and she refused. Finally-- when you arrive at the Star Forge, and have to face her in battle. It comes down to the wire. She has nothing left. Either you turn her back, or she's going to die. It really struck me as just a beautiful moment in... anything, really. It's just one of the reasons why KotOR is my favorite game. And that's not even it's only beautiful moment, it has plenty. That's just the one that really stood out to me.
And then, of course... there's Red Dead Redemption's 'moment'. And that's all I'll say about that.
EDIT: Heavy Rain! Oh, how could I forget Heavy Rain? That entire game is beautiful. And I WOULD have said Okami, but that one's kind of a given and I would feel generic.