There are more orders than orders
A new table `stg_order_changes` is fed by change capture. An analyst counts rows for last Tuesday and gets a number noticeably larger than the count of orders in the source for the same day. Nothing failed, and the totals in the source are not in dispute.
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.
- 1State what one row of
stg_order_changesrepresents, and verify it: compareCOUNT(*)withCOUNT(DISTINCT order_id)for the day. - 2Look at a single
order_idwith several rows and read the change records in order. They will describe one order's life rather than several orders. - 3Check whether the change records carry an operation column — insert, update, delete — which confirms the grain immediately.
- 4Decide what the consumer actually needs: the latest state per order, or the full history of changes. They are two different models and both are legitimate.
What is actually going on
The trap
The fix that looks right. Read it even if you got the answer — especially then.
Wrap every query in
SELECT DISTINCT order_id and move on. The count is now right, the underlying question of which change is authoritative is unanswered, and every measure computed alongside — amounts, statuses, timestamps — is silently taken from an arbitrary one of the three change rows.