90% of the battle against the Dragon. Sure, it starts off a bit weak with you running away as he chases you, but he will eventually get stuck, allowing the Arisen to beat on him until he breaks free. He then fights the Arisen on the ground for a bit before taking flight and tearing the castle apart, leaving the Arisen with no option but to run along collapsing walls, making for a ballista to get the Dragon back into striking distance. After taking a ballista bolt to the chest, the Dragon is unharmed, but really angry, so he dives at the Arisen, who promptly jumps onto the Dragon to transition the fight into phase three, which consists of a Colossus Climb towards his heart, while he tries to shake the Arisen off by flying through canyons, flocks of harpies, and even above cloud level. Upon striking the heart, the Arisen rides the Dragon's stunned form back down to the ground to finish up the battle in a massive crater as reality itself starts tearing at the seams.
The Dragon is something of a One-Scene Wonder, in that while he only appears a handful of times throughout the game, every encounter with him is memorable, and more than that, everything he says is pure undiluted awesome. His earth-shaking Badass Baritone is truly what clinches it. A true hats-off to the voice actor, David Lodge.
Made all the way more awesome with the revelation that the Dragon was once an Arisen, just like the Player Character. He went through everything you do, up and including facing the Seneschal. Even though he lost the fight, you could really tell that he is genuinely seeking for a worthy successor to finish what he couldn't.
The first time you take down any of the climbable enemies when you had to run like hell from one before. Especially notable are encounters with a griffon, chimera, or drake. The fanfare that plays when you drop your foe at last is just icing on the cake.
Fighting the Ur-Dragon would surely count as well. While it doesn't play itself like a series of action sequences culminating in a fight, like the Dragon, it does make Colossus Climbing a necessity. It has weak points on essentially every part of it's body, all of which need to be destroyed to kill it. The fact that it's huge and, when you damage a weak point, it's skin gradually gets torn away, making it look like a zombie dragon, just adds to the spectacle. The desolate battlefield with patches of purple flames is also a brilliant stadium.
Warning: This is a major spoiler, but also a moment of incredible awesome. You are warned. Facing down Savan the first time makes for an incredible fight. But go through the game on New Game+, and the Seneschal this time... is YOU. Your Arisen from the first playthrough stands before you. You knew it was coming, and yet it remains truly awesome. The epicness of the following line is increased when hearing them come from your previous player character while your previous Main Pawn also drops in for the fight:
"Come, Arisen. I shall meet you on your own terms, joined by my own companion of old. You stand now at its end, Arisen. See your journey through!"
In Bitterblack Isle, you can eventually encounter a dragon in the second open-air courtyard. Because of the split level nature of the room, it gives you one of the very few places in the game where you can climb up high enough to jump and grab the dragon in midair! This feels super awesome when you are playing a melee class that usually has a hard time doing anything to a dragon in mid-flight.
The end of the "Deny Salvation" quest, specifically the moment when the Dragon crushes Elysion in the middle of his speech. Not only is it satisfying to see the Smug Snake get reduced to paste in an instant, but the Dragon's next words sum up his importance in the grand scheme of things.
Dragon: The rantings of an upjumped zealot make for tedious listening. His ilk serves no role in what is to come.
When fighting the Archydra, it's very likely that all of your pawns will be swallowed, leaving you all by yourself, possibly for the very first time since the game started. Yet, if you use careful tactics, timing, and have enough patience, powerful enough spells or weapons, you can take down the beast by yourself! And considering how you've had pawns helping you out in every single battle before (unless they were killed), it's extra-awesome knowing you can take down such a leviathan without any help.
Even crazier than that is if you yourself get swallowed. You are completely helpless to do anything, just sitting there with a dead controller in your hands watching as you slowly sink down into the belly of the beast... and then your team of Pawns kill the thing. Your team of what could very well be a bunch of midgets with giant blue afros wielding swords taller than they are slay an Archydra by themselves. Saving your ass.
The Arc of Deliverance/Obliteration. It's the strongest greatsword move in the game, takes 20 seconds to charge, and once charged unleashes an overhead swing that can accurately be described as a nuke in sword form.
Speaking of nukes... Magic Knight has the ability to enchant his shield with various elemental buffs. Parry an attack while the shield is enchanted and watch the fireworks go off.
The battle against Daimon. You, the newest Arisen, facing down the legendary Dark Arisen, your wills clashing against each other. Once you enter the battle, you are not greeted by a rocking battle theme, but rather the more ballad-esque and somehow melancholy 'Coils of Light', which is perfect for Daimon. Not only is he incredibly fast, dishing out punches, slams, and spin-kicks, he's also able to cast the most powerful spells with nothing but a nonchalant swipe of a hand. You'll need to pack all your good gear and tons of healing items/Wakestones to survive the onslaught. And his second form ups the ante even more!
A somewhat minor example, but one that is certainly able to catch you off guard. In an out-of-the-way area, Soulflayer Canyon, you'll find yourself fighting hordes of enemies, including a golem (in one of their rare appearances). It's a brutal gauntlet of fighting, rendered even more difficult by the fact that the canyon is so twisted that you barely have an idea of how long it is towards your goal. And then... Bluemoon Tower. A beautiful ruined city on the coast after miles of claustrophobic canyon. Given how out-of-the-way it is, and how you only need to go here for two side quests, it's a moment of awesome for the developers as well.
A meta example. Despite being initially a flop, the game goes on to become Capcom's fastest selling PC title due to word of mouth alone. Go to any forum and mention this game, chances are that people will instantly express their wish for a sequel.