The incredibly popular cheerleader who's beautiful, writes poetry, is very intelligent (she's going to study law at Harvard!) is also a gymnast, was the youngest girl to successfully play an entire piano concerto of Beethoven, lost both her parents in a car crash, is very angst-y (which is why she writes really deep poetry) and has a very low self image, despite always being friendly and carefree.
No...I meant he has flaws AND he's a Mary Sue.
A good writer can manipulate a Mary Sue or a cliche to be workable and likeable. The idea behind Mary Sues and cliches have such a negative light that most try so hard to stay away from them, go down a list of what they shouldn't do, and end up with a mess. Cliches aren't inherently bad; it's the execution.
And since it's been brought up, I'll go ahead and use Kvothe as an example character. I've talked with people who didn't like The Name of the Wind because they thought Kvothe was too perfect, which is understandable. Patrick Rothfuss created Kvothe as a portrayal of the classic legendary hero, so the character walks a fine line between "realistic, likeable character" and "predictable, boring cliche".
However, I think Rothfuss took an overdone character idea and made it different by adding a few elements to the mix. Kvothe may have some of the definitions of a fantasy hero, but instead of wielding the magical sword of instant victory, he works hard to get where he wants to go, makes mistakes that cost him and/or other people, and sometimes fails at his goals. This in turn makes him relatable to the reader and the story a little more unpredictable because you don't know if Kvothe's choices will affect the world around him in a good way or a bad way.
In contrast, the Iron Druid series tries to do the same thing, but doesn't really pull it off. The MC there is too awesome and cool from the get go. Instead of having abilities and contacts that are just a little bit cooler than I am, he's leagues ahead of me in the things he does and the people he hangs around with. Too much contrast.
I'm open to either approach in terms of morality or lack thereof. Didn't Conan once pummel a woman into unconsciousness (well, I think it was one punch) because she wouldn't go into a cave with him?
How do you deal with "awesomeness"? How do you know when you've sacrificed depth for awesomeness? It seems like a frailty of our genre. What do you think?
I think it misses the point, which is that a story is always two stories (at least).
You're going to have the awesome "got to rescue the Princess, steal the treasure while the dragon's asleep" type of awesomeness, the cgi, the explosions, Bruce Willis shooting villains etc.
But underneath that, there's always the human story.
So it's a false choice. You're not choosing between depth and awesomeness, it's both.
You won't have sacrificed depth for awesome, you'll just have left out the depth (human) part.
You've nailed it. If you are writing fantasy, you can (and should) have both.
Legendary knights with their +20 blades of asskickery looked dull and boring alongside this sad little cripple.
.... and you realize that this is essentially the same concept as the Lord of the Rings. A bunch of in-over-their-heads hobbits fighting an evil they should never have had to confront, looking pathetic and helpless beside all the other proper warriors of the Fellowship.
There is a reason why, overwhelmingly SAM is regarded as the favorite character. Not Legolas with his bow of Gimli with his axe, Gandalf with his magic or Aragorn with his crown.
This concept should be applied to every aspect of your story. Yes, there is a place for outright awesomeness... but that is not what makes a story good.