A case in point.
I use paid Dropbox as a pivot point for my devices: iMac, MBPro, iPhone, and iPad. It runs across platforms, which is great when I need to venture into Windows territory.
At work I was running another iMac as a livestreaming platform, and used my personal Dropbox to transport graphics and feed 1Password for critical passwords. The Dropbox app on that work iMac was set to sync a subset of my folders.
When we went on livestreaming hiatus for a period of several months, the iMac sat idle. My own work life moved on.
In February a new video team need to be resourced, and I fired up the iMac, preparing it for their needs. In the course of discarding old folders and files, I managed to dump the Dropbox folder into the trash and emptied the trash.
Poof.
Folders, files, my 1Password vault, synchronized to that machine, which was telling them I wanted all that data to go. And, all my other devices dutifully synchronized to that new reality.
I called support, who at the time could offer me a convoluted procedure involving my sync history, removing the Dropbox app from all devices, restoring the sync history at the server, then reinstalling Dropbox and signing in. Eventually, all was well, but it was one of those cold sweat moments and I wasn’t “whole” again until some time the next day.
Dropbox has since rolled out a “restore” function for its paid subscribers, which interestingly happened about a month after I had my moment. I’m sure it was a coincidence. 
I still have no confidence in iCloud being there when I need it (and as others have pointed out, it’s not a full backup). I tried the almost-free one year iDrive trial, and ended up not renewing it because it never did give me the complete backup it promised and kept complaining about vague errors that prevented it from doing so.
My desktop backs up to Time Machine, but again I’m never sure if something I want to pull back from the past will actually be there. Amusingly (?), every time I run Windows on VMWare Fusion, it fills up my TM drive partition so I know older stuff is being thrown out.
So getting back to one of the OP’s original pondering, something has got to make backup worthwhile for you. Despite my experience from earlier this year, I agree that having everything online all the time changes the equation a bit, but I also know how shocked I was to see everything disappear in other places because of something I did in one place.
What I really want back is my DVC SCSI tape drive with Retrospect, which was slow but got the job done 20 years ago. That, or its modern equivalent, felt more reliable and complete to me than the rolling archive kept by Time Machine.