This is starting to sound pretty Cliche, because everyone's saying it but... KotOR. The twist is masterfully executed. There are JUUUUST enough hints at it that it isn't unbelievable, but there are JUUUUUUST few enough that you're most probably not expecting it. There's a reason why it's the game I've replayed the most times (Despite it NOT being my favourite. That would be Deus Ex).
BioShock's was pretty sweet as well, but I think it ruined the part of the game that came after it. The story lost all impetu, the gameplay became bland, and, at least I, didn't give a damn about anything. If it had been about one hour into the game later, it would have worked a lot better. I wouldn't call it the best twist out there, because good twists are supposed to make the part after them BETTER not completely horrible.