For me, that moment was in Kingdom Hearts 2. I hadn't played the first game (or the second game) and didn't really understand the concept of sequels that continued a story. My parents had gotten me the game probably because it had Disney characters in it. But this moment stuck with me nonetheless.

It was the game's first boss fight, the Twilight Thorn. Everything leading up to it and the fight itself was just utter cinematography to my young eyes. I wasn't even able to actually beat the fight (and I was the older brother, so I didn't have anyone to help). But it stuck with me for years. I ended up getting a PS4, the first console I bought with my own money, for the sole reason of playing the Kingdom Hearts collections.

There was nothing quite as intense as a ServerSmash in Planetside 2. Which means ~800 people doing joint ops on a single map and everything is highly coordinated.
I think blob fights in EVE are even larger, but this was a first person shooter and also rather arcadey, not a thousand spreadsheets fighting at a server tick rate of 1 ^^

The first season of telltale the walking dead. The ending with Lee and Clementine had my newly Dad self crying.

The Stanley Parable was a great exploration of the nature of free will. It was a game that made me think about the nature of the relationship between me and the creator of the game.

I was playing "The friends of Ringo Ishikawa" and realized that, instead of simulating Ringo getting his life together, I should be getting my own life together. Not a bad game, to be clear.

I feel like I had an epiphany at the end of Omori, but I am too lazy to write it out. Play Omori.

The Missing: J.J. Macfield and the Island of Memories.

I can't say why it was a profound experience for me because that would spoil the whole game, but after I finished it I just sat down and stared at the ceiling for an hour or so.

Stray. Honestly the entire game.

I am a cat dad to 3 cats and I rescue anywhere from 1 - 5 alley cats every year. I take them in, clean them up, get them spay/neuter and their vaccines and find them homes.

That game captures cats so incredibly well. The entire game was a pleasure, but there are a few moments that stick out to me.

Spoilers

At the beginning when he falls and is separated from his friends.

The way that the guardians react to him.

The desperation of being so incredibly close to freeing them and so nearly being thwarted.

But most of all, when his friend dies and when the ceiling opens.

And last but not least, at the very end he sniffs the air and smells his friends.

So some god amongst men on YouTube did the painstaking work of figuring out where stray fell, and where he exited and found that stray exited only a 20 second walk from where his home was, and towards his home is the direction he took at the end (but the game doesn't tell you this).

That game was the most wonderful and amazing experience I have had in a game since I can remember. I cannot recommend it enough.

I know it's not the answer you're looking for, but I've played an awful lot of games, and none of them have ever done this for me. I can't imagine I'm in a tiny minority in that regard.