Why does the story end on such a sad note?

There's an appalling sense of inevitability about Paul's tragic demise. He's spent most of his short life on this earth chasing a dream, pursuing a fantasy that's eventually come to destroy him. His collision with a locomotive at the end of the story in an act of suicide is a metaphor for the last, brief intrusion of reality into a life mired for so long in a dream world.
Paul has come to New York with over $1,000 of stolen money in his pocket, determined to live out his fantasy as a wealthy young man about town. But when the money runs out, his dream dies with it, and so he has nothing left to live for. His death, though tragic, does at least have a certain rationale to it from both a moral and a literary perspective. In writing her story, Cather is warning us of the dangers of fantasizing, instead of living, and making our way, in the real world.