Overall, thematically, I feel that a mage Hawke works best. Act one fits together better if Hawke is scrambling to obtain wealth and prestige to secure his own future, I find. Not that assisting Bethany isn't a good thing, but there's a degree less of urgency to the story (and by extension the player) when you are struggling to help a third party rather than the protagonist.
Act two I think actually works a little better as a rogue or warrior, if only by virtue of the dual at the end. It's a minor point.
Act three I've found works best logically in terms of the overarching Dragon Age story if you side with the mages, and it gains a degree of additional power if you are a mage yourself, fighting for your fellows even though many don't actually deserve the effort. And the idea of a mage Hawke becoming a rallying cry for other mages makes more sense to me, and just feels better, rather than a rogue/warrior kicking it off. Like, instead of needing a virtuous "normal" person, a mage is the face of the revolution.
This is all a manner of degrees, of course, and the fact that mage Hawke doesn't get called out more seems to really bother some people, so your mileage may vary!