Cloud storage is a wonderful way to extend limited hard disk or SSD space on your computer to an accessible internet-reachable location. Many services combine synchronization and storage offloading. Synchronization ensures that files on every linked device are stored in a central repository and copied and get up to date on every attached device. Storage offloading lets you opt to keep the file in central storage but not on every end point after the file is first uploaded.
Dropbox and iCloud Drive are two popular options for macOS, iOS, and iPadOS cloud storage, and they differ substantially in this approach. Dropbox changed its approach last summer for personal accounts, so it might be a little different than you recall if you haven’t used it for a while.
iCloud Drive works with any tier of iCloud storage, although you can only really get much use out of it at the 200GB tier or higher. macOS entirely manages which files are stored on your Mac, and which are purged to free up storage, and only when you have Optimize Mac Storage checked in the iCloud preference pane (macOS Mojave and earlier) or in the iCloud tab of the Apple ID preference pane (macOS Catalina). You can also check the Desktop & Documents Folder to sync and optimize those folders across Macs.
macOS uses various cues about file access and modification to remove those in least use from each Mac with that setting enabled. Those files are always stored in iCloud, however, and can be reached via the Files app in iOS and iPadOS or iCloud Drive via iCloud.com.
With Dropbox, the Dropbox folder on each Mac you use automatically syncs and updates everything from all other Dropbox sources on all platforms and via Dropbox.com. You can Control-click any file or folder in the Finder, however, and choose Smart Sync > Online Only to shift the file or folder and any nested contents off the current Mac. (A paid Dropbox plan is required.)
With both iCloud Drive (and Desktop & Documents) and Dropbox, you can double-click or open a file or folder to have its contents appear, and any necessary files are automatically downloaded and made available locally as required. The file and folder structure is intact, too, so you know exactly what’s stored in each folder, whether it’s on the Mac or not.
The only limit is the amount of bandwidth you may have at the location you’re attempting to retrieve the file from. For large files, that may be an issue on thin broadband pipes.