Is there any kind of logic to it?
Like guns a blazing is due to just 20 enemies coming after you (with nothing else to consider)? And insta-fails are due to another factor like if you get caught the plot means a guard presses a button and kills a hostage, so you failed or something like that.
Or is it completely random trial and error what is what?
No no, sorry maybe I didn't explain properly.
There are some missions and regions where it's an insta-fail if you get caught, you are either transported out of the stronghold if free-roaming, or if its in the middle of a mission, it's like dying and you restart check-point.
There are some scenarios more closer to what we see in games like Deus Ex where stealth is encouraged but if you get caught, it's not a mission fail and you can blaze through the rest of it.
And there has been at least one or two mission that start off like an instant fail on getting spotted, but after you go through a story checkpoint or two, your character just goes 'alright i think i'm safe to fight now', and from that point onward in that mission, you are free to get into open conflict.
The main issue, that even the director agrees with, is that you're given a couple of instant-fail stealth missions at the very start after finishing the prologue and they feel a little too unfair because that early in the game you have none of the equipment that you can get later that makes it easier to stealth around, disguises etc. So it hits extra hard when the game leads with those kind of instant fail missions.
There's also some other QoL things lacking like you can't hide bodies you've knocked out, so they might get spotted by other patrolling guards.