Windows even has a built-in overlay icon for an "offline file"--i.e., a file that exists within an HSM storage hierarchy, where the only copy of the data is currently in a lower part of the hierarchy (e.g. on a tape), and accessing it will trigger a delayed retrieval.
Of course, your own computer probably doesn't have any "HSM storage hierarchies", so even on an enterprise workstation, your local files will likely never show that overlay. Instead, it's more a thing you'll see if you have a home dir stored on a SAN, consumed by your workstation over SMB. In that sense, it's sort of a feature in the same group as "quota management."
Then again, it's not like you'd never run into it outside of an enterprise context; if you downloaded an AWS S3 filesystem driver for Windows, for example, the proper indicator for "this file exists only in Glacier" would be that same overlay, because S3's use of Glacier is also Hierarchical Storage Management.
Dropbox has smart people and I'm sure they would have wanted a feature like this from day 1, so if they're introducing it, I'm sure they have good answers. I'm just curious what they are!
It also will let you overwrite or interleave files unless both sides use advisory locking.
I'm curious how Dropbox has made this safe to use for people who don't want to think about things like "close-to-open consistency" or "stale file handle".
Also local files behave exactly like other local files so in that case (the typical case) all that pain you feel with NFS/FUSE goes away