I don't know the nature of your maintenance, but I've had unattended security updates working for years, I automated a bunch of stuff and use etc-keeper.
> I only run things that don't need to be highly available
Redundancy helps. 2 (more!) RPis cam be primary/secondary/tertiary DNS servers to match paranoia levels. Even if you have a single PiHole, keeping a pristine copy of the PiHole on a $3 sd card will get one up and running instantly.
> Suggesting that a particular home-hosted solution is "unbeatable" is meaningless
What site am I on, Subscription-Services-News? (:
$20 a year spent on a hand-rolled RPi that you have full control over and enjoy tinkering with—amazing value!
$20 a year for something like NextDNS so you can spend your time worrying about more important (to YOU) things, amazing value!
It's wondrous the choices we have today. 30 years ago it would have taken a rack full of noisy servers and a few thick books to keep a DNS service up and running at anything even close to 99%.
I run a bunch of local services on RPis and a decade-old Mac Mini. I love having the control over things, but I don't pretend I don't spend a decent amount of time maintaining it. I only run things that don't need to be highly available, so something like Pi-Hole is off the table. The last thing I want is for our DNS to go out while I'm sleeping, and my partner has to wake me up because she has work to do.
You mention SD card corruption as the only reason why a RPi-based service might fail, but there are plenty of others: botched updates, random hardware failures, power supply issues, and likely other things I'm not thinking of.
And even if a Pi-Hole can keep three nines of uptime (I'm skeptical of this claim), many people will find significant value in giving someone else money so they don't even have to think about digging into fix a problem for the rare occasion it happens. Suggesting that a particular home-hosted solution is "unbeatable" is meaningless; "unbeatable" in this case is a subjective measure, and other people will value different things than you do.