Parallel Order Systems, Duplicate Revenue, and Status Running Backwards
Tessera Logistics ingests order events from two order-capture systems: a legacy OMS delivering a nightly full extract, and a new cloud OMS streaming hourly change-data-capture through Kafka. During an 11-week parallel run both systems are live; orders created in the cloud app are replicated to the legacy system by middleware with a four-hour lag. The warehouse sums order value across both feeds, and the finance reconciliation is off by $1.4M against a $62M base. Analysts also report orders flipping status backwards - shipped, then back to picked - and late-arriving dimension rows producing null product keys on the fact table. The team's proposal is to deduplicate in the BI layer with SELECT DISTINCT and add a lookback window.
1. Where should deduplication and idempotency live, and what is the correct record-selection rule given out-of-order events?
Read the worked answer
The finance error is not a DISTINCT problem. SELECT DISTINCT removes identical rows. The $1.4M arises because the same business order exists under two identities across the feeds - one created in the cloud system, replicated to legacy four hours later under a different key - so the row sets are disjoint and DISTINCT keeps both. Adding DISTINCT also risks collapsing legitimately identical line items.
Where the fix belongs. In staging or integration, not in BI. BI-layer deduplication is invisible to every other consumer, cannot be reused, and will be reimplemented differently by each team.
Selection rule. Do not deduplicate on order ID alone, because the two systems mint different IDs. Build a canonical order identity map - the middleware correlation ID where it exists, otherwise an entity-resolution key (customer, timestamp window, amount bucket) reviewed by stewardship. Then:
- Land both feeds append-only and immutable, with source and ingestion metadata.
- Deduplicate on the canonical key, choosing the survivor by business event time plus an explicit source-precedence table: cloud OMS is authoritative for orders it originates, legacy for its own. Never choose by load time.
- Make the merge idempotent - MERGE on canonical key with a row hash, so replaying a batch cannot create a second row or double the measure.
- Use the CDC offset plus a watermark with overlap (two hours); overlap is safe only with idempotency, never without it.
- Reconcile the canonical count against both feeds daily and alert on variance above 0.1%.
2. Design the handling for status regression and late-arriving dimensions. Distinguish the symptom from the fix.
Read the worked answer
Status regression. 'Shipped then picked' is out-of-order delivery, not a business event. Two fixes, both required:
- In the pipeline: order by the event timestamp in the payload, not the Kafka produce time; apply a watermark and divert events older than the watermark to a reconciliation side channel instead of applying them blindly.
- In the model: represent status as a state machine with legal transitions. 'Shipped to picked' is not legal, so it is rejected and logged rather than overwriting current state. Keep full event history in a status history table and derive current state from it.
The dashboard regression is the symptom. The defect is a sink that applies last-write-wins on arrival order.
Late-arriving dimensions. Do not let null product keys reach the fact table.
- Insert an inferred member (key -1, 'Unknown' attributes, plus audit columns: first_seen, source, resolved flag) so the fact loads and the measure survives.
- When the real dimension row arrives, update the inferred member in place where the surrogate key can be preserved; otherwise re-point facts in a correction job and disclose the restatement.
- Track inferred-member age; anything unresolved past 72 hours is a data quality incident with a named owner.
Symptom versus fix. SELECT DISTINCT and a lookback window are symptoms. Canonical keys, idempotent merges, event-time ordering with watermarks, state machines, and inferred members are the fix. A lookback window is a tuning parameter that hides late data for a while; it does not make the pipeline correct.