A few of my personal favorites:
Portal 2. That moment where the ceiling opens up and you see the moon in the distance, I just paused for a second and thought “…no, there’s just no way…” Then when it actually happened it was just everything I could have hoped for. I was grinning like an idiot, appreciating how what seemed like a brief throwaway gag came back full circle.
Dead Space - This basically pulls off every single pulp sci-fi trope all at once yet somehow manages to make it fun an engaging. Betrayal? Yup. Plot twist? Uh-huh. Sudden, brutal resolution? Absolutely. Gigantic, disgusting monster? And how. Fun little stinger right before credits? Of course. The final fight doesn’t overstay it’s welcome, it doesn’t give you a needless exposition dump at the end, and the game goes on for maybe three minutes after you no longer have control. It’s just so tight and knows exactly when and how to end.
Bayonetta. It ends about a dozen times, and each time just escalates it even further. You thought it was over when you navigated a lipstick bullet right into that dude’s face, then you thought it was over when you killed a god 1,000 times your size, but no. It’s not actually over until you rescue Bayonetta as Jean by riding a motorcycle up spent solid fuel boosters into outer-space then riding the broken debris of a skyscraper sized idol through re-entry into the atmosphere. Oh, and you fight your way through the credits. For a game about excess, it saves the best for last.
Silent Hill: Shattered Memories. The less said about the ending the better because it is truly a punch in the gut that should be experienced. Reading about it does not have the same effect, it needs to be experienced cold. I’ve never been really moved by a game in a significant way, but the ending to Shattered Memories hit me hard, so much so that I was in an actual funk for over a week afterwards.