Alice in Chain's Rooster was the first thing I thought of. It's not obscure, obviously, and I do think they have better songs in their discog but the first time I ever heard the track I remember kinda drifting off through the first two minutes, and when that first "here come the Rooster, YEEEEEEEEEEEEEAHHHH!" kicked in I immediately turned it up. Definitely sets the tone for the rest of the song.
EDIT: This specific version of The Door's "The End" from Apocalypse Now. The climax of the song that comes in at around the 4:50 mark really gets me hype. I have both this and the original 11-minute long version on my playlist, but this one definitely hits the spot better as it doesn't have the exact same conclusion.
The gold standard for false endings, in my opinion, is Rope - the nebulous coda to Low's debut I Could Live in Hope (discounting a hidden track - a frail cover version of Jimmie Davis and Charles Mitchell's You Are My Sunshine).
The song gradually deconstructs itself until around the five-minute mark, where it is hanging by a single repeated note, at which point it abruptly rekindles and plays out over a further minute, bringing the album to an unsettling and ambiguous conclusion. You need that fragment of You Are My Sunshine afterwards just to sweeten the pill.
I nominate Dreamer Deceiver/Deceiver by Judas Priest. It's """"""technically""""""" two different songs but practically it's the same song. They never get played separately. The whole album is a masterpiece but this track best represents what the OP calls for.