This might be oversimplifying a bit - How about keeping all OS files, app files etc on SSD. No auto-tiering of those. - Then some equivalent of a 'cron job + filter driver' combo that moves data files to and from SSD to the 1/3TB disk? When I say filter driver, I am thinking of Windows file system mini-filter driver. Linux equivalent would be VFS I guess.

How difficult is it to implement some thing like that in a pre known 2 tier configuration context? And what would be the effectiveness of such a solution?

In Windows filter drivers have been used effectively to implement file tombstoning for archive purposes and utilizing reparse points/extended attributes to store the remote URI (for recalling file contents from remote archive upon intercepting a read request). It's not too hard to write a mini filter driver in Windows and use it in conjunction with reparse points to implement tombstoning in filesystem.