It depends on the game.
If narrative's an important aspect then character progression becomes a big issue. It doesn't have to be your own character either. Following the stories of different NPCs can be a big reward if done well (first game I'm thinking of is Majora's Mask).
Skill progression is always good but, again, it has to be done well. If you level up just to fight a bunch of enemies who are more powerful, nothing really changes. Done badly, this can make the game worse. My example is another Zelda game- Tears of the Kingdom- which I loved but, the way I levelled up, meant each simple fight seemed to be taking forever (my defence increased, so did the monsters', but the weapons I was using, like bombs and fire didn't).
I think it's important to keep the player's interest, and keep offering them something new, and character progression is just one of many ways to do this- the levels can change, new mechanics can be added, new graphics, sound tracks, characters, twists the player didn't expect, subverting existing mechanics, etc. etc.
