In theory, they make for a steady difficulty curve that can be easily monitored and keeps the player accessing content that equates to their level. In practice... not so much.
Most of the time you find yourself over-levelled due to playing side-missions or additional quests/missions, meaning that when you get round to the main progression, it's now beneath you, becoming exceptionally easy and losing the charm and difficulty that should've made it sing.
Games like The Witcher and Horizon: Zero Dawn suffer the most from this as they tie their difficulty into levelled missions. Instead of adopting a natural way of indicating to the player that maybe they should go level up, they flat out tell you, ruining that role-playing element. Instead of you as the character deciding the challenge is too tough, it's you as a player looking at the numbers and saying it's too much - a key distinction.
Other game like Skyrim and Breath of the Wild manage to scale enemies to your level, whilst still having powerful one-offs as deterrents to particularly tough areas. At no point does the game say you fundamentally aren't a high enough level, instead it lets you figure that out by getting your ass handed to you - a far more memorable and worthwhile method of player engagement.