Hard Disk Drives (HDD): What options are there for transparent, virtually unlimited storage -- are there any applications which can transparently use dropbox, iCloud, or box.net to  support "smart" caching?

You neglected to say what OS you are using.  I'll assume Windows since I have an answer for that. Jungle Disk offers exactly this.  You can map a local staging area in your computer to one of the supported cloud services (I use Amazon S3).  The program creates a drive letter to represent what is in the cloud and shows all files, whether they are on the computer or not.  When you copy files to the drive it copies them first to the staging area then as quickly as it can it copies them to the cloud drive.  When you request a file that isn't in the staging area it begins downloading the file and gives you access apparently long before the whole file has been downloaded (of course, if your application requires the entire file then it will just have to wait). It provides encryption in and out of my computer and does not allow the keys to leave your system (so if you lose your encryption key they can't help you). They have a "pro" service which uses indexes stored on their own servers to allow you to upload only part of individual files (so, if you have a very large file and only 10% of the file has changed, you only have to upload the 10% and the device driver will handle applying the delta when you request the file again).  This is supposed to save a significant amount of data transfer and storage (I haven't done the work to actually prove this out, but I do pay for the extended service). It also doubles as more traditional backup software to the same cloud service (but to a different bucket).