It's entirely possible to read it in good faith as a failure is catastrophic - and at other times equally possible to understand D&D rules as "you can just keep rolling until you pass" again in good faith. Your example of the guard spotting you shouldn't be a catastrophe - what happens next? You bribe the guard? You intimidate them? You knock them out? There are now more questions.
I think the big problem here is that Hussar has this like mental image of a heist that doesn't really match what other people (except maybe tetra) have. Like, the way he's describing it, the second the guard realizes you're not who you say you are, he's forced to immediately radio in to his colleagues or start hollering at the top of his lungs or whatever, without even trying to ascertain the situation, and then bells start ringing and all hell breaks loose, and it's like if they have guards like that, and a system like that, you need a plan where that isn't going to happen (like maybe you need to dig into the vault or something).
But really, guards are likely to be people (sometimes they'll be monsters or things, but those have different ways of dealing with them). Most people don't want to be embarrassed, don't want to die, and don't like pain, and do like being alive, money, safety and so on. They aren't simply metagame constructs or suicidal maniacs (I mean, mostly).
This embarrassment thing is particularly important. People don't like to cry wolf, and the sort of people who become guards are disproportionately likely to be either lazy or bullies or both. This isn't theory - if you look at real-world heists a lot of them succeed because guards are lazy and guards don't want to be embarrass themselves by unnecessarily calling other guards, let alone the police. Fear is a big motivator too - that can work for you or against you.
If a guard sees through your disguise, he probably still doesn't know who you are. If you're on your own, and look non-threatening under the disguise, he may well attempt to constrain or bully you, and might get another guard to help (depending on the culture of the place), but probably isn't ringing the alarm. Or he might try and get you to reveal who you are. If he sees through the disguise and realizes that there's a decent chance you're extremely dangerous, especially if your group outnumbers him, he may well attempt deception of his own as he tries to get away from you, preferably putting an impassable obstacle (like a heavy door) between you, and then try and raise the alarm, because he doesn't want to die horribly or be turned into a frog or whatever. Or he may try to get you to leave, continuing to pretend you are who you said you are, but saying that he needs to wait for his manager, please come back later or w/e.
And all the details matter. If he has a silent panic-button (hyper-advanced for most D&D settings), he's probably going to press that if he's at all scared. Ultimately though, if he's a person, he probably just wants to go home today with all his limbs. So then we have to factor in what happens if he screws up? Is there a villain in charge of him? Is he in a position to torture/murder people who fail him? That can also be a double-edged sword - fear of the villain may make him obey the villain, but he may also be more willing to let the PCs through if they're going to kill the villain or create a situation in which the guard can escape (and bribery can factor in). Or he can just decide letting himself getting tied up and/or knocked out is the right way to go - most real people decide
exactly that in heists and robberies.
The ideal heist is you get in, take the thing, you get out, nobody even knows you were there - you were a ghost.
But many heists can never go that well, because for that to even be possible you need flawed security. And there are countless situations less ideal than that, but which are still successful.
(Hussar's scenario would make sense with fanatics, or well-disciplined well-trained modern-day soldiers guarding a military base, note. But it's not very broadly applicable.)