The one with the most dramatic build-up for me is "Join the Cirquis" in Cirquis Voltaire. A true extravaganza, working through multi-tiered progress.
The hardest to get to, is a toss up between Lord Of The Rings's "Valinor" or The Simpsons Pinball Party's "Super Secret Wizard Mode".
The wizard mode with the best audio & production values is "Monsters of Rock" in Monster Bash. They have an entire full-length song recorded just for this mode.
The wizard mode with the best lighting effects for me is "Power Down" in Johnny Mnemonic. Game lighting is effected by gameplay, during the wizard mode, as you try to feed power to the dying pinball machine.
The wizard mode with the best software integration is "Final Frontier" in Star Trek: The Next Generation. Artifact collecting during the normal game, in order to effect the wizard mode scoring is STILL an absolute genius idea; kudos to Dwight Sullivan and Steve Ritchie.
The easiest to understand wizard mode (for novices particulary) is probably the "Stiff-O-Meter" in Scared Stiff. Hit the lit shot, then hammer the crate.
The wizard mode that gives the player the biggest adrenaline rush; "Lost in the Zone" in Twilight Zone. Non-stop multiball on a timer! Pure madness! Jackpot explosion inspired by the same crazy-loud one from Addams Family. Lot of times, when you have friends on a TZ, you might hear "Ooooooooh sh*t, here it comes!!!" right before hell breaks loose.
The best reward for completing a wizard mode, I would have to go with "Battle for the Kingdom" in Medieval Madness.
King of Payne: "NOOOOO! You have destroyed my castles, but you will never destroy me!"
Merlin: "Well done! You have freed the people, and brought joy upon the land. Congratulations! You are the new king!"
People of the land: "Yeaaaaaaaaaa!" *cheering* (little farmers dancing & celebrating)