A small, steady shortfall that never alerts
Reconciliation against the source shows the warehouse is short by a small number of orders every single day. It is never large enough to move a volume check. Re-running the extract for a past day changes nothing.
What you would do first
Answer before revealing anything. The value of the exercise is entirely in committing to a diagnosis you can be wrong about.
- 1Confirm the shape: a small loss spread evenly across every period, present on every day, and stable rather than growing.
- 2Anti-join source keys against the warehouse for one closed day and look at what the missing rows share. Here they will share nothing except long-running transactions.
- 3Compare each missing row's
updated_atwith the watermark values recorded by the extract runs. The missing rows fall just below a watermark that had already advanced. - 4Confirm the mechanism at the source: check whether the timestamp is set by the application before commit or by the database at commit.
What is actually going on
The trap
The fix that looks right. Read it even if you got the answer — especially then.
Widen the window by an hour. The daily loss shrinks enough to stop being visible in the reconciliation you were watching, the mechanism is untouched, and long transactions still fall through — now less often, and with no signal at all, which is a strictly worse position than the one you started in.