It adds fully animated baking, including most sensible ingredients, adoption of children if you want to get them out of that shithole in Riften, a use for all that excess iron you've accumulated since mastering smithing, and apparently if you married Camilla Valerius she now gets kidnapped by Sven.
It does change up leveled lists and NPC dialogue quite a bit, so it could actually have incompatibilities with several mods outside the home-building scope.
As for the quality of the house, it's pretty good, on par with all but the very best of house mods in terms of balance, aesthetic, and choice. It's a little lacking in the choice department compared to, say, "Tundra Defense", but pretty much offers exactly the same amount of choice as "Build your Own House." If you want storage space, you'll get plenty, including a basement full of many, many safes. You could also build a full temple of the divines in your basement, and a fully equipped forge area (finally!), all without compromising. Unfortunately, though there are 9 wing options total, these are divided between N E and W wings, meaning you cannot mix'n'match exactly what you want: Kitchen and armory are mutually exclusive. You can still choose decoration style of the room and the style of many individual pieces of furniture, but you can't change layout or position of furniture.
Is it worth it? I'd say it is, since the framework it adds for houses/adoption could be expanded upon by modders to make something neither Bethesda DLC nor mods based on vanilla could do alone. Instead of building from scratch, modders could fix the shortcomings of Hearthfire and expand upon it. I'm expecting someone will do a Hearthfire overhaul that will make it truly amazing. If you're on the Xbox, this is probably the best you're going to get, homes potentially added by future DLC nonwithstanding.