How Do You Catch Deletes?
An incremental filter asks 2 things of a source row: that it exist, and that its change column pass the watermark. A row deleted at the source fails the first test before the second is ever evaluated. The target keeps the row forever, the revenue report counts a cancelled order, and nothing logs an error, because from the pipeline's point of view nothing happened. Deletes are the first thing an interviewer probes after you say incremental.
Worked example: 2,000 orders a day vanish
Suppose a cleanup job at 03:10 hard-deletes roughly 2,000 orders a day from the source, most of them test orders or duplicate submissions. Over a quarter that is 180,000 phantom rows in orders_fact, and every one of them carries an amount that gets summed into a daily revenue figure. Here is a 5 row slice on the morning after 2 of them were removed.
Orders 5003 and 5005 were deleted at 03:10. The 04:00 run filtered on updated_at above yesterday's watermark, found 5002, whose status changed from pending to paid, and loaded it. Rows 5003 and 5005 matched nothing and stayed in the target, which leaves the daily revenue figure 135.01 too high.
Option 1: soft deletes at the source
If you can change the source, the cheapest fix is to stop deleting. The cleanup job sets is_deleted = true, and the same statement bumps updated_at. The row now passes the incremental filter like any other update, arrives downstream carrying its flag, and either the load applies the delete to the target or every consumer adds WHERE NOT is_deleted. This breaks when a code path sets the flag and forgets the timestamp: the row is logically gone and invisible to the load, which combines the worst of both options. Make the timestamp bump a database trigger rather than an application convention, so no code path can skip it.
Option 2: reconcile the keys
When the source can't change, pull every primary key, and only the key, once a day. The 40 million 8 byte integers come to roughly 320 MB, 50 times less than the full rows, and land in a few minutes. Stage them, then remove whatever the target holds that the source no longer does.
DELETE FROM orders_fact
WHERE order_id NOT IN (
SELECT
order_id
FROM orders_src_keys
)In an interview, point out that this statement catches deletes once a day, at the granularity of the key pull, while the incremental run itself may be hourly; a deleted order can therefore show in a report for up to 24 hours. The statement is also dangerous when the key snapshot is partial. If the key extract fails halfway and 20 million keys are staged, the delete removes 20 million correct rows and reports success. Guard it with a count check: refuse to run the delete when the snapshot holds fewer than 99 percent of yesterday's keys, since a real day never deletes more than 1 percent.
Option 3: read the change log
Databases record every delete in their own transaction log, and a pipeline that reads that log receives the delete as an event with the key attached. It is the most complete answer and the subject of the next tier of this track. For now, know that it exists, that it needs access to the database internals, and that it's the only option which catches a delete within seconds rather than at the next daily reconciliation.
| Option | Source change needed | Delete latency | Extra daily cost | Fails when |
|---|---|---|---|---|
| Soft delete flag | yes, cleanup job and a trigger | next incremental run | none | a code path sets the flag without the timestamp |
| Key reconciliation | no | up to 24 hours | 320 MB key pull and an anti-join | the key snapshot is incomplete |
| Change log | database access, no schema change | seconds | a log reader running continuously | the log is truncated before it is read |
- Do
- Bump updated_at in the same statement that sets is_deleted, by trigger
- Pull keys only for reconciliation, never full rows
- Count the key snapshot before any delete runs against it
- Keep deleted keys as tombstones in the raw layer for backfills
- Avoid
- Trust a soft-delete flag that application code sets by convention
- Run a reconciliation delete that reports success on a partial snapshot
- Assume a hard-deleted row ever reaches a timestamp filter
- Measure delete latency by the incremental run interval when keys arrive daily
About this lesson
This section is part of the Incremental Loading: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.