There are many factors at play.

First - SD cards can’t be trusted - they’re an disposable transfer media. Depending on the quality you’re buying, they can be absolut trash. Its basically the (cheap) MMC module with convenient connectors. Good quality modules can last a long time with regular use.

One often overlook problem is with file systems. Cameras (and my other things) use the FAT Filesystem. Which has no resilience what so ever built in and tend to corrupt the Filesystem over time with use (regular formatting should mitigate that - for digital cameras, always format with the camera).

Don’t long term store data on them. Always have Backups of your Data.

As long as you treat them for what they are - an untrusted transfer medium - you won’t have unexpected problems and data loss.