As a kid, Ocarina of Time and 007: Goldeneye were pretty 'breathtaking' just because of the technology involved. Mostly, pushing games into 3D.
But as for games where I truly felt such an experience:
Dragon Quest VIII: That first time I walked out into the overworld and heard the combination of the overworld theme and saw the expanse of the world stretching out forever is an experience I will never forget. And I never got over for the 160 hours I played the game.
Resident Evil Remake: Much of the game amazed me graphically when it came out. But when I first walked out into that graveyard on my way to Lisa's house and saw the rolling fog through the brush, the moonlight and the shadows...it was the first time I truly saw a game I felt looked 'realistic'.
Edit: Now updated with video! Fancy.
Dragon Quest VIII - Exploring the Overworld (Though its using DQII Music)
Resident Evil Remake - Jill on the path to the Cabin
Six upcoming games you should look into:
