Entity Resolution & Record Linkage

This lesson is about deciding which records describe the same real-world thing, when there is no shared key and the data is typed by humans. The text-similarity ingredients (MinHash and LSH derivations, TF-IDF) are in Classical NLP & Text Classification and are only reused here; dense-vector matching is Embeddings & Representation Learning and Vector Databases and Hybrid Search; the scoring vocabulary (precision, recall, F1) is Model Evaluation Metrics; generic clustering is Clustering & Dimensionality Reduction. Here the subject is the whole pipeline around them.

Interviews on this topic follow a pattern: "dedupe 100 million customers", "link two company databases", "how do you know you did not miss duplicates?" Each reduces to a few decisions: how to avoid comparing all pairs, how to score a pair, how to turn pair scores into entities, and how to measure all three when ground truth is partial. Every number marked "measured" comes from scripts in /tmp/er/ run on a synthetic customer table described in the worked example (fixed seed, 110,538 records, 45,000 entities); they show mechanisms and orders of magnitude, not benchmarks, and anything not run is labelled as an assumption or as recalled from memory.

Problem Variants

The same toolkit serves four situations that differ in what is known in advance:

• Deduplication (one source). Find repeated entities inside one table: customers registered twice, patients with two charts. All records are comparable with all others; the output is a partition of one table.
• Cross-source linkage (two or more sources). Match table A to table B (a CRM to a billing system). Pairs are only formed across sources, and if each source is already clean you may impose a one-to-one constraint, which turns matching into an assignment problem and removes many errors for free.
• Identity resolution. Many sources (web logins, devices, emails, loyalty cards) are merged into a persistent person or household ID. Records are sparse, identifiers are shared (a family laptop), and the output must stay stable over time because downstream systems store the ID.
• Company and product matching. Names are long and noisy, and hierarchy matters: legal entity versus brand versus parent group, product variants that differ in one attribute (storage size, colour). Strong identifiers (tax ID, GTIN, domain) exist for some records and are absent for the rest.

Two cautions apply to all four. First, "same entity" is a business definition, not a fact in the data: should a mother and daughter at one address, or two branches of one company, be merged? The synthetic table below includes father-and-son records that share a first name and an address, and twins that share a last name, address and birth date, precisely because a model cannot know which side of the line you want. Second, the base rate is tiny: in the example 201,585 true pairs sit among 6,109,269,453 possible pairs, a match rate of 3.3 \times 10^{-5}, so a classifier that looks excellent on balanced data can be useless on raw pairs.