Bit of a tangent, but on the topic of "making failure fun": even if Larian succeed in making every path that can result from a roll a fun adventure, the fact remains - you failed. This is something I think they've overlooked. If you see "check failed" on screen, you don't feel good about it, no matter how interesting the outcome and how well-developed the path. I'm betting that most of the time, players won't even connect "I'm having fun with the story right now" with "I failed that check". Meanwhile, it feels good when you see "check successful". "I won!" That's a reward in itself, inconsequential as it may be.
As for die rolls in general: I don't really want to say it, as I like this whole "roll the dice like in tabletop" thing, but there are problems with this. Success/failure, story branching and RNG are all mashed together, which... I'm not sure is a great idea. I don't think much room is left for player agency during dialogues, if the most important branches are hidden behind RNG rather than behind actual decisions or roleplaying the character. The "success" is not a result of the player's cleverness or being prepared in some way; it's random. With some modifiers, but still.
And, as per what you say, even the execution of the "fun failure" design leaves a lot to be desired.