For whatever reason the entire epilogue in Lunar 2: Eternal Blue Complete really worked for me. The game has a sad ending that isn't 100% satisfying, then after the credits you finally get a full party incl. Leo, a fully-explorable world, new items and collectibles in virtually every corner, and three meaty dungeons before a new final boss that gives you a happier ending. Great stuff.
The red moon episode in Disgaea: Hour of Darkness, and its resolution in the game's Good Ending. What a silly game to get all teary-eyed about. The Disgaea series is all about grinding, bizarre humor, and making big numbers roll across the screen, but each of them surprised me with its heart.
And damn, I'm going to avoid spoilers even though it's 14-15 years old, but the special event at the midpoint of Castlevania: Symphony of the Night. You think you have things figured out, you explore the castle in full, you find the two rings, you equip the magic glasses, and you fight the boss at the top and then... you realize you're just halfway through. My favorite videogame plot twist of all time.