The opening cinematic to Heart of the Swarm, which has Kerrigan sending down hordes of Zerg onto a Dominion planet, sending a swarm piling into a plaza and right into the middle of a line of Terran Marines and siege tanks, ending with a battlecruiser crashing into the city.
Siege Tanks in action. In full CG animation.
Don't stop there, keep going. How 'bout a siege tank being flattened by a rampaging ultralisk. Then a viking transforms into its mecha form, blasting the rampaging ultralisk with both gatling cannons (which itself is a moment of awesome for the cinematics), only to be swatted away like a toy. Then the ultralisk (at least I hope its the same one, since that'd be even more badass) proceeds to rampage its way into a line of siege tanks and marines while merely flinching from siege tank fire and stomping on a distracted marine. The zerg sure know how to 'evolve away' their weaknesses.
Basically, a CGI cinematic doesn't have to adhere to the Units Not to Scale aspect necessary in-game, and the creators used it to glorious effect. Ultralisks turn out to live up to their name, and then some. And the Leviathan completely lives up to the awed reactions it caused in Wings of Liberty - and we don't even get to see the whole thing!
Raynor's Big Damn Heroes moment, saving Kerrigan from Mengsk as he's torturing her with the Xel'Naga artifact and giving her the chance she needs to finally kill Mengsk once and for all.
"Change of plans!" *grabs Mengsk by the head and hurls him across the room, breaking the artifact's remote trigger*
Mengsk gets one in the final mission when he sends the Odin against you! And then as Kerrigan and the Swarm, you get to destroy it, knowing firsthand how powerful it is.
If you make your ability choices in a specific way, you can watch the Odin fight a Leviathan.
Mengsk's death. After everything he's done throughout the series, after all the people he's murdered, the entire planets he's destroyed, and all the various war crimes he's committed for the sake of personal power, seeing Kerrigan finally, finally take her vengeance is extremely satisfying.
The campaign as a whole. You're reminded of how Arcturus owes his life and empire to the mercy of the Queen of Blades (ever since Brood Wars). The moment the Queen goes on the warpath against him...
The Zerus missions. You re-invade the Zurg homeworld and start kicking ass on a death world. In addition to the impressive insight into the origin of the Zerg, Kerrigan regains her status as a Physical God and starts eating Eldridge Abominations to boost her power level. In the Terran Campaign, Tychus remarked that Kerrigan had everyone "running scared". Now you'll know just how right he was.
Remember that Psionic scale, going to ten, on which the Bitch of the Universe Queen of Blades ranked a 12? Well, when Kerrigan contacts the Hyperion after having gone through every Primal Zerg Pack leader, the adjutant has one thing to say: Kerrigan's power is off the charts.
One of the abilities you can choose for Kerrigan to use in a mission? Summon a Leviathan. 'Nuff said.
Near the end of the Campaign, Valerian calls Kerrigan out on her reckless behavior, even while fully aware (from personal experience) that she can kill him with a thought. Kerrigan, who has personally hunted and killed ancient abominations, calms down and listens.
Kerrigan: You've done some growing up since Umoja. Valarian: Zerg aren't the only ones who can adapt.
Valerian tops himself a short time later, when he convinces Kerrigan to intentionally put herself at a severe tactical disadvantage so that he can save Terran lives. He essentially got The Queen Of Blades to obey his orders twice, without anything to offer, bargain, or threaten her with. Kerrigan even admits that Valerian is a better man than his father, after she recognizes that he was truly concerned for his people.
Kerrigan: I was wrong, Valerian. You're nothing like your father.
It's not the last time either: Just before the final battle begins, Sarah contacts the Hyperion and tells Valerian and Matt that the time for evacuation has run out, and she can't afford to hold back any longer. Valerian urges Kerrigan to avoid civilian centers, and after claiming that she can't do that, because Arcturus would see the pattern in her movements and use it against her, Valerian plays the 'Millions will die' card. Incredibly, for all the additional difficulty it will now pose, and despite being the Hive Queen of a Horde of Alien Locusts, Sarah actually backs down - she'll avoid the civilians if they don't get in her way. That's right Ladies and Gentlemen, Valerian, having nothing to offer in return and being severely outclassed in the military department, makes The Queen of Blades follow his will not once, not twice, but three times!
This can also be seen as a method of redemption for Kerrigan to keep from mercilessly slaughtering innocent people as well to try and show Jim she is not like the old Queen of Blades at all. Making her complete victory over Mengsk even more of a moment by taking on the best troops he has to offer while limiting civilian casualties. The later hard even when you focus on it in a battle.
It may have taken ten long years, but Alexi Stukov finally got his revenge against Duran/Narud for what that bastard did to him during Brood War. Needless to say, long time fans were cheering throughout the battle.
On that note, Kerrigan killing Narud. The Primal Queen of Blades gets to show off her newfound power (including creative use of a Nydus Worm) against a shapeshifter who plays on her weaknesses. First he takes the form of Raynor, which only succeeds in pissing Kerrigan off even more ("You are not Jim. You are nothing!"), in spite of him dropping a huge rock on her. Then Narud shifts into Ghost Sarah to remind Kerrigan of everything she lost. In the end, it ends with the Queen of Blades using her "wings" to impale them both.Revenge Before Reason made awesome. In all its spoilery glory here.
Narud himself gets one in that the Nydus Worn didn't seem to scratch him, and when he causes Kerrigan to hesitate, he creates an energy blade and impales her. Unlike the boulder, this does hurt her, a lot, causing Kerrigan to pass out from her injuries after she kills him.
While we talk about Narud. Think about it, his whole participation since Brood War was him winning and fulfilling his objective. He dies, but Amon has been revived, and not before Narud had to manipulate basically every single faction in the games.
Awesome points for Sarah herself as (mentioned above) she IMPALED herself to kill Narud. Basically, it answers the question: What happens when the Queen of Blades stabs herself?
"Enemy Within" — in order to destroy a Protoss shuttle, Kerrigan infects Lasarra with a Zerg larvae, then lets the templar warp her to the ship. The larvae bursts from within her, hides from the Protoss as it slinks around the ship consuming energy from test subjects, and ultimately morphs into a new broodmother named Niadra. She then creates a legion of minions to aid her and single-handedly destroys the crew and infests the ship. Not only is it a fun mission, it's also a magnificent piece of cunning from Kerrigan, and the overall mission emphasizes the key Zerg aspects perfectly — infiltrate, consume, grow, destroy.
The final mission. You're facing off against the Dominion from a rather small base. The situation looks grim. Then what happens? Raynor comes down in the Hyperion and rains absolute hell down on an entire Dominion base and utterly destroys it. Your chances just got a lot better. What's even better is that right before this happens, the theme song that plays is Card to Play, the same theme song that plays during the cinematic where Raynor pulls his Big Damn Hero moment on Warfield, saving him and the Dominion Troops from the Zerg.
Installation Missions tend to be pretty fun. The "With Friends Like These" mission takes it to a whole new level. The entire mission is in space, you have a constant squad of ships backing you up, and you're playing as the Hyperion. It's great fun and unbelievably cool.
For the next mission, which involves freeing Raynor from a maximum security prison, Horner suggests sending in Tosh (or their trained Ghost agents, if you sided against Tosh in the previous campaign) and be as sneaky as possible. Kerrigan tells him not to bother and proceeds to gracefully waltz in trough the front door with her zerg behind her. Kerrigan no longer needs her cloaking skills. Which involves having her Leviathan grapple the prison ship, punching tentacles through the hull and sending in waves of Zerg reinforcements as needed while Kerrigan blasts her way around the various decks.
Valerian: Be careful Kerrigan, my father will have made every precaution to ensure no one reaches Commander Raynor. Kerrigan: He's not prepared for me.
Stukov's entrance into the final battle once you've eliminated the terran outpost keeping his forces out. You destroy that place, and an army of infested terrans start staggering into the battle zone.
The final unit upgrade mission, starring Ultralisks. You get to absolutely curbstomp a city with the biggest, meanest Zerg troops on offer and they won't stop, even as Mengsk fires warhead after warhead at you, panicking all the while. Mook Horror Show at its finest.