Your incremental load copies rows where `updated_at > last_watermark` every 15 minutes. Row counts slowly drift from the source, and a few updated rows never arrive. Why?

The watermark assumes rows become visible in updated_at order, and they do not. In PostgreSQL, now() and CURRENT_TIMESTAMP return the time the transaction started, but other sessions only see the row when the transaction commits. A transaction that starts at 10:00 and commits at 10:20 writes updated_at = 10:00. If your 10:15 extract already moved the watermark past 10:00, that row is never picked up. Hard deletes are lost too, because a deleted row has no updated_at to find.

Walk through the failure

Other causes of the same symptom:

• Code paths or bulk scripts that update rows without touching updated_at.
• Clock or time zone differences when the watermark is computed on a different machine.
• A watermark taken from the extract's own clock instead of the maximum value it actually read.

Fixes, from quick to thorough

1. Overlap window. Read updated_at > last_watermark - interval '1 hour' and upsert by primary key, so rereading rows does no harm. Choose the overlap longer than your longest transaction, and alert on transactions that run longer.
2. Set updated_at with a trigger, so every write path sets it, including scripts.
3. Track deletes: soft deletes with a deleted_at, or a periodic full key comparison.
4. Use change data capture from the database log such as PostgreSQL's logical replication. It sends every change, including deletes, and applies them in the same order as the source, so the timestamp problem goes away.

Prove the gap is closed

Run a daily reconciliation: counts and a checksum of key columns per day or per key range, source against target. It catches every one of these causes, including ones you have not thought of.

What interviewers want: understanding that commit order differs from timestamp order, now() returning the transaction start, the overlap-and-upsert fix, deletes, CDC as the thorough answer, and reconciliation as the safety net.