For the most part, I just like to think that the story is building toward something. If the journey is as important as the destination, it's still important that there actually be a destination - otherwise you're just driving down a random road.
Edgar Allen Poe famously wrote his stories in reverse order - where many authors will excruciate over the opening line of a story, Poe would work out the last line of the story and work backward from there.
I don't think game designers necessarily need to be going to that extreme, but I think it's important (in any sort of narrative - books, movies, videogames, etc) that you at least have some idea of where the story is propelling you to. You can't really build in foreshadowing and other narrative techniques if even the author has no idea where the story is going when they're writing it (see: Fahrenheit...)
My all-time favorite games have always had very good endings. In RPGs especially, that's often what I look for most. I don't often replay games, so whatever I walk away from at the end of a game is the impression I'm going to be left with.
Fallout 1's ending blew my mind at the time. I had no idea it was keeping track of any of those things I'd done in the game. That a lot of the consequences to my actions were surprising twists made it all the more memorable. KOTOR not only had some especially memorable twists, but came to a conclusion that left me satisfied and excited for a sequel.
All of the Deus Ex games, conversely, have felt like a let-down. At the end of the game, none of the things I'd done previously end up mattering - it all comes down to choosing which button to press and ending up with widely different endings. And when I can just save right before that decision and see all of them in a row, it makes the whole game feel a lot less personal to me.
