If you were in the moment, in that time, the reveal in KoTOR was pretty epic

The end of Xenogears' intro area/story arc when the trout hits the fan, that was pretty epic

Shadow of the Colossus. I was gonna say seeing/defeating your first colossus. But that feeling doesn't diminish throughout the game, most of the fights are epic. I can think of like ... one, which wasn't all that epic. Way to go on that game

While it's still my least favorite in the series, I think the opening in Saints Row: The Third was pretty stellar

Xenoblade Chronicles had two pretty blood-pumping openings in one