The most common failure for a DVD (other than scratches) is physical degradation of the storage medium, which makes the data corrupted and completely unretrievable, if it can be read at all.
A spinning disk is most likely to fail because the moving parts get locked up. In a pinch, data retrieval specialists can move the platters to a new drive and be able to read them again.
Magnetic hard dives do sometimes experience spontaneous data corruption, but it’s much less common, and typically corrupts only a file or two (a flipped bit here and there) rather than making the entire drive unmountable. The exception is if you expose your drives to stormy magnets, which... we’ll, you don’t do that, do you?
I’m less familiar with SSD failure modes - perhaps someone else can speak to that.
Can you link to any company offering this service please?
I would set it up so that it reports whenever it finds errors. Every time a drive starts to fail frequently (because everything needs to fail at some point) I would buy a new hard-drive to replace the failing one and clone the drive that has the good copy.
It might be good to get drives from different (good!) vendors to minimize the chance that they'll both fail at around the same time, because if that happens you'd have to figure out from which drive to copy which blocks. Maybe it's a good idea to make a RAID 1 with more than 2 drives? Idk, that might be going overboard. Still tons better than DVDs, lol.
EDIT: If you want to go further, it might also be good to sync your backups with an online service like rsync.net or something. It's more expensive than local backups because you have to pay rent for the storage, but that way you won't lose anything if your house catches fire or was robbed or whatever. You might think that if you did that, then there's no point in having your backups locally, but I would hope people stop trusting businesses so much (especially the free ones) with something as important as having the only backups of family photos and videos, etc. Backups is the kind of thing that never matters until it's critical.
The unreliable part of a backup system is the human. Maybe DVD media is more reliable. Maybe it isn't. Probably it doesn't matter. If all your data fits on one DVD, then it is practical. If all your data doesn't fit on one DVD, then it's not...at least for most people[1] -- I think.
For me, I use hard disks. Not really as a explicit strategy [2], but because I have them laying around. I have them laying around from upgrading to SSD's and replacing laptops and installing new OS's...new disks are a time efficient way to upgrade an OS on an existing system. Compared to DVD's storing 100GB of data on an old 250GB hard disk is easy...or two old 250GB disks. Most of the time, I don't need old personal data. If I need it, it's ok to have a few (or many) copies...it's probably better. Short of storing video, HDD far outstrip most people's personal data needs. As I mentioned, that's still a common case for DVD's. It's just that DVD's require explicit backup and HDD's don't.
[1]: Anyone who is actually willing in practice to swap DVD's will almost certainly have already committed to a backup strategy.
[2]: Well ok, I have motherboard RAID mirroring on the tower ...I'd kind of forgotten about it because the tower is 10 years old and shipped with it.
For long term archives of selected data I play with Perkeep on local NAS. Also I burn M-Discs and BD-R HTLs and in general every decade copy all that to newer and bigger WORM storage.
For backups of NAS against disaster recovery I use offsite SFTP storage (Hetzner StorageBox). BorgBackup and a bunch of scripts take care of encryption, dedup and retention.
The point of archive is that it needs to be easy for my grand...grand children to find and read my box of family photo disks in the under stairs closet. Then in case of NAS failure I have these fresh cloud backups to recover from.
Also using Amazon S3 or digital ocean spaces is relatively cheap and super easy to do with tools like s3cmd