Drill 13 — The Day That Happened Twice

Theme: Data Disasters · Format: 20-min drill · Difficulty: core

The story

A nightly ingestion job fails halfway and is rerun — but the first attempt had already committed its rows. Tuesday's events now exist twice. Velocity features double-count: "3 applications from this device this week" becomes 6, tripping fraud rules. Decline rates spike; call-center complaints follow. Meanwhile the metrics pipeline also read the doubles, so daily volume dashboards show a great day.

⏸ Stop here and ask the room: what property was the ingestion missing, and who noticed first?

Run it — facilitator script

Timing: 2 min story · 10 min questions · 3 min reveal · 5 min transfer.

Why is "rerun the job" dangerous by default?

Listen for: Because at-least-once delivery is the default in distributed systems — a job that fails halfway may have already committed partial state, and a rerun appends on top of it. "Rerun" is only safe if idempotency was deliberately designed in, and by default it wasn't; the 2 a.m. reflex assumes a property nobody built. Weak signal: "Reruns are fine, we do them all the time" — mistaking survived luck for a safety property; the jobs that happened not to half-commit yet are not evidence.

What makes a pipeline safe to rerun?

Listen for: Idempotent writes — natural or business keys with deduplication or upsert semantics, or transactional delete-and-replace per partition so a rerun converges to the same state; and crucially, the property is tested by actually rerunning jobs in staging, not assumed from the design doc. Weak signal: "We check a flag to see if it already ran" — a run-marker scheme that fails on exactly the half-committed case that opened this story: the flag says "didn't finish," the table says "half of Tuesday is already here."

How many downstream consumers were poisoned before anyone noticed?

Listen for: An enumeration instinct — velocity features, fraud rules, the metrics pipeline, plus anything else reading the event store — and the sharpest observation available: the monitoring itself drank the same poisoned water, so the volume dashboard that should have caught the anomaly celebrated it instead. Weak signal: Stopping at "the fraud rules" — no mental map of the fan-out from the event store, which is the map you'd need in the incident.

The reveal

Customers noticed first — declined applicants calling in. Engineering traced it back through the fraud rules to the duplicate day.

Why this happens — the mechanics

Partial failure is the native condition of distributed batch processing: a job can crash after committing some of its writes, and the system has no built-in memory of which rows made it in. At-least-once delivery — everything arrives, possibly more than once — is what infrastructure gives you for free; exactly-once must be constructed on top of it with keys, transactions, or partition replacement. A pipeline written as a pure append has implicitly chosen at-least-once and inherited its consequences, usually without anyone making that choice on purpose.

The operational context guarantees the trigger. "The nightly job failed — rerun it" is the standard on-call reflex, executed at 2 a.m. by whoever is holding the pager, and it's the correct reflex for idempotent jobs. Nothing at the moment of rerun distinguishes a safe pipeline from an unsafe one; the interface is the same button. The gap between "safe to rerun" and "assumed safe to rerun" is invisible until the first half-committed failure finds it.

What makes this instance vicious is that the contamination reached the observers too. Every duplicated row is individually valid — right schema, right types, plausible values — so nothing at the row level can object; duplication only exists at the level of counts. And the one system that watches counts, the metrics pipeline, read the same doubled store, converting the loudest available symptom (volume anomaly) into apparent good news. When the check and the thing being checked share an input, they fail together — which is why the detection path ran through the call center instead of a dashboard.

Diagrams

Problem — how the failure unfolds:

sequenceDiagram
    autonumber
    participant IJ as Ingestion job
    participant ES as Event store
    participant FR as Fraud rules
    participant CU as Customers
    IJ--xES: Fails halfway - rows<br/>already committed
    IJ->>ES: Rerun commits Tuesday<br/>again
    Note over ES: Every Tuesday event<br/>exists twice (silent)
    ES->>FR: Velocity features double-<br/>count - 3 becomes 6
    FR--xCU: Fraud rules trip -<br/>declines spike
    Note over ES: Metrics pipeline reads the<br/>doubles - dashboards<br/>show a great day
    CU->>IJ: Declined applicants call -<br/>customers notice first
    Note over IJ,CU: Trace back through fraud<br/>rules to the duplicate day

Solution — where the guardrails go:

flowchart TD
    A["Nightly ingestion job<br/>fails halfway"] --> B["Rerun triggered"]
    B --> C["Idempotent ingestion:<br/>natural keys with dedup"]
    C --> D["Duplicate rows rejected on<br/>second write"]
    D --> E["Tuesday exists exactly<br/>once downstream"]
    C -.-> F["Alarm fires: row count vs<br/>expected daily volume"]
    E --> G["Rerun safety proven in<br/>staging game days"]

What good looks like

Every ingestion table has a natural or business key with a uniqueness constraint — or merge/upsert write semantics — so a duplicate write is rejected or absorbed, never accumulated.
Where keys are awkward, write by partition replacement: delete-and-rewrite the day's partition inside a transaction, so any number of reruns converges to the same state.
A post-load reconciliation check on every batch: loaded row count against source count and against the expected daily-volume band (say, ±3 standard deviations of the trailing 30 days), plus a duplicate-key scan — failures block downstream consumption.
A run ledger: every batch records its watermark, row counts, and completion status; the rerun path consults the ledger and resumes from the watermark instead of starting from zero.
Volume-anomaly alarms fed by a dedup-aware count (count distinct on the natural key), so the monitor can't be poisoned by the same duplication it exists to catch.
A quarterly game day: rerun yesterday's production jobs in staging and assert the downstream tables are byte-identical — reprocessing safety as a tested property, on a calendar.
A runbook rule for on-call: no manual rerun without checking the run ledger's committed watermark first.

The lesson

At-least-once delivery is the default in distributed systems, so idempotent ingestion is not optional: natural keys with dedup, or transactional "exactly-once" writes per partition. Every batch job should be safe to run twice — that property must be tested, not assumed.

Named concepts: idempotency, exactly-once vs. at-least-once semantics, reprocessing safety, downstream contamination.

Real-world echoes

Duplicate-ingestion incidents surface publicly only in their most customer-visible form — double-charged cards, doubled payments — and even then the postmortems stay internal. The version in this drill, where the duplicates poison decisions and dashboards rather than statements, essentially always stays private. That's the point: the customers are the detection system, which is exactly the property you're trying to remove.

Transfer

Which of our jobs would corrupt data if run twice today? Try it in staging this week — that's a one-hour game day.

Make it a ticket: schedule the staging game day — rerun our main nightly ingestion against a snapshot, diff the downstream tables, and file a finding per job that doesn't converge; the diff report is the deliverable.

Drill 12 — The Join That Ate the New Customers — the same missing instrument, row counts, catching the opposite failure: too few rows instead of too many.
Drill 9 — The Merge That Couldn't Be Unmade — safe-to-redo and safe-to-undo are the two halves of operational reversibility.
Theme 1, Drill 4 — The Stale Score: velocity features silently corrupted by an operational change, with fraud outcomes as the first detector.