First, most of the huge mods run just like Skyrim. So don't look at file size as your criteria for "huge". Some mods do bog you down. For example, I got a mod for Lakeview manor that added all kinds of things. And I could only jerk myself around in it (haha, you know what I mean) because it was so heavy. It seems to be more related to the mod author's ability to integrate their mod into Skyrim than it does actual content. Trial and Error is your friend.
Here is my list of must have mods and why:
1. Immersive Armor and Immersive weapons: Because when you think about it, Skyrim's armor and weapons are woefully simplistic. Immersive armor and weapons adds REALLY COOL "true to lore" armor and weapons to your game play. They change up smithing accordingly so you can make them, not just find them. Also, you can smelt down any mod-origin weapon/armor back into ingots. So you don't have to keep going back to gloombound mine every thirty days for ebony. You'll be up to your ears in ingots of all kinds.
1a. related, but a different little mod. Black Dragonscale armor (standalone) for light armor wearers, because it's cooler than the regular color.
2. Crossbows basic collection: Because it's kind of silly to think that crossbows can only be made of steel and dwarven metal. This collection adds all the others--glass, orc, ebony, daedric, and dragon. They are so awesomely designed that you want one of each. For real. Not just skyrim. They each have their own bolts, too. Of course, you can use any bolt with any crossbow.
3. Camping Kit of the Northern Ranger: You can make it day 1 at the smith in Riverwood. So you'll always have a place to sleep. Roll out your bed roll, start a campfire, use a tent. Pack it all up. You get rested rather than well rested for a bonus, but well, ok. Great for those who think waiting is not really good role-playing, and its painless and easy to use. I LOVE this mod!
4. Immersive Jewelry: This may be a girl thing but if I were a guy, I'd still do it. Immersive Jewelry puts about 3k pieces of craftable jewelry into the system, and looting will get you even more unique pieces. The first time you find a $24,000 pearl and topaz Nethichutti in some bandit cave you will absolutely cream yourself. This 100% changes up smithing. All weights are changed, some ingots and ores have different names, and money weighs something, so you gotta put your cash in a strong box. At first I didn't like that part, but you get used to it. Also, you can go legendary in smithing and work your way back up to 100 in about an hour.
5. Everything by mod author Murderwrath: Gates of Solitude, Morthal Pain, Hjakhtraevarr Tomb, The secret of Dragonhead, the temple of black rock. I like these mods because I enjoy being reduced to tears. I do Hjakhtraevarr tomb for one reason: Fire of Vengeance. You ever think about how underpowered "fireball" and "incinerate" are? 90 points per hit, my ass. You can incinerate a falmer for 20 minutes and he's still half alive. Fire of Vengeance fixes all that. But take care, it will kill you, your follower, your dog, your horse, and half the town, if used improperly. Fire it with your follower 20 steps behind you, then look back, he's on fire, and his life is passing before your eyes. You run to him, hit grand healing like, 20 times, and he's STILL on fire. Cool beans.
6. Immersive Creatures: This probably should have been listed right after weapons and armor. Puts way more creatures into Skyrim, modifies a lot of them, sometimes in funny ways. Offers up loads of new conjuration spells. My favorite? "Conjure Daedroth." I love that guy! Also, "Staff of Lesser Daedra" is a must have. Don't know what I'd do without it.
7. Falskaar and Wyrmstooth (2 different mods) which are as big or bigger than the official DLCs and just as good.
8. Gray Cowl of Nocturnal and Helgen Reborn: Gray Cowl is a NOTABLE effort that gives you a trip to Hammerfell. Helgen Reborn has one of the best ever dungeon crawls; the rest of it is meh to good.
9. Cinematic fire and Pure Waters: Two self-explanatory mods that make Skyrim more beautiful, especially the latter.
10. Lots of player homes, notably "Whiterun Mansion" which as a layout I don't particularly like, but has Ydina, a merchant who has $10k in gold so you can unload a whole lot of loot, and the teleporter spell, so you can teleport out of Murderwrath's hell holes if you find yourself permanently reduced to a sobbing blob on the floor. (also teleports your follower and dog). Look up Blackthorn, this guy is a true architect and makes really nice, functional homes. I have all his mods. But one time I actually filled my armory with all my immersive weapons and armor and pretty much froze myself every time I walked in the house. So use chests as usual.
11. Random and assorted: Rabbits of Skyrim makes the bunnies cuter. Nicer Vlindrel Hall is supposed to change the color of the walls there, but it actually changes the colors of all dwarven walls everywhere. It is much nicer. My home is your home allows you to keep followers in your home when they're dismissed. The eyes of beauty and true brows, if you play female and have to be hot, like I do.