Into the Electric Castle has a fair few of these moments.
"Death's Grunt" in "Cosmic Fusion". The indian sees is lured towards the sun by a breeze, only to be confronted by death itself, who grunts a frightening, but very awesome badass boast. The combination of the deep pulsing guitars, the etherial voices, the atmospheric synths, and Death's haunting yet majestic grunt has been known to raise many hairs. Also could enter tearjerker territory.
The guitar riff in "Isis and Osiris".
The ending of "Day Sixteen: Loser". Over the course of The Human Equation, you get the sense that Me's tension is mounting as he confronts his personified emotions and tries to sort out what happened to him. Still, very few first-time listeners are prepared for the epic freakout he unleashes against the memory of his father as that memory taunts him over his predicament.
A similar, though less intense, denouement can be found in "The Sixth Extinction" with the line Stop the machines, PREPAAAARE TO DIIIIIIIIIIE! After learning what we do about the Forever beings, their reasons for using the Dream Sequencer and their interactions with humanity, to hear them arrive at the decision to finally end their race is pretty heavy. Though depressing, the force of that one line's impact still qualifies it as a CMOA.