An analytics team extracts daily using a `modified_at` watermark. Reconciliation shows the warehouse has 3% more customers than the source. Why?

What the interviewer is testing

Whether you know the specific and very common failure of watermark-based extraction.

The cause

Deletes. A watermark query selects rows where modified_at is greater than the last run. A row deleted from the source does not appear in any query, so the warehouse never learns about it and the record lives forever downstream.

3% more customers is exactly the shape of accumulated deletions over months or years.

The related and equally silent failure: updates that do not touch the watermark column. If any process writes to the row without maintaining modified_at — a bulk correction, a migration, a trigger-less direct update — the change is invisible to extraction, and the warehouse holds stale values indefinitely.

The implications beyond reconciliation

This is not only a data quality issue. Records deleted in the source were often deleted for a reason — a data subject erasure request, a retention policy, a duplicate removal. The warehouse copy means the organisation believes it deleted personal data and has not, which is a compliance exposure that nobody has connected to the pipeline.

The fix

Change data capture from the transaction log captures every insert, update and delete in order, with minimal load on the source. It is the correct answer and requires operational investment: snapshot and cutover handling, key-based partitioning for ordering, schema evolution policy, and — the one that catches people — replication slot monitoring, because a stalled consumer causes the source database's disk to fill.

If CDC is genuinely unavailable, the interim mitigation is a periodic full key reconciliation: compare the set of primary keys in source and target, and soft-delete the difference. Weekly is usually enough, and it should be instrumented so the deletion volume is visible.

What a strong answer adds

Asking whether the warehouse should hard-delete or soft-delete. Analytical history often legitimately retains a record of a deleted entity — but that must be a decision, with a retention rule, not an accident of the extraction mechanism.

Common weak answers

Adding a full reload, which is expensive and treats the symptom. Blaming the source team's timestamp discipline without addressing deletes.