+1... Sort of, to the OP.
First, it is a common misconception that Nat 1 is EPIC failure and Nat 20 is EPIC success. This is not true. Nat 1 was created in 5e for the sole purpose of making it so that even if you could hit an enemy in combat automatically, there is still a 1 in 20 chance that you'll miss. It's to make it so that in certain circumstances, for combat only, you might actually miss, even if your attack bonus would have you hitting every time. Nat 1 and Nat 20 do not carry over to non-combat skill checks and so forth. It is only for combat in 5e.
Secondly, HP symbolizes the weakening of your character, stamina loss, armor damage, etc. You can literally get a Crit Nat 20 and do 2 damage. Hardly worth an explosion of blood if your target has like 50 HP. It symbolizes getting hit in a weak spot but not hard.
Now, if you do 20 HP damage in a single hit, and your target has 25 total HP, THAT'S a serious blow. That would be worthy of a close up smash in the head with a mace, or showing you slash someone really good across the chest with an explosion of blood. Even this wouldn't be a total stab through the chest or lobbing a head off, but it would be a considerable hit that could constitute a close up animation. It's not about the attack roll, it's about the damage roll.
So, I think the amount of blood and such that you see flying should be based on 4 things:
1. Whenever your HP reaches 0, that symbolizes that you've been run through, slashed nastily across the chest or stomach, bashed in the head hard - something that actually incapacitated you. You see, your HP isn't how much major injury can you suffer. It's about how much minor injury can you suffer before you take that one hit that fells you. ONLY when your HP reaches 0 have you taken a debilitating blow. That's why you don't suffer minuses to rolls while you have even 1 HP. It's also why you can heal HP in an hour while having a snack and repairing armor and such. As long as you have 1 HP, you haven't suffered from an injury that is bad enough to hinder you in combat in any way. Every injury, no matter how many HP you lose, just symbolizes how much stamina you had to expend to avoid receiving a debilitating gash in the chest, leg, arms, etc. It's your ability to turn major injuries into minor ones. The higher the damage roll, the more it takes of your personal energy to turn that hit into a glancing blow.
2. Relative HP loss to Max HP of the target. If the target has 50 HP, and they lose 25, that's half HP loss. That's worthy of seeing some blood fly. However, losing 5 HP with 50 max is hardly something to sniff at. That symbolizes that you took a hit, got bruised or cut a little, but it was hardly anything that would slow you down. Ding you up like that enough times and you'll get sloppy and eventually get run through, but 5 HP loss for a 50 HP max characters "tis but a scratch. Just a flesh wound." However, 5 HP loss at Level 1 with 8 HP total, that should show some blood. So, maybe if you lose 25% of your total HP in one hit, that's a minor blood splatter event. 50% would be a moderate blood splatter event. 75% would be a sizeable blood splatter event and 100% would be a running through, major bash the crap out of an enemy with lots of blood and close up and so forth.
3. Instant Kill. There is a rule in D&D 5e. If you reduce and enemy to 0 HP AND the remainder of the damage done is equal to or more than their Max HP, that is an instant kill. THIS would constitute a close up head getting lobbed off cutscene, or a heart shot bullseye from an archer, or an arrow between the eyes, or something like that. Goblin has 7 HP max. You do 14 damage to it, reducing it to 0 HP and then doing 7 more HP to it so that the excess damage equals its Max HP, you should run that goblin through or cut its head off or something of that nature. That's what Instant Kill is for. Hmmm. I wonder if Larian would maybe stop bumping HP for a lot of enemies if they did this. I think players would like to see Rangers shooting arrows through goblins' heads because they did 16 damage on a 7 HP goblin. Something to think about. Maybe having more D&D 5e stats would benefit the overall game. Hmmm... I wonder, but I think players might even like more goblins (even via Random Encounters and such) and other low HP enemies to fight if they got more Instant Kills in the game to see the blood and guts fly. Maybe... Imagine the Spider Lair with Swarms of Spiders instead of just 2 Phase Spiders and 2 Ettercaps. They have 22 HP, but that symbolizes a whole swarm of spiders. So, every hit is seeing spider blood and guts and legs go flying. Imagine the baby phase spiders with 6 HP getting hit by Lae'zel, and she does 12 damage and hacks them in half, sending their spider carcasses flying because she got an Insta-Kill. Imagine, when your spellcasters are able to, they cast Fire Ball and roll a Nat 20, dealing 16d6 damage. Imagine gobs of enemies getting caught in the explosion and having a cutscene where they are all blown to bits because they had 15 HP and you did 30 or more. That's what I'm talking about.
4. Give us Swords of Sharpness and other like items. If you roll just right, even if the enemy has full HP, you cut off a limb or head or something of that nature. That would be cool. Certain weapons have a chance that if you Nat 20, you could just insta-kill with a lobbing off of the head or something similar.
As for non-combat skills. A Nat 1 or Nat 20 means nothing. How well or how terrible you succeed or fail means nothing. Why? It's simpler to not have it mean something. It's pass/fail. I really think this is the best solution especially for a game already as in depth and diverse as BG3. For TT, a degree system would be fine, but for a cRPG, this is one of the things that it would not work well.
Imagine what Larian would have to go through to implement this.
Let's just take the Zevlor/ Aradin confrontation. You can either, Intimidate, Persuade, Attack or Do Nothing. You decide to Intimidate. Well, if you succeed by barely succeeding, they have to record a dialogue for that. If you succeed by 5 or more, another dialogue for that. If you succeed by 10 or more, another dialogue for that. If you fail, another dialogue for that. If you fail by 5 or more, another dialogue for that. Do that for 3 of the 4 options above. That's a LOT of variations they have to include in the game for just one moment.
Even tasks like Jump and Pick Locks would not really be worth it, I would think. The simpler, the better in those situations. You either fail the jump and take 1d6 damage, or you succeed. Boom. Done. No need for additional calculations. Pick Locks. You either succeed and get the chest open, or you fail and the pick breaks. Simple. No additional fuss.