But not specifically of the horror genre.
My moment was when I played The Legend Of Zelda: Link's Awakening where he crashes on an island and at the last quarter of the game you find out inside a temple that everything on the island is a dream, but the really weird part was the the people on the island were completely unaware of it and when the deity of the island woke up and you saved it, the people and the island all just vanished entirely from existence, ending the game on a really melancholic note.