How to Handle Deleted Rows in an Incremental Load

Most data warehouse issues are apparent: a pipeline fails, an alert fires, you get a phone call at an unsociable hour.

Deleted source data doesn’t do this. A row is removed from the source, and no one notices. It sits in the warehouse silently skewing measures and KPIs, until the finance director asks why a cancelled order is still in the revenue report.

What’s causing this?
Incremental loads use watermarks to avoid re-extracting unchanged data. After each load, the maximum ModifiedDate (or similar) is saved and the next run only pulls records changed since then. This saves time and compute, but a deleted row has no ModifiedDate to catch. It simply stops appearing in extracts and stays in the warehouse forever.

Before picking a solution, ask two questions of the source system: does it actually delete data, and how should deletions be handled?

Soft Delete vs Hard Delete
A soft delete, flags a record rather than removing it, IsDeleted is the usual name for it. The record still exists, so the load picks up the change.
A hard delete removes the record outright. With no ModifiedDate left to catch, it needs one of the following:

Option 1 – CDC / Mirroring
Both read the source’s transaction log and will pick up deletes.
CDC captures the delete as a change event which can be acted on directly downstream. Mirroring just keeps a continuously updated replica; a deleted row disappears from it with no event to react to. To get an IsDeleted flag out of Mirroring, reconcile it against the DW using either Option 2 or 3.

Option 2 – Business Key Reconciliation
Periodically run a full extract of the business keys and compare them against the DW. Anything in the DW but missing from the extract has been deleted, so flag it as IsDeleted. Pulling only the business keys makes this cheap enough to run regularly, even on large tables.

Option 3 – Full Reload
For small, standalone reference tables, truncate and reload in full. Anything missing after the reload is deleted, so no detection logic is needed.
For anything larger, or tables not solely using SCD Type 1, don’t truncate the DW table. Fully load into staging and merge into the DW as normal, with one addition: if a row is currently in the DW but missing from staging, flag it as IsDeleted and close the record if it’s SCD Type 2.

What to do with records that have been identified as deleted?
– Don’t hard delete records from the DW, instead add an IsDeleted column and set it to true. This allows foreign keys to stay intact, the deletion is auditable and can be reversed if required.
– For SCD Type 2, end the current row.
– Filter deleted rows out at the reporting layer. Leave the DW complete.
– Keep the process idempotent, so re-runs don’t re-flag or double up history.

Hopefully this has highlighted the issues of data deletion in source systems and provided suitable options to handle it.

Tags: Blog