Having worked on firmware writing to SD cards, I can tell you that the big problem comes from allocating space for writes. That’s when the card disappears on you, as it looks for space it will potentially shuffle data around behind the scenes. Current spec I believe says max latency is 200ms. This is the operation that will abuse this part of the spec. If you want to see an SD card flail, just try to write one byte at a time. This will cause headaches no matter how wear leveling is implemented in the SD card. And will likely significantly shorten the lifespan of the card. If you can make sure you only ever write multiples of 4096 (probably card dependent) you’ll be much happier using SD cards. But even then, you might have a 200ms window where you can lose or corrupt data if your power to the card fluctuates significantly.

IIRC SD cards weren't made to handle the read/writes of running an operating system off them. Which is why you see them break so often when used with Raspberry Pis

My understanding - at least for the original Pi - was that there was a design flaw where the sd card was not protected from power irregularities in the same way the rest of the device was. There's a lot of argument over whether it's a hardware design flaw or a problem with sd cards generally (perhaps the usb spec has better buffered writes or something)... It's apparently much more a problem for long-running applications with high write frequency.