I usually follow this strategy to load fact tables via ETL:
- Truncate the staging table
- Insert new rows (those added after the previous ETL run) into the fact table and changed rows into the staging table
- 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.