In my former life I ran the data department for a large post-production company that had near enough to a petabyte in various types of network storage, and LTO was the standard for on-site data backup for all of it. This was the case for all the facilities across London that we were either working with, or in competition with.
Yes, tape-based storage certainly can be prone to failure (and the LTO machines needed regular service as a result) but the tapes themselves are remarkably robust, both physically and in terms of error-correction and detection. In my 10 years of running nightly LTO backups (through several generations of LTO technology) for all that storage, though sometimes tapes would get stuck in the autoloader of the tape robot, I never had a tape break or jam, and I never had a backup that I wasn't able to restore data from where I wasn't warned of errors during the backup process.
The thing with data storage to remember is (like life) nothing is forever, hence backup strategies like 3-2-1 (three backups across two types of media with one off-site) are useful ways to mitigate the risk. If I needed to archive data for 10 years I'd feel much more safe entrusting it to LTO than a hard drive, (saw dozens, if not hundreds of those drives literally blow a puff of smoke out the back when they reached their EOL), SSD, optical media, or even cloud storage (who knows about the security of your data, the longevity of the provider, and of course bandwidth for large backups becomes an issue).