As far as the base game goes, finishing the civil war questline gives *some* depth. Looking around at the ruins of Whiterun while Stormcloaks patrol the streets shows me that my choice in joining them mattered. But yes, aside from that the base game is woefully lacking in showing the consequences of your actions. For mods, I've found Helgen Reborn to be alright. You get to turn the charred remains into a functioning settlement and, more importantly for me, you get to decide if Helgen joins the empire, Ulfric, or stays independent.