I would definitely say the nuke scene is up there with the most atmospheric moments in gaming. Much like Aeris' death it was a sharp, horrifying twist that just bolted out of nowhere. This isn't a CoD4 thread though.
It was the music that did it all. They couldn't have done a better musical score to accompany the scenes in FFVII. So masterfully done in my opinion. The same artists (Nobuo Uematsu and co) hung around for future iterations of Final Fantasy but none of those stood up to FFVII's work.