A replay, a duplicate batch, and a status that never lands
Tailspin receives retail orders through a REST API. A pipeline runs every 15 minutes, calls the API with a changed-since parameter, and appends the results to bronze_orders. A notebook then appends rows to silver_orders, filtering out any order_id that already exists in silver_orders via a left anti join. Nothing else is recorded about the incoming batch.
On 3 March the API vendor had a six-hour outage and replayed 06:00 to 12:00 the following morning; some records arrived twice within the same batch. The 4 March revenue report was 18% high. Separately, orders cancelled the day after they were placed still show as open in silver, even though bronze holds the updated status. The vendor guarantees replays of up to 48 hours and cannot guarantee ordering.
1. Two distinct defects produce these symptoms. Identify both and explain the mechanism for each in terms of what the notebook actually does.
Read the worked answer
Both defects live in the same left anti join.
Defect 1 - within-batch duplicates survive. The anti join compares incoming rows to silver, not to the rest of the incoming batch. When the replay delivers the same order_id twice in one batch, neither copy is in silver yet, so both pass the filter and both are inserted. Duplicate rows then multiply through any downstream sum, which is why revenue inflated by 18% rather than by 100%. The anti join was never a deduplication step; it was an insert-once gate.
Defect 2 - updates can never land. The same gate treats the first arrival of an order_id as final. When the cancellation arrives a day later, the order_id already exists in silver, the anti join discards the row, and bronze's correct status never propagates. This is not a late-data problem - the data arrived fine. It is an insert-only contract applied to a mutable entity.
The deeper issue: bronze records no batch identity or arrival sequence, so nothing downstream can tell a replay from a genuine second event, or an older version from a newer one.
2. Redesign the silver write to be idempotent and order-independent. Specify the key, the versioning, and what must change in bronze.
Read the worked answer
Bronze first. Append-only bronze is fine and should stay that way - it is the replay log. Add two metadata columns at ingest: an arrival timestamp and a monotonically increasing batch id. Without them you cannot resolve two versions of the same order that carry no useful source timestamp, and you cannot identify a replay window after the fact.
Silver: dedupe the source, then MERGE.
- Select from the incoming set with
row_number() over (partition by order_id order by last_updated_ts desc, _batch_id desc)and keep rank 1. This collapses within-batch duplicates and makes the result independent of arrival order. MERGE INTO silver_orders AS t USING deduped AS s ON t.order_id = s.order_id WHEN MATCHED AND s.last_updated_ts > t.last_updated_ts OR (s.last_updated_ts = t.last_updated_ts AND s._batch_id > t._batch_id) THEN UPDATE SET ... WHEN NOT MATCHED THEN INSERT ...
The version tuple is the whole design. Source timestamp orders real events; batch id breaks ties when the source timestamp is missing, coarse, or repeats. Both conditions are needed, because the vendor guarantees neither ordering nor uniqueness.
A replay now becomes a no-op or a harmless re-update: the same version cannot apply twice, and an older version cannot overwrite a newer one. Idempotency comes from the predicate, not from the scheduling.
3. How do you detect this class of problem automatically, and how do you backfill 3 March without a second inflation?
Read the worked answer
Detection - reconcile between layers, not within a layer. Add a post-load check to the pipeline that compares, for the window just processed, the distinct order_id count in bronze with the row count in silver, and the sum of order value in silver with a recomputation from deduped bronze. Any mismatch fails the pipeline and alerts the owning team through Activator rather than waiting for a finance review. A second, cheap check: day-over-day revenue movement beyond a threshold band should require a human acknowledgement before publish. Duplicates are invisible to row-level tests that assume uniqueness, so the assertion has to be a cross-layer count.
Backfill. Because bronze is append-only and replay-safe, it is the source of truth - rebuild rather than patch. Parameterise the notebook by date range, re-run the dedupe-and-MERGE logic over the 3 March window from bronze, and reconcile the rebuilt total against a known checkpoint (the 2 March closing position plus the vendor's own daily total) before republishing. Re-running is safe precisely because the MERGE predicate is version-based; running it twice produces the same table, which is the property the old anti join lacked.
Do not delete the duplicate rows by hand. You will miss some, and you will have no way to prove you did not.