You're right about that. So it might look like this
static.domain.com (CDN subdomain with auto provisioned TLS)
static-uncached.domain.com (private pass-through subdomain when CDN is missing a file)
www.domain.com (app server hosted wherever)
You're right that you could do something similar with other CDNs including Cloudflare (you can just set the www subdomain to "bypass Cloudflare" to accomplish a similar result), but I'm not aware of any way to use Cloudflare on a domain without forwarding your nameservers to them, effectively giving them complete control over the domain. At least with Stackpath I can host DNS wherever and simply point the subdomains I want at them.
Also, by the time you do the work to split static files into separate subdomains you might as well go with a dedicated CDN. One of the selling points of Cloudflare is for sites serving everything on one subdomain that they can forward to Cloudflare and get caching without any work.