Speaking of 'holy sh!t moments' and NWN (Hordes specifically):
When you finally beat that evil drow lady, and then Mephistopheles pulls a fast one on everybody.
Or when you finally reach the Knower of Names... one of my favorite things about NWN is that there's a lot of stuff you never see in person. You only hear about it and read about it, and so you have to use your imagination. Excellent case in point being all the legendary stuff you hear about in the 8th hell: the Sleeping Man's journey, Meph throwing those twelve folks hella far, etc. Except that you hear about this stuff and imagine it and get hyped up, and then you actually do
see it. Walking into that clearing in the snow, seeing those twelve craters, and realizing the importance of the place, how far I had gone, on foot, THROUGH HELL... that was fun. We need more stuff like that in games.