They can change how a quest progresses or ends, cost the life of npcs or make them leave, fail to keep party members around, get pets for the camp.
All failures mean something in the game changes. Like for example if you fail a persuasion check you may have to kill 2 tieflings holding a gith hostage, then you have to fight them to free her. If they survive the encounter you can find them again later at certain points in the story.
Some failures may cost you skills, like for instance, theres a book of necromancy that can grant you speak with the dead at will, and offers a quest later on to finish reading it, but if you fail you wont get the skills, or may even get cursed (Cant remember as I never got there in my honor run, where it was pretty much guaranteed I wasnt succeeding the hard checks.
You may get some npcs to help you, or to stand down, and a lot can come back later if they survived. Its absurd the lenght to which larian went to validate player choice and player fuck ups.
It really feels like people that dont like the game simply havent played it. Which, fair, theres shit I dont like in it too. But it is one of the best rpgs ever made, better than even fallout when it comes to the ability to interact with the world as if a DM was there and adapted to your choices.