---
name: record-linkage-dedup
source: https://app.decimal.ai/s/record-linkage-dedup@1/SKILL.md
source_sha256: ec6c09030d81
---

# Record linkage and de-duplication without a shared id

Two rows can describe one real customer while agreeing on nothing exactly: `Bob` vs `Robert`,
`(415) 555-0100` vs `415.555.0100`, a maiden name vs a married one, an email present on one side
only. There is no id to join on — sameness must be inferred. Shown this task, the base model
tends to (a) test rows for exact-string equality and miss the real duplicates, and (b) when it
does collapse a pair, keep one row and throw the rest away, silently losing every field that
lived only on the discarded rows. This skill supplies the discipline that prevents both: bucket
to make the problem tractable, match on normalized field-aware similarity, gate the merge on
confidence with a middle band that goes to a human, and combine survivors so no value is lost.

## When to activate

- Combining two or more overlapping lists of people, companies, or accounts into one clean set.
- Collapsing a table where the same entity was entered many times with drifting values.
- Linking rows across two systems that hold no common identifier column.
- Any "find the duplicates" / "de-dupe" / "who is the same here" request where no key exists.

Not this skill: resolving mentions to a fixed in-house id set (entity-normalization), or joining
on a key that already exists (an ordinary keyed join).

## The method

### 1. Bucket first — never test all pairs

A list of N rows has ~N²/2 possible pairs; at 200k rows that is tens of billions of comparisons.
Cut it: derive a cheap grouping value that two versions of the same entity will almost always
agree on, put rows sharing that value into the same bucket, and only compare rows inside a bucket.

- Good grouping values: first few characters of a normalized surname, postal-code prefix, the
  last 7 phone digits, an email's domain, a Soundex/metaphone code of the name.
- A single grouping value risks splitting a true pair that disagrees on it (a typo'd surname).
  Run more than one pass with different values and union the candidate pairs each pass proposes.
- The grouping step trades completeness for speed on purpose — pick values robust to the errors
  you actually see in the data.

### 2. Compare with field-aware, normalized similarity

Normalize each field to a canonical form BEFORE comparing, then score each field with a metric
that fits it. Never test raw text for equality.

| Field | Normalize | Compare with |
|---|---|---|
| Person name | case-fold, strip punctuation, expand nickname → formal (Bob→Robert) | Jaro-Winkler / edit distance; phonetic for spelling drift |
| Company name | drop legal suffixes (Inc, LLC, Ltd), case-fold, strip punctuation | token-set / edit distance |
| Address | expand abbreviations (St→Street, Ave→Avenue), standardize unit/zip | token similarity on the normalized string |
| Phone | strip formatting, drop country code, keep the local digits | equality on the normalized digits |
| Email | lower-case, trim | equality on the normalized value |

**Full tables:** see `references/nickname-and-address-tables.md` for the nickname→formal dictionary,
the USPS street-suffix / directional / unit-designator abbreviations, and the legal-entity suffixes —
the body covers the method; the specific entry to expand is a lookup.

Combine the per-field scores into one match score (a weighted sum, or a simple rule such as "two
strong fields agree"). One shared field is weak evidence: dozens of distinct people share a common
name, so a name-only match over-collapses. Require corroboration from a second discriminating
field (email, phone, address) before calling a pair the same.

### 3. Score → two cutoffs, with a review band between them

Do not use a single yes/no line. Set a HIGH cutoff and a LOW cutoff:

- score ≥ HIGH → auto-link / auto-merge.
- score ≤ LOW → treat as different entities; keep them apart.
- LOW < score < HIGH → **uncertain**: route to a human review queue, do NOT auto-decide.

The band exists because a wrong merge fuses two real customers into one — costly and hard to undo.
When unsure, defer rather than guess.

### 4. Cluster transitively

Matching yields pairs. If A links to B and B links to C, resolve A, B, C into ONE entity even
when A and C were never directly compared or don't look alike. Group connected pairs into clusters
(connected components); each cluster becomes one merged entity.

### 5. Survivorship — merge, never drop

A merge builds ONE surviving "golden" record from a cluster. It is not "keep the first row." For
each field, pick the surviving value by an explicit rule, and retain what you don't pick:

- **Most-complete** — a null loses to a populated value; fill blanks on the survivor from any
  cluster member.
- **Most-recent** — when two members both have a value and disagree, prefer the one with the
  newer `updated_at` / timestamp.
- **Source-priority** — when a trusted source (a verified billing system) disagrees with a weak
  one (self-entered web form), the trusted source wins regardless of recency.
- **Never silently discard.** A value that loses (an old address, a second phone) is kept as
  history / an alternate, not deleted. Record which source each surviving value came from.

State which rule you used per field; when rules conflict, say which took precedence and why.

### 6. Output

Emit, at minimum:

- the merged golden record per cluster;
- which original record ids folded into it (provenance);
- the source of each surviving field value;
- the retained non-winning values;
- the review queue — the uncertain pairs a human must adjudicate.

## Linkage vs merge

Two goals share this machinery. **Merge** collapses duplicates within/across sets into one golden
record (survivorship applies). **Linkage** leaves both systems' rows in place and just records a
correspondence (a crosswalk / foreign-key link) so downstream joins work with no shared column —
here you produce links and a review queue, but you do NOT overwrite either side's fields.

## Edge cases

- **Common-name collision** — two records agreeing only on a frequent full name are NOT a match;
  demand a second corroborating field.
- **Household / shared attributes** — different first names at one address (family members) are
  distinct people; a shared address plus surname alone is not sufficient to merge.
- **Asymmetric completeness** — one rich record and one sparse record can still be the same
  entity; missing fields are absence of evidence, not evidence of difference.
- **Conflicting timestamps missing** — with no reliable recency signal, fall back to source
  priority, then to the review queue; do not pick arbitrarily.
- **One-sided values** — an email on only one member of a cluster is kept, not dropped as "not
  confirmed by the other."

## Evaluation criteria

A good execution of this skill should:

- [ ] Bucket candidates to avoid an all-pairs comparison on large inputs
- [ ] Normalize fields and use similarity fit to each field, not exact-string equality
- [ ] Require a second corroborating field rather than matching on a common name alone
- [ ] Gate merges on a high cutoff and send the middle band to human review
- [ ] Cluster transitive links (A–B, B–C) into one entity
- [ ] Build the survivor by an explicit survivorship rule and keep non-winning values
- [ ] Record provenance: which rows merged and where each value came from