Does CF matter, when intermediate ISPs are collecting IP address and DNS query activity and can be subpoenaed?

The answer to both this and parent is yes: partial privacy improvements are still improvements. There are two big reasons for this and many smaller reasons as well:

First, legal actors prioritize who to take action against; some cases are “worth seeing if $law-enforcement-agency can get logs from self-hosted or colo’d servers with minimal legal trouble” but not “worth subpoenaing cloudflare/a vpn provider/ISP for logs that turned out not to be stored on the servers that received the traffic“.

Second, illegal actors are a lot more likely to break into your servers and be able to see traffic information than they are to be able to break into cloudflare/vpn/ISP infrastructure. Sure, most attackers aren’t interested in logs. But many of the kind of websites whose logs law enforcement is interested in are also interesting to blackmailers.