Knights of the Old Republic -
The Legend of Zelda: The Wind Waker -
Terranigma -
You are Revan. Found this really cool, and unlike anything I'd seen in a video game before. Even though it gave you clues along the way, I didn't really take notice of them. The way it was revealed by showing all the different clues in a flashback was great too.
The Legend of Zelda: The Wind Waker -
Not sure if this is really a plot twist, but the part where Ganondorf actually completes the Triforce. Took me by surprise, because in every game, the fight begins before there's actually a chance for Ganon to do that, or he did it at the beginning of the game. Also was surprised that instead of restoring Hyrule, Daphnes drowned it.
Terranigma -
Ark rebuilds/saves the world... and dies*. Sad, yet also well done. I think that's the only game I've played where the main hero dies at the end.
* = can be interpreted in another way.
* = can be interpreted in another way.