An early favorite moment in CV while growing up, and I think is a little tired...but
In SotN, first rolling into the Castle as Alucard. You're fully decked out and you're just making everyone your b**ch. You get the feeling of "yeah, this dude's pretty badass!" and then BAM, Death comes out and is all like "Naaaw dude, you gotta do this legit, no h4x for j00!", steals your stuff and jets. At first you're like "Ok...but I'm so bad I can deal...right?" and go encounter your next enemy, after a million punches later it dies, drops a knife and you're like "WTF OMG GIMME DAT, I SUCK SO HARD NAO"
Such a huge change in emotion and how you perceive yourself and your character, it's always stuck out to me as an awesome moment in CV.
Another sweet CV moment is when...
...in CV64 when Malus reveals he's the master of the Castle/Dracula. Woah! I mean, you knew something was...off with the kid, but being Dracula? Bravo Konami, bravo. I think Renon also turning into a super demon to collect your soul for his services was also super sweet.