I have a series of cache-like tables used in an application that I am evaluating for a lift-and-shift to Azure SQL Data Warehouse.
The application uses a series of cache-like tables that are loaded and then used in joins to fact tables (of two or three dimensions, e.g., time, location, product). The cache-like tables are shared through the application and different reports load rows with arbitrary strings as an identifier in one column and the foreign key for the dimension column in the fact table.
It appears statistics are lost when a table is TRUNCATEd. Is it possible to keep the statistics as they were via hint, et al?
No you can not keep the statistics after a 'TRUNCATE'. Resampling your key columns (not using 'FULL SCAN') does not take that long anyway, in my experience. This is after all an MPP system.
Re locking, you should know the default transaction isolation level for Azure SQL Data Warehouse is Read Uncommitted so lock contention is not an issue.
The technique for spoofing statistics is not available in Azure SQL Data Warehouse at this time.