It would be super nice to have a setup that uses multiple CDNs w/ automatic failover.

Doable, but that removes all the free tiers of all the CDN's. AFAIK they all require an enterprise account to keep using ones own DNS and their own GSLB DNS failover. There are probably a few exceptions and one could maybe make something of that but I don't know which ones are the exceptions.