Most of my favourite game moments are story based as a result can be spoiler rific but one that springs to mind is from flOwer I can't pick a specific moment because the whole game is my favourite moment from start to finish.
A special mention goes to stage 5. (minor spoilers)
After the first 3 levels where after you complete the level, everything is happy and smiley, once you complete stage 4...everything goes to hell. stage 5 is so bleak, so depressing it's unbearable. Some really debilitating stuff, even though there's no dialog to tell you the narrative, you do get a sense of the story and this part is the equivilant of a key character dying just before the final act.
Stage 5, if brilliant to say the least.