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