Hey guys!

A bit of a weird request, possibly, but I just watched MandaloreGaming's dive into the Marathon trilogy plus Pathways Into Darkness and I was wondering what other games might have stories or underlying narratives that offer similar "whoa" factors when looked into. I recently also finished NieR Replicant that gave me similar vibes, if that helps at all.

Thank you in advance for your help!

I think what's "whoa" is very subjective, but I think the Metal Gear Solid franchise has a meta story that most would consider to fit that category.

I would say the revelations after completing Tails to Azure about what really happened during the intro to the prior game Trails from Zero is definitely a wow moment.

I don't know the Marathon trilogy
but i know a few games with some 5 star quality game story's.

https://www.gog.com/game/planescape_torment_enhanced_edition
absolute one of the best stories you will find on PC gaming till this day.

https://www.gog.com/game/the_longest_journey
also a abolsute must play (if you are into stories)

I don't know the titles in the OP (just news from Nier Replicant, at most), so hopefully my assumption of what you mean isn't too far off what you really mean.
Now, while trying to avoid as many spoilers as possible, those are the games that I remember that, for me, offer either the most head-scratching lores (in a good way), or that are at least pretty interesting to interpret beyond what's obvious:

Team Ico's games - ICO, Shadow of the Colossus, and possibly The Last Guardian too. If you start reading the theories and interpretations on those games, you won't get out of that rabbit hole for a long while.

Final Fantasy XII. More in regards to character development, and not just from the protagonists', I must add.

Dandara. But do mind the underlying story is hidden behind extremely hard challenges in an upside-down-like world (Stranger Things reference), and not even the in-built cheats seem to help much in some challenges.

Dreaming Sarah. Although it has a planned sequel and a small interlude, taking the first game as a complete journey, details throughout the game seem to imply what is really going on.

Celeste. It feels like a parable to the fight against mental issues, like anxiety and depression, albeit in a far more fantastic way. And maybe I'm overthinking it, but even the game's extremely hard but fair difficulty seems to add weight to the plot.

Momodora franchise & Minoria (both by rdein and his team, Bombservice). Kinda hard to say anything without spoiling things too much, so I'll keep it at "pay attention to the details".

ABZÛ. Uses plenty of visual storytelling, and no spoken or written storytelling.

Dissidia 012 Final Fantasy. While it is primarily a game focused on crossover fan service, as well as being an expanded version of Dissidia Final Fantasy, it has some rather interesting plot points I'd not expect from a crossover, specially when doing the optional content.

LiEat. No comments, just play it. It's a short game, too. Wish there was still a DRM free version being sold, though...

Parasite Eve. I haven't played the second game past the first boss so I can't comment on it. But Parasite Eve is a Biohazard-esque survival horror (but more RPG-oriented) and a direct sequel to a Japanese horror book with the same name (but don't worry, everything you need to know is in the game). Story's pretty solid, is all I can say.

GrimGrimoire. Another game I can't say much without giving it all away. Just play it.

Odin Sphere. The beginning (the first point of view) can be rather hard to digest, but it gets pretty good after you get the chance to play as the second playable character.

Persona franchise. Even if some of the stuff is clearly retconned, the lore throughout the games actually works pretty well. Just avoid Persona 3 Portable and the censored versions of the Persona games from before 2006, if possible.

Planescape is a game that has always been in the background and I've heard about but have never actually played, this might be the recommendation that gets me to take the plunge.

just fur the fairness's sake, it's actually free. both as in freeware and DRM-free. Steam offers an easier way to give your money if you want to support it, though.

subj-wise: Outer Wilds is a great one. and its addon is just as great, even though different.
and I'd second Planescape: Torment and The Longest Journey.

When i read the thread title i instantly thought of Marathon. ;)
Cant really think of anything that comes close, but Planescape:Torment is a good suggestion, Bioshock Infinite as well, even if its all way more directly presented and explained. Still, loved the story...

No idea about your games mentioned in the OP or exactly what you want for "whoa" but:

Horizon Zero Dawn's story (a true 10/10 story) is filled with many "whoa!" reveals that make perfect sense with what's been going on in the game too.

If you instead mean games where giant things come out of nowhere, there are uncountable ones (even if you exclude surprise final bosses you never learn of until very late in the game).

Thanks so much for all the suggestions!

There were a large number of titles I didn't know about that I'll need to look deeper into.

I have been playing Outer Wilds, but having a bit of a hard time getting into it, just have to push through it.

A couple, mostly japanese PS2, games come to mind, I'll have to look them up this weekend as I don't recall the titles.

Both Little Nightmares have remarkable plot twists, and in general the narrative is really engaging.

Myst as a standalone game isn't too mindbending. But if you peer under the covers into the lore...

I have to admit, I never played any of the games you mentioned, therefore I don't know what exactly is "mind-bending" about them...but if you're also interested in "mind-bending" console games, maybe Eternal Darkness: Sanity’s Requiem on the GameCube could be interesting for you.

It features a so called "sanity meter" and, in relation to that,"sanity effects" (aka: mind-bending stuff), and is usually considered to be "one of the best horror games ever".