At this I would tell the rogue they can continue the attempt but it will take time, up to 20 minutes (2d10 for my house rule), do they want to try?
Just as an aside, I once got locked out of my place as the wind blew the front door shut and it auto-locked. As it happened the only option was to call for a locksmith (my sister who had the spare was out of town).
The dude literally took 30 seconds to unlock the door. I dunno if that is standard but boy was that easy money.
Other scenarios would have different options. For example fail to climb a wall may not mean any HP damage (or it might) but you may also make a lot of noise as you fall alerting nearby guards. The difference is that the guards will always be there regardless of the climb check and could have been alerted in other ways as well.
I borrowed an idea from
@pemerton's game whereby 1 of the 2 PCs had their boot damaged by a Fail Forward check (think it was Perception) a few hours after having left the city. Unable to mend the boot, the PCs had the option of returning to the city or hoping to meet a cobbler or tradesman along the way. The damage boot was affecting their movement speed negatively. They decided to stay course and not return. That evening they joined an encampment of a group of travellers etc on the way to the city.
At the encampment the other PC managed to successfully find and buy a pair of boots off one of the revelers.
The reason to use fail forward to me is so that the ongoing gameplay doesn't come to a screaming halt because of a single failed roll.
I think either you or
@FrogReaver mentioned something about D&D being about overcoming obstacles. Well on that, I appreciate that Fail Forward provides a mechanic to generate those obstacles.
But even more so I like that it affords me the authority in the creation of those obstacles without having obstacles generated via
DM decides without a mechanical trigger.
EDIT: Lastly, I like that the mechanic can work in such a way to encourage the DM to being more creative with the obstacles. And a by-product of that being a greater engagement by the players, IMO.
And just to reiterate, I view this as a tool for my table not standard play for every roll.