Duplicated Orders, Regressed Status and a Four-Hour Fraud Lag

These are two different defects and merging them will produce a fix that addresses neither.

Duplicates - delivery semantics Auto-commit before the sink write gives at-least-once delivery with a non-idempotent sink, so a restart or rebalance reprocesses records already written. The warehouse loader restarting from the last batch start compounds it: every failure re-writes part of the batch. Evidence: query the warehouse for order_id duplicates joined to consumer restart timestamps; if duplicates cluster around deployments, rebalances and failures, the cause is confirmed. Also check for duplicate events from the source itself - a Debezium snapshot taken while streaming can re-emit rows.

Status regression - ordering Kafka guarantees order within a partition, not across partitions. Keying on order_id should keep one order's events in one partition, so regression implies one of:

• some events are keyed differently, or arrive with a null key and are round-robined;
• an older event was replayed after a newer one with no version check at the sink;
• a second producer writes order status independently. Evidence: sample 200 regressed orders, pull their event headers from the topic, and check partition assignment and key per event. Null keys and mixed keys will show immediately.

The distinction matters Deduplicating will not stop regressions, and enforcing order will not remove duplicates. Fix both.

The lag is an architecture problem: two consumers with a 2-second requirement and a 10-minute requirement share a coupling they should not have.

Warehouse consumer

• At-least-once delivery is acceptable if the sink is idempotent. Write with an upsert keyed on (order_id, event_version) or dedupe on an event_id stored in the target.
• Commit offsets only after the sink transaction commits.
• Batch by time, not by volume, so a slow batch cannot silently extend freshness.

Fraud consumer

• Separate consumer group, separate scaling, and its own alerting on lag. It should never be measured by the warehouse's health.
• Partition count caps parallelism: six partitions is six concurrent consumers. During peak, hot partitions (large sellers) skew the load even with order_id keying. Increase partitions or use a composite key if the ordering requirement permits.
• Add a per-order monotonic version and have the scoring service reject out-of-order events rather than overwrite state.

Fallback when lag exceeds threshold Define it explicitly. Options: score from the current-state table directly rather than from the stream; apply a conservative interim rule and re-score asynchronously; or fail open to manual review. Choose based on fraud loss tolerance, document it, and test it. A four-hour lag discovered after the fact means the threshold was never defined.

Judgement Do not promise exactly-once. Promise idempotent effects, which are achievable and testable.

The two are related: both depend on knowing what is in the topic and what was dropped. Do them as one piece of work.

Investigating the $120k Value present in the warehouse but absent from source is unusual and points to one of:

• Records that were deleted or updated at source and the warehouse retained a stale pre-image - check whether delete events are captured and honoured.
• Duplicated writes inflating totals - 0.3% duplication on high-value orders is plausible arithmetic for $120k on this volume. Quantify before assuming a lost-event cause.
• Events landing in a dead-letter store and being replayed inconsistently.
• Currency or unit mismatches between source and warehouse, which present as value divergence rather than row divergence. Method: reconcile at three grains - order count, order value, and order-status distribution. The grain that breaks first identifies the class of cause.

Making the replay safe

1. Confirm retention. If Kafka holds 30 days, replay from the topic; otherwise replay from the raw archive landed by the pipeline - verify the archive is complete first, or the model trains on a biased history.
2. Replay into a separate consumer group and a separate target schema. Never replay into production tables a live consumer is writing.
3. Require the sink to be idempotent by event_version, so a replayed event cannot regress state or double-count.
4. Throttle, and watch broker and consumer-group lag during replay - a 30-day backfill competes with Black Friday-scale traffic.
5. Publish the replay window and expected effects before starting, so downstream owners can distinguish replay artefacts from real anomalies.

Sequence Build the raw archive completeness check first. Both the reconciliation and the replay depend on trusting it, and it is the cheapest of the fixes.