They use it in SmartSync, so files that are actually stored in the cloud and not physically on your local machine, are visible and selectable in the Finder and automatically downloaded on demand.
It would be great if there were some user-space support for this in next year's macOS release. I think it would be useful for a lot of cloud storage providers.
Apple previously added third-party sync integration points into the Finder to stop Dropbox from injecting into that process (when it was easier and Apple was locking it down). It’s likely they do not want to break Dropbox and will try to support whatever it needs.
There is NSFileProviderExtension, but last I heard it was still a very immature API.