The chances of there ever being a final Zelda game are fairly slim as long as people keep buying Zelda games. Even the recent, most highly criticized Zelda games still sell phenomenally. I don't think "the final Zelda game" is a reasonable worry.
Still, what I would want in a final Zelda game is a strange question, and I'm not entirely sure how to answer it. If it was my dream Zelda game, then it would probably be an amazing game and sell well, and in such a case it would likely revive the series. That dream game would involve a good difficulty setting, possibly in the form of a second quest, and would be something of a combination of MM and ALttP, with the great style and setting, story and character development of MM, with the overworld style, length and wealth of content of ALttP.
That said, applying the best final Zelda game to my dream game isn't necessarily automatically appropriate. I think the best kind of game for the series to end off on is one of closure, and also of nostalgia. A farewell to the series, done proper, would end off on all the notes that are dear to people and really hit home for them as Zelda fans, and while that differs for some people I think that means adventure, whimsy and beauty. It should also provide closure on all elements of the series left in the air that shouldn't be left as such, such as timeline issues perhaps, as well as any other major plot details, such as perhaps the Dark Tribe.
That said, Skyward Sword is already looking to be all of those things, and I do not feel it's going to be anywhere close to the final Zelda game, so I don't know what kind of point I'm making. I just think talking about the "best final Zelda game" in general is a weird conversation to have. That's... difficult if not impossible to answer.
