Preserving full refresh data source I have a SQL Server database that gets updated every week with a completely new set of data.
I am currently using two staging databases. A series of view databases are connecting to these and one week the view tables have views that point at one set of tables contained in one of the staging databases and the then the views are altered the other week when the ingest of the refreshed data is complete. Staging tables are truncated before each load. There are 12 tables which range from 50Gb to 100Gb.
This is to avoid people waiting for the lengthy ingest to complete and keeps people working.
Are there any more efficient strategies? In Oracle I would turn a materialised view into a table and then refresh the view once the ingestion was complete.
I don't think there is a similar strategy for SQL Server. I am using SQL Server 2016 enterprise.
ALTER TABLE ... SWITCH would allow you to switch in each table when you want to change the &quot;live&quot; table.  The only requirement is to ensure both tables involved in the switch are in the same filegeoup, and have the same structure.