Optimize Mac Storage is disabled on my iMac and Bombich (Carbon Copy Cloner) confirmed to me last week that CCC cannot access iCloud Drive files even if they are resident on my iMac. This is their answer .....
While I can appreciate your frustration in working with the iCloud data, its misdirected. This sits squarely on Apples shoulders. They choose to bury this data and obfuscate it by having it presented differently in the Finder as if it were located elsewhere. Dropbox, Box, and others provide a cleaner path to the data.
and
This can get a little confusing when you use a shortcut to access the location like the "iCloud Drive" item in the Finder. That's not the "real" location of the files, the iCloud sync folder on your computer its located here "USER_NAME/Library/Mobile Documents/“.
Further complicating the issue, in macOS 10.15 Catalina, this can be harder to find as it is on the " - Data" volume specifically here: "Macintosh HD - Data/USER_NAME/Library/Mobile Documents/“.
Trying to include USER_NAME/Library/Mobile Documents in the CCC target list does not work either because the files in that location are buried in a mass of system gobbledegook
Have you actually tried and succeeded with iCloud Drive back up by any means other than Time Machine or manual copy?
Steve