Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. Detecting hard deletes in batch

Batch & Streaming · Batch Design

Detecting hard deletes in batch

Mediumbatch-streaming-49
hard-deletesbatchcdcdata-integrity

Question

An incremental load uses updated_at, but rows deleted in the source never disappear in the warehouse. How do you fix it?

Solution

Incremental batch loads that filter on updated_at > last_watermark fail to detect hard deletes because physically deleting a row removes it from the source database, leaving no updated record or timestamp behind to query. You resolve this by implementing log-based Change Data Capture (CDC), introducing soft-delete flags in upstream applications, executing periodic primary key anti-joins, or computing snapshot diffs. In analytical warehouses, matching records should be marked with an is_deleted flag rather than physically dropped to preserve historical lineage and reporting consistency.

Why timestamp watermarks miss deletions

When an upstream relational database executes a hard delete (DELETE FROM orders WHERE id = 101), the database removes the physical row from disk.

An incremental query filtering on modified timestamps only inspects surviving records:

SELECT * FROM source_orders
WHERE updated_at > '2026-10-06 00:00:00';

Because deleted row 101 no longer exists in source_orders, the query never returns it. The warehouse retains row 101 indefinitely, causing downstream revenue aggregates and record counts to drift higher than true production figures.

Four engineering patterns to capture deletions

Data engineers use four primary strategies to detect and propagate hard deletes:

  • Log-based CDC: Tools like Debezium read database write-ahead logs (WAL) or binlogs directly. When a DELETE transaction occurs, the CDC engine captures the tombstone event along with the deleted primary key, streaming it directly to the warehouse.
  • Upstream soft-delete flags: Partner with application engineers to replace hard deletes with soft deletes (UPDATE orders SET is_deleted = TRUE, updated_at = NOW()). This updates the timestamp column, allowing normal incremental loads to catch the change.
  • Periodic primary key anti-join: Periodically query a lightweight list of all active source keys (SELECT id FROM source_orders) into a temporary staging table. Run an outer join against warehouse keys; any key present in the warehouse but missing from the source has been deleted.
  • Snapshot diff comparison: For medium-sized tables, extract full daily snapshots into object storage and execute an anti-join between consecutive days to identify absent keys.

Preserving history with soft deletes

When processing deletions in analytical warehouses, avoid issuing physical DELETE statements on fact and dimension tables.

Instead, update target records with is_deleted = TRUE and populate a deleted_at timestamp. This preserves audit trails, allows historical reconstruction of state at past dates, and lets downstream data models filter out inactive records cleanly.

PreviousNext