When truncate is used, it is very fast and removes every record in the table. It’s important to keep in mind what type of table data is being deleted from and if the table holds data from multiple sources or not. A person can delete or truncate load and stage tables all day with little repercussion. Imagine if a person deletes data from a fact table, what would happen if they have three sources populating a fact table and needed to do a historical reload for the second data source and they used a truncate on that table.
The three types of tables that will be adversely affected by the DELETE or TRUNCATE are:
1) Fact tables – will require a historical reload from perm tables. Not too difficult, but can be time consuming.
2) Permanent tables – will require a historical reload, extracting from the ERP source system can be fairly tricky.
3) Dimension tables – deleting one of these is THE BIGGEST DANGER to a data warehouse, because the keys are lost and there is no guarantee that re-populating the table will ensure that the keys get sequenced in the same order. All fact tables that reference those keys will likely need to also be historically reloaded from permanent tables.