I’ve played in a few RPG sessions, mostly one-shots, involving giant rampaging monsters. They’ve been disappointing because they focus on killing the monster, which reduces this huge lumbering horror to litte more than a 40-story sack of hit points.
At the opposite extreme are monsters who can’t be defeated (and I’ll admit to unleashing one of these in my campaign; a CR 35 horror that destroyed the city of Stoneheim in the World of Greyhawk). Those can be equally disappointing for players because characters (especially high level ones) think they can defeat anything.
Then again, maybe that’s missing the point.