I can answer that question from direct personal experience. This was the first TNG episode I watched, and it caught me hook-line-and-sinker. Within a few weeks, I had bought the VHS back catalogue of all previous seasons and fell in deep.
It’s almost a clichéd answer amongst Trek fandom, but it is the fifth-season TNG episode “The Inner Light”.
This isn’t space-battle Sci-Fi. There’s actually not much of it set in space. What there is, is some tour-de-force acting by Patrick Stewart, supported by some of the best episode writing in the entire franchise which leans on more thoughtful Sci-Fi and philosophical concepts than the typical “disaster of the week” trope. The writer, Morgan Gendel, actually won a Hugo Award for it. This period of TNG has some of its strongest episodes, with Darmok the other notable example.
In Inner Light, the Enterprise encounters an alien probe which promptly zaps Picard with a beam, knocking him unconscious. As the crew try to revive him, Picard awakes in the village of Ressik in a seemingly less technologically advanced society called Kataan. His family and friends convince him that his ‘life’ as a Starship captain was a vivid dream, and he is actually Kamin, a humble Kataan scientist. Over time, Picard grows to accept this reality and begins to live life as Kamin. He learns to play the flute and live as a productive member of society. Over the space of 40 years, his wife dies, and his children grow and have children themselves, though Kamin grows to learn that their planet is suffering an irreversible natural disaster, and the Kataan civilisation is in jeopardy.
As an old man, Kamin attends the launch of a rocket containing a probe. At this point, the family members from his life appear to him and inform him that he has seen the probe before. The truth dawns on Picard; he is still on the Enterprise and the past 40 years were a simulation, designed to pass on the knowledge of the Kataan so that their memory lives on after their civilisation disappears. Picard awakes on the Enterprise, minutes having passed, but with memories of an entire life lived in his head.
The probe contains Kamin’s flute, which Picard keeps as a keepsake.