Major quest: Saving (or not) Redcliffe in DA:O. On my first playthrough, I had my PC turn around and leave town as soon as the guy ran up to warn him about the undead invasion. I figured I'd go check out the mage tower first and come back later - no big deal, since the invasion would just wait around for me to get back, right? Nope. Bye bye Redcliffe. I was shocked, appalled and very, very impressed. I thought the rest of that chain was excellent too, right up to choosing Connor's fate and Alistair's reaction afterwards.
Side quest: the Cult of the Eyeless in BG2. First you're chasing down some weird cultists, then there's this ancient temple, then a town of undead, then a beholder lair, and throughout the whole thing you're interacting with interesting people and learning about the lore of this world. Plus there's some good loot. I love it.
Minor quest: Playing through Redcliffe again, this time in DA:I, you can get 'Flowers for Senna.' It's very simple - talk to a widower, go put some flowers on his wife's shrine, come back - but I thought it was all beautifully written, including a little poem on Senna's grave. If you're playing a Dalish Inquisitor you even get some special dialogue. I'm a sucker for quests like this one.