Can Git and iCloud Drive be effectively used together?

No, they shouldn’t be used together.

• Apple says “You shouldn’t store app folders, libraries, or .tmp files in iCloud Drive.” .git is an app folder.
• There is a list of filenames excluded by iCloud Drive that git doesn’t avoid. So, for example, if you name a tag “tmp”, that tag won’t be synced. There’s also nothing stopping git modifying its internal file structure, or iCloud Drive modifying its exclusion list, such that this sync failure always occurs.
• iCloud Drive ignores symlinks, git doesn’t. So, git status may return different results on different computers that iCloud Drive is done syncing.

More broadly speaking, git and iCloud Drive are two different lossy implementations of “what are the contents of this directory”, one shouldn’t expect them to be equivalent.