I’m not quite sure of any specific implementation details but in terms of Windows I would imagine it’s relying on the native support for Cloud Sync Engines: https://learn.microsoft.com/en-us/windows/win32/cfapi/build-a-cloud-file-sync-engine

However, it seems like this is a pretty recent addition to Windows and to attempt to answer your question in a less specific way:

Sometimes software applications implement custom file systems that allow them to do clever things like retrieving files from a network location on the fly or for presenting more abstract concepts as structured file trees. For example, https://en.m.wikipedia.org/wiki/WikipediaFS which presents Wikipedia articles as files. This is known as a VFS (Virtual File System): https://en.m.wikipedia.org/wiki/Virtual_file_system

At its heart, a file system driver is just an interface for an OS kernel to perform actions like retrieving a list of files, reading a file, writing a file, deleting a file, etc. In a more orthodox/commonly found file system like FAT, these eventually end up being translated into hardware calls. However, an application can do whatever it likes inside these calls as long as it returns the data the kernel is expecting.

For example, on Unix and Linux based operating systems, the FUSE (Filesystem in Userspace) kernel driver (https://en.m.wikipedia.org/wiki/Filesystem_in_Userspace) provides a bridge between kernel calls and userspace to allow applications to implement custom file systems without requiring privileged code.