I've got a very grand house on Hound's Head, it was a rags to riches. The view is great, it's a good world. If you have seasons, the blizzards can really make winter feel quite long. The house better be exciting because sims won't be adventuring outside for long stretches of time. So it's a bit like real life. Does the climate matter? Does the presence or lack of amenities matter? The 50x40 lot in Newcrest is plenty big enough for a nice house, the kids monkey bars and jungle gym can be a plus.
I love the fame system in Get Famous, I use it all the time, but I do not like the houses or the vibe or the sunlight in expensive neighborhood. The sun is only ever on one side of the house, it's like the planet is tidally locked, or Del Sol Valley is in the arctic circle. I can't live with that. The front-facing windows will never see the sun, and it bothers me immensely. Okay, the back of any house there has the spectacular view.
The original big lots, Oakenstead and Affluista Mansion have pros and cons. I'd change the Landgraab house as I play. I'm not a fan. It's a rather inefficient layout, and a big footprint for only 2 bedrooms. I'd renovate or bulldoze it, but that lot has gorgeous lighting. No fishing, no bonus items. Then again, it's 50x50 and if a kid needs a jungle gym for the sake of an aspiration, there's probably somewhere to put it. Oakenstead is greener, has fishing, has food stall, has brick driveway that says "this is posh". I recently placed a grand 50x50 Landgraab house from My Save Game 2, circa 2014, on Wakabamori 2-5-1 from Snowy Escape, also 50x50. It's a beautiful lot for a big house. The river, the mountains, the trees, the elevation changes. (I didn't kick the Ito family out, it was a blank save.) It might depend on the style of the house, but right now that would be my first choice as a lot for a big house. I like Hound's Head. I like Wakabamori 2-5-1 more.
But my favourite houses, regardless of lot, are those I built over time. I have a rebellious, problematic mansion that I can't get quite right, no matter how long I play it, though it's really nice in parts. I have a few others that are great. One is at Hound's Head. One is at Affluista Mansion (which has a twin in Sulani).
I also like Nookstone. 20x15. I like adding a 2nd floor, adding a basement, adding rooftop entertaining. I've got more saves of sims living at Nookstone than any other lot, by miles. So that must be my favourite. Or it's a psychological anchoring bias. It's not the best lot, but it's the first one I played in Sims 4.