For me it was Okami. I remember you do a ton of shit to get to a certain boss which I thought it was the end, only for it to reveal that there was a loooong path ahead.
Other than that, maybe cyberpunk? I was surprised after all that stuff you do in the beginning just to see the titles pop up
Not quite the beginning but realizing that I’m only at the halfway point of Prince of Persia Warrior Within got me so hyped back then :D still my favourite game to date.