I know a lot of people prefer villains with interesting backstories and multi-dimensional personalities, but to me, the ideal villain is someone I "love to hate." They don't need to be redeemed or have a reason for doing what they're doing, they just need to have an awesome presence and just keep me wanting to see more of them. When a story can make a villain so twisted I want them to die horribly, but also can't help but want to see them even more, that juicy contradiction is just perfection.
Unfortunately, I can't think of many examples of that in video games, but some come pretty close.
Malos (Xenoblade Chronicles 2): Good example of the above. No sad backstory or any real reason for his actions other than his connection with his Driver, was basically just a bored guy with the power to destroy everything. And looked stylish as hell while he does it all. Little things like him winking at his fellow Torna members and laughing at the thought of fighting Mythra again just made me want to see more of this guy.
Ashnard (Fire Emblem Path of Radiance): Didn't get a lot of exposition, but what little we saw of him was really powerful. The sequel gives more motivation to his actions, but still leaves him vague enough to remain an intimidating force that set events in motion to change the entire world. While it breaks my rule above slightly, I find it interesting that he essentially was in the right all along, in his own twisted way.
Ghirahim (Zelda: Skyward Sword): Another stylish villain who I couldn't help but like to see. Creepy as hell but man was he cool to watch, and his true identity was so satisfying given the nature of the game.
Ganondorf (Zelda series): This one is probably a bit hard since his personality and character vary across games, from mindless evil to classic villain to actual tragic character, but overall I can't think of video game villains without thinking of this guy.
Hades (Kid Icarus Uprising): Mostly a joke villain, but still was funny enough that I enjoyed seeing him all the time. His banter with the other gods and the final showdown (PIT! HADES! PIT! HADES!) were hysterical.