Planescape. Not really the ending, but the entire last half of the game. Shadow of the Colossus had an interesting twist that makes you think for a bit. Metal Gear Solid 3, when Naked Snake learns Big Boss's true motives and understands her sacrifice. The last 30 minutes or so of MGS2 were also pretty ridiculous.
I can't really think of a game where the ending made me go "holy trout I need to think about my life."