How to handle source table row deletions in SQL ETL?

Viewed 92

I usually follow this strategy to load fact tables via ETL:

  1. Truncate the staging table
  2. Insert new rows (those added after the previous ETL run) into the fact table and changed rows into the staging table
  3. Perform updates on the fact table based upon the data in the staging table

The challenge I am facing is that the source table rows can be deleted. How do I handle this deletion in ETL? I want the deleted row to be removed from the fact table. I cannot use merge between source oltp db table and target data warehouse table because that puts additional load at each ETL run.

Note: the source table has got a date last modified column, but this is not useful to me because the record disappears from the source table upon deletion.

0 Answers
Related