To be fair, one thing Caius has going for him is the aspect of "prescence." When Caius appears, the game makes a major shift in tone, and even the music has that feeling too. I'll grant trying to make him sympathetic was a dumb move; its really hard to get behind someone whose entire goal is basically KILL EVERYONE for the sake of one person, and that one person doesn't even want that. That would be Caius' failing.
If the motive was more just a starting point, and it was a "devolves into insanity" style villain, to the point where "no, you're just evil", which frankly the way Noel and Serah treat Caius in the game's ending feels like was going on ANYWAY, that would have worked far better. Instead, yeah, Caius is definitely someone the game tries to make us sympathize with because "He's not really a bad guy, honest! He's just misguided!", which it fails at.
Caius does have all the other traits needed at least. Going down the list...
-Presence. Its hard to deny that Caius doesn't steal the scene when he appears, and become the focus of everything. Whether he's enjoyable during this time is a different argument, naturally, but he definitely changes everything.
-Credible Threat. The opening displays him as being good enough to hold his own against a Suped Up Lightning, but he also clearly is stronger than Serah and Noel (though oddly, he has no "Unwinnable/Optionally Winnable fights", which is weird because the outcome, plotwise, could have supported that easily)
-Personal Impact on the Hero. Self explanatory really; everything you do is because of Caius' actions.
So...the only one I feel he fails at is the Motive one, which goes back to the whole "they want him to be sympathetic, even though he's not" thing, since the motive is WHY he fails at being sympathetic. To be fair, his motive could have worked if his goal wasn't so obviously horrible. If it was sold as more of a "Less than ideal outcome" rather than "Worst possible fate one can imagine", it may have worked. But I digress, its kind of impossible to sympathize with a villain whose motive is "Blow up the world because he's sad."
If you want a sympathetic villain whose goals are incredibly drastic, but you can at least understand his side (though still not side with him), look no further than Fou-lu. Yeah, doesn't qualify what CK wants, but he's a good contrast to Caius if nothing else.
On a different note, I will say one thing about the article:
He does label out WHY Sephiroth is an effective villain in a decently thought out manner, not in some fanboy raving "He's awesome" style. He establishes criteria, and shows how Sephiroth fulfills them, and why he works. Its sadly rare to see this for someone like that.
Re Snowfire on the 11th Hour thing:
He actually acknowledges that some. He says the villain can appear at the end IF they are hinted at throughout the game, if there's some reason to believe there's someone behind them, etc.
And honestly, Necron was not meant to be anything but "a big final boss fight." They wanted Kuja to have one last act of redemption in the game's ending, and as such, you needed one guy who wasn't Kuja, so Kuja could rescue the team from him (game blatantly states Kuja saved the crew.) There are a bunch of far better ways they could have handled this of course, and Necron is anything but ideal, but FF9 doesn't pretend he's more important than he is. I think the best theory I've heard is that if FF9's theme is about LIfe and Memories, Necron is the Anti-thesis of that, being an Incarnation of Death itself. And given that's a shaky reason at best, and its the best explanation of his existence I've heard...you get the idea.
His example of 11th Hour villain doesn't really include "Giant Monsters for sake of final boss fights." That feels more like an N/A factor. He means genuine characters who replace other villains when there's no reason behind them.
In truth, Zemus is a pretty shitty villain for this reason. Oh, now we can forgive Golbez...even though there was no reason to care about him, or anything like that beforehand. The "Brother" reveal didn't come until way too late for me to care about that.
I hate to use this example, but Ultimecia controlling Edea was better from the "Puppet Master" perspective. FF8 reveals BEFORE the fight with her that Edea use to be a nice person, and now your team goes "wait, why did she become evil? And why did she create an organization that is now her arch nemesis?" It leads to a bunch of unanswered questions, and then revealing "Ultimecia was the true culprit" is the answer to all of those.
FF4 lacks any of that. FF4 is just "Golbez is the bad guy! ...no, just kidding, Zemus is the real bad guy, Golbez is puppet. Oh by the way, he's also your brother."
And before someone uses the "argh Orphanage Scene!" argument, discussing this a bit, I agree the scene would have worked if it was relegated to just Squall, Seifer, Edea and Ellone. Including the entire cast (Sans Rinoa) kind of killed the scene. Giving Squall a personal connection to Edea is kind of needed nonetheless though, as its the only way you can pull off the Ultimecia reveal.
(now, Ultimecia's failures stem from a whole lot of other factors, but lets not get into that)