Drill 9 — The Merge That Couldn't Be Unmade

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

The story

The entity-resolution pipeline auto-merges two customer records: same name, same address — a father and son. The son's delinquency lands on the father's profile; the father gets declined and files a complaint that escalates to a regulator. Engineering goes to split the records and discovers the merge was destructive — source records overwritten, no lineage.

⏸ Stop here and ask the room: how long does the unwind take, and what would have made it a non-event?

Run it — facilitator script

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

What's the cost asymmetry between a missed duplicate and a wrong merge?

Listen for: A missed duplicate costs a little redundancy — two profiles, some duplicated marketing spend — and can be fixed any time; a wrong merge corrupts real decisions about a real person, invites complaints and regulators, and (if destructive) costs months. Match thresholds should be priced off that asymmetry, not tuned as if the two errors were symmetric. Weak signal: Talking about precision and recall as interchangeable dials — optimizing the match rate without ever pricing what each error type does downstream.

What does "reversible" require, concretely?

Listen for: Immutable source records; the merge stored as a view or link table with full lineage — which records, which rule or model version, what confidence, when; and an unmerge operation that has actually been exercised in staging. Weak signal: "We have backups" — a restore loses everything written since the merge, and nobody has ever rehearsed a surgical partial restore under regulator scrutiny.

Which of our automated actions are destructive?

Listen for: An inventory instinct — merges, dedups, cascade deletes, overwrites, auto-closures — and for each one the question: is the irreversibility necessary, or just the default the tooling shipped with? Weak signal: "Not many, I think" — an answer from memory instead of a list; the whole point is that nobody has ever enumerated them.

The reveal

Three months of manual reconstruction from upstream sources — for something that a soft-merge with lineage would have fixed in an afternoon.

Why this happens — the mechanics

Entity resolution is probabilistic by nature: it estimates whether two records describe the same person from evidence like names and addresses. Name-plus-address is genuinely strong evidence — which is exactly why the father/son case is the canonical trap. Family members share surnames and households, so the highest-confidence-looking matches include a systematic population of wrong ones (Jr./Sr. pairs, parents and adult children). The matcher isn't malfunctioning; the world contains adversarial look-alikes, and a threshold tuned on aggregate accuracy will confidently merge some of them.

The destructive part is usually inherited, not chosen. Physically collapsing records into one row is the storage-efficient default many master-data tools ship with, and "keep every source record forever plus a link table" looks like needless complexity right up until the first wrong merge. Nobody decides to make merges irreversible — they just never decide to make them reversible.

Organizationally, the costs land on different teams. The team tuning the matcher sees match-rate dashboards; the complaint lands with customer service and compliance months later, with no obvious thread connecting a declined applicant to a pipeline parameter. And the failure is invisible in the interim because a merged record looks exactly like a normal customer — richer, if anything. Nothing about the father's profile says "two people live here" until he applies for credit and the son's delinquency answers.

Diagrams

Problem — how the failure unfolds:

sequenceDiagram
    autonumber
    participant ER as ER pipeline
    participant RC as Records
    participant FA as Father
    participant RG as Regulator
    ER->>RC: Auto-merge - same name,<br/>same address
    Note over RC: Sources overwritten -<br/>no lineage (silent)
    RC->>FA: Son's delinquency lands<br/>on father's profile
    ER->>FA: Application declined
    FA->>RG: Complaint filed -<br/>escalates
    RG->>ER: Unwind the merge and<br/>explain the decision
    ER--xRC: Split impossible - source<br/>records destroyed
    Note over ER,RG: Three months of manual<br/>reconstruction

Solution — where the guardrails go:

flowchart TD
    A["Entity resolution flags<br/>possible duplicate"] --> B["Match confidence scored"]
    B --> C["Low confidence: routed to<br/>human review"]
    B --> D["High confidence: soft-<br/>merge as a view with full<br/>lineage"]
    D --> E["Source records remain<br/>immutable"]
    E --> F["Wrong merge reversed in an<br/>afternoon, a non-event"]
    D -.-> G["Alarm fires: merge audit<br/>trail flags disputed<br/>profile"]

What good looks like

• Source records are immutable, forever; the "golden record" is a computed view over a link table, never a physical collapse.
• Every merge writes a decision log: record identifiers, matching features, rule or model version, confidence score, timestamp — enough to explain the merge to a regulator without archaeology.
• Confidence bands with different actions: auto-merge only above a high threshold and only when the merge is reversible; a human-review queue with a service-level target for the band below.
• A never-auto-merge blocklist for known adversarial patterns: shared address plus generational suffix (Jr./Sr., II/III), known household or family links, matching name with a large age gap.
• An unmerge operation that exists as tooling — not a SQL improvisation — and is rehearsed quarterly in staging until it's a one-hour non-event.
• A disputed-profile flag: any complaint touching identity freezes automated actions on that entity and alarms the data-quality owner.
• A standing blast-radius review for every automated action in the pipeline: who is harmed if this is wrong, and can we undo it within an hour?

The lesson

Never let automation take an irreversible action when a reversible representation exists. Keep source records immutable, make merges a view with full lineage, and route low-confidence cases to humans — the blast radius of "wrong" includes regulators.

Named concepts: reversibility, soft-delete/soft-merge, data lineage, blast-radius design.

Real-world echoes

• Credit-bureau "mixed files" — records of two people fused into one — are a decades-old, well-documented category of consumer complaints and Fair Credit Reporting Act litigation in the United States: this exact failure class, operating at industry scale.

Beyond that genre, individual wrong-merge incidents almost never go public — they're settled quietly, which is exactly why teams keep rebuilding the same trap.

Transfer

List our automated actions that can't be undone in an hour. For each: is the irreversibility actually necessary?

Make it a ticket: produce the irreversible-actions inventory for our main pipeline, and for the single scariest entry, design (and staging-test) the reversible representation that replaces it.

Related

• Drill 8 — The Backfill That Rewrote History — the same sin against the past: destroying the record of what was true before.
• Drill 13 — The Day That Happened Twice — operations you can safely redo are the mirror image of actions you can safely undo.
• Theme 1, Drill 6 — The Health Check That Checked Nothing: automation acting on a wrong signal, with the blast radius nobody designed.