Final result is better than before, because scanning applies only into targets which are ending into the cloud. Content was plaintext in the cloud before (not counting server-side encryption).

With current change, they have "partial" E2E encryption and Apple can decrypt them only if CSAM treshold is reached. If we leave all speculation, this is a great improvement for privacy in CSAM context.

You can opt-out from scanning by not using iCloud.