I had a pretty big incident mid last year, which will turn into a dramatic blog post eventually.
I didn't lose anything I've yet noticed. Very happy with how well the backup strategy held up, although potentially quite lucky the second on-site backup was unaffected. Didn't have to dip into the complexities of the off-site chaos (which reminds me...).
One of the nice things about having an off-site backup is that, in addition to being offsite, it's presumably a completely different backup process so if the local backup wasn't working for some reason (and you didn't notice) you're probably still covered.
Having a spare hard drive that you occasionally sync up and drop off at a family member's house in addition to other backup strategies is indeed a decent idea.
However, I'm guessing that--if you're anything like me--you get lazy about refreshing it and, if something happens, you realize it's been a year since you did a fresh backup.
A cloud provider backup (as opposed to sync) is a good belt-and-suspenders cheap insurance backstop to local backups that you hopefully never need.
I have local Time Machine and Synology NAS but I also pay Backblaze a nominal amount. Companies were paying Iron Mountain large sums of money before there was a cloud.
You can also say: cloud servers are on fire and the house is on fire, have another backup someplace else. It's all probability.
The probability that your house will be on fire these days is low, and the probability that your computer/hard drive will get on fire is also low (since you will probably take it out, no one will leave his phone in a burning house right?).
We can say that the probability above is that low that it doesn't really affect anything. There are many other different parameters to add, and I use a bit of math here which doesn't always reflect on life, but you get the point.