My personal favorite is Ash Lake from Dark Souls. It's buried behind two secret walls, an area that's really out of the way, a descent filled with some of the most annoying enemies in the game. What do you get from finding this super secret location? Some of the most atmospheric, beautiful visuals in the game, and get to experience the last vestiges of the Age of Fog.
The Painted World is another really great example, and in terms of gameplay, it blows Ash Lake out of the water, but damn the atmosphere is just so amazing.