One of the issues I have with these conversations is that we can never seem to just discuss specifics. We go from "how does describing how you climb a wall mean automatic success where rolling a dice means possible failure" to <poof> there's a rickety latter that can be climbed that was never mentioned previously. It feels like we either talk past each other or the goalposts keep moving.
So how about this scenario:
The PCs need to get into a warehouse. Their informant has told them that there's a back door but it's barred from the inside at night so it can't be opened from the outside without some sort of magic. If it comes up the DM reminds the players that knock can heard up to 300 feet away. If someone can get inside they can open the back door. You'd rather not kill any guards if it can at all be avoided.
Casing the place, the PCs notice several things
- There's a single guard but they're sitting about 10 feet away from the front door, sitting on a barrel slumped against the wall. You can't tell if they're awake or not.
- The streets at night are deserted, there are crates and barrels along with a wagon on the street. You might be able to sneak into the front door without being noticed, you can't tell.
- The warehouse also has a cupola on the roof for ventilation, you could probably get in that way if you can get up to the roof. There is a drain pipe for the eves on the side of the warehouse out of sight from the guard that might support someone but you're not certain. In any case it could be difficult climb up.
How would you handle this scenario?
TBH? I wouldn't describe the warehouse in that much detail BEFORE the challenge was entered into. The players would participate in a Skill Challenge (this can be straightforwardly adapted to 5e modulo you would pick DCs a bit differently than in 4e). First we define the consequences of failure, as well as possible consequences of tallying individual check failures for 1 and 2 fails. We would then elucidate what the goal is, in order to establish the basic consequences of victory.
Now fiction will begin to happen. The PCs show up at the warehouse (or maybe this part happens earlier/on a different day, etc.) and start casing the joint. Do they find some means of ingress? Checks can be made for that! OK, there's a drain pipe and a cupola, and the one guard on duty at night seems fairly inattentive, and there are often things located near the main entrance which could conceal a PC, but the side door is barred tight!
Going on from there, maybe the side door was the best gambit, and one of the PCs tried to find a way to sneak in and hide so they could unbar it later, but they failed! OK, so we now know that the other options are less good, second choices that we are left with as a consequence of the first failure. Maybe, as that failure was played out, the player foolishly acted in a way that really got the guards suspicious, next night there are 2 alert guards on the front door! OK, so its going to be the drainpipe...
Well, going up the drainpipe works, but the noise makes the guards even more suspicious. They don't spot you, but now they're checking the interior and the PCs have to wait a while until they tire of it. They're still pretty suspicious though, so now the rest of the op is 'lights out' and any noise is going to be a critical issue, we're down to one failure left. Well, eventually the characters succeed, but there was just no way they could get to all of the loot and not be caught, so they end up with a smaller haul, but they did succeed.
Obviously there will be more detail in actual play, but we cannot really HYPOTHESIZE that, it has to be played out. That is part of the nature of a Story Game, the fiction ARISES dynamically out of play, it is not usually all nailed down ahead of time (though I illustrated how the same fiction you described COULD arise).