Apollo describes the fate of the other gods thusly:
CAROLYN: What happened to the others? Artemis, Hera?
APOLLO: They returned to the cosmos on the wings of the wind.
CAROLYN: You mean they died?
APOLLO: No, not as you understand it. We're immortal, we gods. But the Earth changed. Your fathers changed. They turned away until we were only memories. A god cannot survive as a memory. We need love, admiration, worship, as you need food.
APOLLO: Even for a god, there's a point of no return. Hera was first. She stood in front of the temple and spread herself upon the wind, thinner and thinner, until only the wind remained.
And at the episode's conclusion, Apollo himself seems to meet the same fate:
APOLLO: Zeus, Hermes, Hera, Aphrodite. You were right. Athena, you were right. The time has passed. There is no room for gods. Forgive me, my old friends. Take me. Take me.
From this we can gather that:
- His species does not die as humans die. I interpret this to mean that he is biologically immortal, and that his fate is something more complicated than biological death. This makes sense because he fades away and doesn't leave a corporeal body behind.
- Nonetheless, he does speak of survival ("a god cannot survive as a memory"), hinting that some aspect of himself will cease to be.
- He is not entering an afterlife (that he knows of), as he is to become nothing ("only the wind remained").
- Contrarily, the other gods are described as having "returned" to the cosmos. This could just be poetry ("dust to dust"), or it could indicate a regression into some sort of non-biological phase from which they began life. Perhaps he was being literal about what he saw in describing Hera's demise.
On the whole, the episode is very vague about the science-fiction elements Apollo's species (the "gods"), as it really isn't important to the plot. This is because the real answer to this question (from an out-of-universe perspective) is:
Apollo isn't dying. He transforming from a god into a memory.
The episode practically tells as much when Apollo links the survival of the of the gods (a biologically immortal species) to their worship by humans. They cease surviving when they become memories. The fate of Apollo (the alien) isn't ever really specified because it's only important insofar as it's a metaphor for the fate of Apollo (the god)--which is that the myth has lost its power and faded away due to humanity's cultural evolution.
So, does Apollo die? Well, he tells us that his species doesn't die, so the short answer is "no." But he's passed from a god to a memory of one, which seems to be tantamount to death for his species. Of course, it can be argued that legends never truly die...