I would have to say that under Rands' definition of a Holy Shit Moment, Morrowind would have to be the fastest, hardest-hitting, and most clear cut example of a gaming Holy Shit I have ever experienced. In fact, by those three criteria (rapid-realisation of Holy Shit value, deepest implications, ease of identification) I'd say the only other Holy Shit that is, well, a bigger Holy Shit is nanotechnology.

That being said, I'd like to propose some sort of category in between the Holy Shit and "that's cool" response, in order to cover the art of storyteliing. You know, those books, films, or games where afterwards you have thought "holy shit." They're not Holy Shit moments in that they're not going to change your life, but you are just in awe of a story being told so well within the parameters of the medium. Examples: a book, Diaspora (Greg Egan); a movie, Apocalypse Now; a game, Half-Life.

Half-Life isn't half the game Morrowind is, and yeah, you're on rails, but fuck, that's a good story.