I suspect it occurs when a card loses power while in the middle of shifting data around for wear leveling purposes. Presumably, some internal data structure gets borked in a way that the card's embedded MCU firmware didn't anticipate and can't recover from.
Power loss is definitely a threat to an SD card in a Pi. But I've seen many cards go bad while the system is still running, so it's not the whole story.
Weirdly, I've had a number of cards that entered a state where they would be inaccessible under Linux or Mac OS. But they could easily be recovered by a re-format under Windows. No idea why. Perhaps the manufacturers test a lot more thoroughly with Windows?
I like to imagine in my head that the MCU in an SD card can detect its voltage dropping quickly enough to get into a safe state most of the time. With all the recommended capacitance around the socket, it might actually have a few milliseconds to work with after external power loss. When you pull the card out of a socket, though, the MCU stops working before it has time to do anything about it.
I didn't have many issues with my consumer cards of choice, which are Sandisk Ultra A1, which I chose because they are cheap, not too slow and not too fast (interface limit is 23MiB/s and there's no point using faster cards), made by the actual manufacturer of the NAND chips not just some packager buying random NAND chips from a bucket on the spot market, and have verifiable origin via scratch codes.
Not sure about their failure modes, because all of the cards I had fail on me so far were Samsung ones. (some/most of them probably fakes, because I bought them on aliexpress, and Samsung didn't give a crap about consumers being able to verify origin of the card, until very recently and for select cards only)
So far I'm very happy. Cheap and reasonably reliable low power storage.
I guess if I had 10000's of cards in the wild in harsher conditions, I'd care if 1/100 failed on me per year. But in home conditions, 1/100 per year means 1 card failing every 2-3 years in any of my devices, which is very tolerable, and easy to recover from.