Don't ask your players to roll the dice unless failure has a consequence.
This mantra isn't particularly obvious in the rules of D&D as many times the checks are relatively pointless and failure at those checks doesn't really come with much cost. Failure is an opportunity both for you as the DM and for your players. Here are some ideas as to how to make failure have a cost in the situation you describe:
- A second attempt at picking the lock costs time, and the life of a hostage.
- The lock is ruined and the door must be forced causing noise and alerting the guards inside.
- This door is not an option and you have to find another way into the building.
These are three ideas of things you can have in mind if the check fails. The important thing is that failure should cost something (not a lot, it's 1 d20 roll, but something). Make it something small, the next encounter is slightly harder, the opportunity for surprise is lost, a later plot consequence etc.
Nothing major should depend on your character succeeding a skill check. This is because skill checks fall afoul of the Goblin Dice concept, which is that important things should not be decided by a single swingy die roll.
Don't let failure stall the action. Use it as an opportunity to move it forward, make it more interesting and to raise the stakes.
The DMG does not offer any direct guidance on how you should narrate outcomes, but it does offer a few bits of information that may be helpful here. All of these are found on page 242 of the DMG.
It specifies that most checks are straightforward, pass/fail checks, but gives a few options for "flourishes and approaches" that you can use. It offers the options of Success at a Cost (used if the roll missed the DC by a narrow margin) and Degrees of Failure (to indicate how badly you failed that attempt).
From this, we can determine a simple fact about any ability check, save, or attack roll: The numbers on the die determine how well you did beyond a simple 'pass/fail.'
The DC is the low-end threshold to succeed in what you were trying to accomplish. If you consider the rules mentioned above, it specifies that nearly hitting the DC is cause to still allow a borderline success, that has negative consequences...and missing the DC by a large margin means a more spectacular failure.
Based on that, if you wish to fluff up your roll resolution description, you can consider the roll result's proximity to the DC to be a measure of how well the attempt went.
To take your example of the flip...
Barely missing the DC could be exactly as you described...you almost pull off the flip, but fail to stick the landing.
Missing the DC by a lot could mean you slipped while trying to start the flip, and simply landed flat on your back
Hitting the DC dead on or slightly higher means you successfully turned your flip and stuck the landing
Surpassing the DC by a broad margin means you made that look effortless.
The same sort of thing could be applied to attack rolls, saving throws, and so on. In games I run, I do exactly this, narrating outcomes based on how close they were to the DC.
However, I will add this: once your players realize what you are doing, it's going to start giving them hints as to what the DC is. I, personally, think this is a good thing. If a PC makes an attack roll, gets a 14 vs an AC of 20...I may describe the attack as having been 'contemptuously swatted away' or 'harmlessly glancing off their armor.' If it's a 19 vs an AC of 20, I may describe the attack as having been 'diverted at the last moment by a quick twist of their shield' or 'the creature hunkers down at the last moment and your blade scrapes across its armored shoulder, narrowly missing its mark.'
And, conversely, I may describe a roll of 22 vs an AC of 12 as the player 'deftly slipping past their defenses' or 'smashing their weapons aside to land a blow.'
This gives the players an idea of what they are up against, and I think it greatly adds to the gameplay experience. After all, it's much more flavorful to know how close you came to accomplishing something, or how far you surpassed the difficulty...than to just get a simple "You fail." or "You succeed."
Best Answer
In many cases, for published adventures and challenges, D&D will use multiple DCs. This is especially common for knowledge or perception checks.
For example, with a DC 10 knowledge check you might know that trolls regenerate damage; with a DC 15 you might know that fire prevents said regeneration.
When designing challenges as a GM, you could extend this to other challenges. Make opening a lock at the cost of breaking your tools DC 18, while opening the lock without negative consequence would be DC 22.