From my point of view, failure is the defaul. We fail far more often than we succeed. So I think that failure is humanizing in a fundamental way—to the point where a character that never fails feels alien and therefore fake. Plus, failure is way more interesting because each failure is a crucible that shapes our character. It's the rare successes in a sea of failure that that feels glorious. But that glory will feel like a given if there isn't true failure.

In storytelling, failure is absolutely common. It's just that those failures so often lead to another challenge (maybe a string of challenges) that eventually lead to success that it's easy to label the entire string a success. I think that's a mistake. The eventual (maybe inevitable) success is only worthwhile after a string of failures. That we the audience know the progtagonist will probably make it out of the overall situation successfully is beside the point. It's the struggle that really matters because the struggle is what comprises all the human details that tell us about who the character really is and isn't.

Finally, thre "whiff" is something handled poorly in D&D because it's out of tune with the rest of the experience, and because it's so easy to simply roll the dice and try again (another source of dissonance). In storytelling more generally, however, the whiff is something rare but useful. It breaks the flow of the story like an exclamation mark. It can be formative or humorous, but the whiff should give the reader pause, and should point to something interesting in the story or the character. At least that's my opinion. I'm sure there are other reasons to include a whiff, but I hold there should at least be a break in the story to point out that the whiff is significant.