What's your idea of the ideal last level of a video game? Challenging? Simple? Long and building gradually, or short and climactic? I think the final levels of any game should be challenging and require you to use all the skills you learned throughout the game. That's why I loved the final levels of Zelda games pre-TP, especially in OoT and TWW where you had to go through the mini-dungeons of each dungeon you'd been through before. TP was really disappointing in that regard, as the final dungeon was just so generic. On the other hand, it doesn't need to drag on for too long, or it kills the momentum and ruins the buildup to the final confrontation.
The final level of a game definitely should be challenging and utilize all the skills the player has learned throughout. But what truly makes a final level memorable is its uniqueness. For example, the Moon in Majora's Mask. The player had to use the three main masks to solve difficult puzzles. Plus the Moon itself was mesmerizing. It was a very surreal landscape that epitomized the look and feel of Majora's Mask: creepy yet beautiful. Another great example is Grunty's Furnace Fun from Banjo-Kazooie. This was great because it again required the player to use everything they learned while playing, from guessing correctly the in-game music and character's voices to solving once more the puzzles and mini-games found within the game. Very fun and memorable.
To me, the final level of a game should be challenging and memorable. All of the skills you took the time to learn before should be put to the test, and the level should be carried out in a fashion that makes it epic, emotional, etc.
