TNG episode “the Offspring” where Data makes a child android. Something goes wrong with her and they can’t repair the problem and so she dies - Data apologizes to her that he could not save her, but she thanks him for her creation and the opportunity to live, and lets him know she loved him and will feel the emotion of her death for both them, before her system collapses. Watched it several times, cried each time,
Earlier in the series, Buffy’s Becoming. I was bawling at the end of that episode.
“The Body” doesn’t count for me, because I sort of expect “Buffy” to run roughshod with my emotions.
But I don’t expect that kind of throat-tightening heartstring-tugging emotional complexity from Star Trek. So my answer to this question are: “The Inner Light” from TNG and “The Visitor” from DS9.
Will TELL the family. Definitely one of Smith’s better scenes.
This.
Hands down, my favorite Star Trek episode. And my canonical example of the artistic significance of science fiction. The episode is about the choices Picard has made - to pursue a career in Starfleet, to remain unmarried, to have no fixed home. In this story, he is shown how his life would be had he made different choices.
Science fiction/fantasy is the only genre that could tell that particular story in that way.
For me, TNG’s “Haven”, a story of lovers of destiny, and DS9’s “The Visitor”, quite possibly the best father/son story I’ve seen on TV.