One that really impacted on me was Ultimecia's Castle in FFVIII, actually. Mostly for its
music, admittedly...you get a nice, lighthearted little accompaniment when you first see it, move towards it, and suddenly this ridiculously ominous organ kicks in. It then goes all over the place, which gives this certain level of suspense; every time the organ comes back it feels like something ridiculously powerful is going to jump out at you...which is definitely possible, considering the numerous bosses you can find in it. It's like Final Fantasy meeting Castlevania or something. It was one of the first RPG final dungeons I got to as well, and it stuck with me.
Mars Lighthouse in Golden Sun: The Lost Age, too. Whilst Venus Lighthouse was the more memorable dungeon -
that music! - Mars really drove it home that the journey was coming to an end; it had taken years of waiting and a lot of patience, but finally being at the last of the four lighthouses was an incredible feeling.
Also, the Shadowlord's Castle in NieR. One of
the more haunting background pieces, plus the shit that goes down in there...well. Sometimes the level design doesn't need to be all that memorable for the place itself to be memorable, and that place shocked and appalled me in equal measure.
The most recent final dungeon to leave its mark on me was Lorule Castle in A Link Between Worlds, though. It's a lot more puzzle-oriented than most final dungeons in Zelda titles, which are typically a lot shorter and easier when compared to previous dungeons (excluding, perhaps, Dark Hyrule Castle in Minish Cap, which was ridiculously long) and the best part?
The music evolves with each section you complete. It starts off quiet and simple and eventually turns into a full-blown orchestra and choir the closer you get to the final boss!
...yes, I know; my choices are motivated mostly by the background music. But a final dungeon doesn't feel like a final dungeon if it doesn't have a suitably epic/haunting/climactic background to it! Without that, it's just another location you need to battle through before fighting a boss. Resonance of Fate's Basilica, as another example, is just a series of rooms with enemies in it. But
this and then
this play in the background the whole time you're there. It goes from creepy to intense at the drop of a hat and it's fantastic.