Entity resolution

How are two records decided to be one person without a shared key, and what does the threshold really set?

Entity resolution (ER) is identity where no shared key exists. The same person appears as "Jon Smith, 12 Oak St" in one system and "J. Smyth, 12 Oak Street" in another. There are two regimes.

Deterministic: normalize the attributes (lower-case, strip punctuation, expand abbreviations), apply match rules, and take the transitive closure of the matched pairs to form clusters. (If A matches B and B matches C, put A, B and C in one cluster.)

Probabilistic: score each candidate pair using agreement and disagreement weights per field (a match on surname is evidence for; a mismatch on birth date is evidence against), then cut the scores with thresholds into match / possible match / non-match, then cluster the results.

Four mechanical facts

1. The pair space is O(n²), so blocking is mandatory. Worked example. With n = 1,000,000 records there are n(n−1)/2 ≈ 5 × 10¹¹ pairs (500 billion). You cannot score them. Blocking generates candidate pairs from a cheap key and scores only pairs inside the same block. Cheap keys include a phonetic surname code (Soundex gives "Smith" and "Smyth" the same code, S530), a postcode, or a MinHash band (a short fingerprint of a record, chosen so that similar records often share a fingerprint band). If 10,000 postcode blocks hold about 100 records each, you score 10,000 × (100 × 99 / 2) = 10,000 × 4,950 = 49.5 million pairs: about 10,000 times fewer. Recall lost at blocking is unrecoverable downstream: a true pair that is never in the same block is never scored. This is the same machinery as locality-sensitive hashing (LSH, a method that puts similar items into the same bucket) and MinHash from approximate-search methods: turning a quadratic comparison into a linear candidate scan.

2. Pairwise scoring is not transitive. A matches B. B matches C. A does not match C. Any clustering rule resolves this in one of two ways: over-merging (transitive closure puts A and C together) or over-splitting (requiring every pair to agree separates B). Both are wrong, in different directions. There is no third option.

3. The threshold is an asymmetric business risk, not a tuning parameter. If you over-merge two real people, you merge their data. That is a privacy incident (one person sees another's orders), not just a data-quality defect. If you over-split one person, every customer count is inflated. The threshold sets the exchange rate between those two harms, and nothing in the data determines it.

4. The resolved identity needs its own surrogate key and its own version history. Clusters change as evidence arrives: merges and splits. Each change retroactively alters every historical aggregate computed by entity. That is a restatement event, with the same shape as a late-arriving SCD correction. It is the part most implementations discover after the first merge.

What ER assumes and cannot verify

It assumes that its match features are independent signals. They are not. Name and address are strongly correlated (families and shared households share both). So naive score combination overstates confidence exactly where errors are most likely: two relatives at one address look like a stronger match than they are. This is the independence law again, and here it biases in the dangerous direction (toward false merges).

Case, resolvedConstructed case · Nadia at Fernwood Outfitters

Three numbers for active customers

Three teams reported 44,010, 38,950 and 41,200 active customers for September from the same tables, and nobody could say how many different people the customers were. Where it started: Meaning and identity →

Three numbers for "active customers". She writes one definition with the finance team as owner: paid, not refunded, not a test account, in the shop's time zone, counted by resolved person. She puts it in a metrics file in the repository. When marketing wants a different definition ("anyone who visited and ordered"), it gets a different name, engaged_customers. The three numbers still differ, but now each has a name and an owner, and every change shows up in a pull request. The semantic layer also refuses to sum gift_card_balance over months.

How many different people? She builds entity resolution with blocking on postcode plus a phonetic surname code, scores pairs with agreement weights, and sets a conservative merge threshold: it is better to count a few people twice than to mix two people's data. She stores the result as a person_key with its own version history. After the first run, the active-customer count falls from 38,950 (by account) to 36,400 people. She tells the CEO that this number depends on the threshold, and it will restate if the threshold changes. (These numbers are illustrations.)