Entity Deduplication From 50 Million Pairs to Thousands

Two supplier records both score 91 on embedding similarity. One pair is the same vendor, entered by two sourcing teams a year apart. The other is "Acme Industrial Supply Co." registered in Ohio and "Acme Industrial Supply Co." registered in Singapore, two legal entities your finance team needs kept apart. Identical score, opposite correct decision. That gap is the entire problem with putting embeddings first.

This walkthrough builds an entity deduplication pipeline on a concrete 10,000-row supplier list and treats embedding similarity as what it actually is: an adjudicator for the narrow band of pairs that cheap deterministic stages cannot settle. Normalization, key hashing, and blocking resolve most duplicates and collapse roughly 50 million naive pairwise comparisons into thousands of candidates at negligible cost. The real engineering lives in the decision layer around the scores, namely how to calibrate a threshold with no labeled data, how to account for yield and cost at every stage, and how to write merge rules that survive an audit.

What a Similarity Score of 91 Actually Tells You

Less than you need. A cosine similarity between two sentence embeddings is a relative geometric quantity, not a probability. It has no units, no guaranteed relationship to match likelihood, and no stable meaning across deployments. The same pair of records can land at 0.86 under one embedding model and 0.79 under another; add or remove ten thousand rows of corpus context and the scores shift again, because the vectors themselves change. So a fixed cutoff like 0.85, the number most tutorials hand you, does not transfer between projects, models, or corpora. It barely transfers between two runs with different source systems.

This is not a new problem dressed in new vocabulary. The record linkage field has dealt with "is this agreement pattern a match?" since Fellegi and Sunter's 1969 theory, which computed match probabilities from agreement on comparison variables rather than trusting any single raw similarity figure. Modern embedding models are excellent at capturing semantic equivalence that edit distance misses, and the Sentence-BERT paper shows why: sentence encoders map paraphrases near each other even when surface strings diverge. But semantic closeness of text is still not proof of entity identity. The score ranks evidence. Your pipeline decides.

The Case, a 10,000-Row Supplier List

One list, carried end to end. It has the mess you would expect from a supplier master fed by ERP exports, a CRM, and two scanned vendor onboarding forms:

• Case and whitespace noise: "acme industrial supply ", "ACME Industrial Supply"
• Abbreviations: "Intl" versus "International", "Mfg" versus "Manufacturing"
• Legal suffix variants: "Ltd", "Limited", "LLC", "GmbH", and their combinations
• Input and OCR errors: "Sunrise Logistcs" from a scanned W-9
• One brand spanning multiple legal entities: a parent and its country subsidiaries sharing a normalized name

The last pattern is the trap, and we will come back to it.

This same pipeline shape serves the builder contexts you probably actually sit in: deduplicating a RAG corpus before indexing so retrieval does not return three paraphrased variants of one policy, cleaning CRM accounts, deduplicating agent memory writes, and reconciling conflicting outputs from two tools. For a far deeper treatment of matching theory than this walkthrough can give, Christen's Data Matching book is the standard reference.

The Naive Baseline, 49,995,000 Comparisons

Before any scoring, compare every record to every other. Ten thousand rows produce 10,000 × 9,999 ÷ 2, which is 49,995,000 unique pairs. Roughly 50 million candidate pairs, and you have not yet scored a single one. At even one millisecond per comparison, that is about 14 hours of pure scoring; with embedding inference on each pair, the bill stops being defensible long before the quality argument starts.

So every stage that follows is judged against one accounting frame: how many comparisons it processes, how many new true matches it finds, what it costs at the margin, and what it costs per additional true match. Keep that frame in your head. It is the artifact that settles every architecture argument later.

Stage 1, Normalization and Key Hashing

Before any matching, normalize company names for deduplication with a deterministic rule set:

1. Unicode normalization (NFC or NFKC) so accented and composed forms stop looking different
2. Case folding and whitespace collapsing
3. Legal suffix stripping against a list: "Ltd", "Limited", "LLC", "Inc", "GmbH", "Pte Ltd", and common combinations
4. Abbreviation expansion from a domain dictionary: "Intl" to "International", "Mfg" to "Manufacturing"

Then hash the normalized string into a key and group records by exact key equality. This is a hash lookup, not a pairwise scan, so the cost is effectively zero. In many supplier lists this single stage captures the largest share of true duplicates, all the whitespace, case, and suffix variants, though the exact share is corpus-dependent: a clean single-source list yields less, a merged ERP-plus-scan mess yields more. Do not promise a percentage before you run it. Report it after.

What hashing cannot touch is anything requiring fuzzy judgment: "Sunrise Logistcs" hashes differently from "Sunrise Logistics", and no suffix list fixes an OCR dropout. That residue moves to Stage 2.

Stage 2, Blocking and Candidate Generation

Blocking to reduce pairwise comparisons in entity matching works by assigning every record one or more block keys and comparing only within blocks. Practical keys for a supplier list: the normalized name's first few characters, a sorted-token fingerprint (sort the normalized tokens alphabetically, so word order stops mattering), and geography such as country or region code. A pair whose records share no block key is never compared, which is exactly the orders-of-magnitude reduction you need, and also the known recall risk: a record with a typo in the field driving the block key can fall into a block where its twin is absent. Use several overlapping keys so a pair only needs to collide once.

On the worked list, three block keys typically collapse roughly 50 million possible pairs into thousands of candidate pairs, a reduction of four orders of magnitude, at the cost of a group-by. Tools built for exactly this: Splink's blocking rules guide for probabilistic linkage at scale, and the recordlinkage indexing docs for a lighter Python path. Inside blocks, cheap fuzzy scores triage candidates: normalized edit distance, Jaccard over token sets, or, if the data lives in Postgres, the pg_trgm similarity operator, which keeps everything in-database.

Post-blocking, you have three piles: pairs settled by exact or near-exact agreement, pairs clearly disjoint, and an ambiguous band in the middle.

Stage 3, Embedding Similarity for the Ambiguous Band

The ambiguous band is the residual set, pairs like "Sunrise Logistcs Pte" against "Sunrise Logistics Private" where deterministic scores sit in an uninformative middle range and geography is missing or unreliable. Embeddings exist in this pipeline to adjudicate that band and nothing else.

That placement is an economic argument, not a preference. Embedding scoring is the most expensive comparison per pair in the pipeline, and restricted to the band it also has the highest cost per additional true match, because the easy matches are already gone. Point it at all 50 million pairs and you pay the top rate for the bottom-value work. Point it at the band and every inference dollar buys a genuinely contested decision.

The output, remember, is still an uncalibrated number. Stage 3 hands the pipeline scores, not verdicts. Which brings us to the part tutorials skip.

How to Calibrate an Entity Deduplication Threshold Without Labels

You have no labeled pairs and no annotation budget. You can still pick a defensible embedding similarity threshold in four moves.

1. Plot the score distribution. Score all ambiguous-band pairs and histogram the results. Well-behaved corpora often show two modes, a cluster of high scores (probable matches) and a cluster of lower scores (probable non-matches), with a valley between. The valley, not the modes, is your candidate cutoff.
2. Hand-check a seeded set. Sample roughly 20 to 50 pairs, weighted toward the valley and the flanks on both sides. Label them yourself. This is an hour of work, and it converts "0.85 feels right" into "the valley at 0.87 held up on 40 checked pairs."
3. Set the cutoff precision-first. Assume a false merge costs more than a missed duplicate, which is true in supplier masters (merged vendor records corrupt payment history and audit trails) and in most entity resolution work. Place the threshold above the valley, on the match-mode side, so borderline pairs route to human review instead of auto-merge.
4. Sanity-check across corpus slices. Split by source system or region and confirm the distribution and cutoff roughly hold. A threshold that only works on one slice is telling you the slices need different treatment.

If you can label a few hundred pairs interactively instead, dedupe's active learning asks you exactly the pairs it is most uncertain about and learns weights from your answers, combining exact-key shortcuts with a trained classifier. The label-free playbook above is the version for when even that loop is too much process.

The Per-Stage Yield and Cost Ledger

Here is the organizing artifact: one table, one row per stage, updated every run. The numbers below are illustrative of the shape, not a benchmark; your corpus shifts every cell.

Read it as a marginal-yield story. The hash stage resolved the large majority of duplicates for free. Blocking plus cheap fuzzy scoring found hundreds more for cents. The embedding stage, pointed only at the contested band, found the last tranche at the highest per-match cost in the pipeline. That is a stage worth running. Invert the order and the same table shows embeddings burning most of the budget to rediscover what a hash lookup settles instantly, which is why per-stage cost tracking belongs in the data deduplication pipeline from day one, not in a postmortem.

If your ledger shows the embedding stage costing several multiples per match of the trigram stage for a marginal handful of finds, you have a defensible answer to "do we even need embeddings here", with numbers attached.

Failure Modes the Tutorials Skip

Transitive over-merging. You have pairwise decisions, but entities are groups, so you take the transitive closure: records linked into connected components merge together. Now record A matches B, B matches C, and A fails to match C by your own threshold, yet all three collapse into one entity, and chains longer than three can drag in records no single pair decision supports. This is a recognized transitive closure over-merging failure in production entity resolution. Mitigations: cluster with constraints that require some minimum agreement across the group, or cap chains, or route large components to review instead of auto-merging.

Distinct legal entities sharing a normalized name. Stage 1 strips legal suffixes, which is exactly what makes "Acme Industrial Supply Ltd" and "Acme Industrial Supply LLC" collide. But one of those suffixes was carrying real information: jurisdiction. A parent and its subsidiaries, or one brand franchised across countries, can normalize to identical keys and score near-perfect embedding similarity while remaining separate legal counterparties. String identity and embedding similarity can never prove entity identity on their own. This is why blocking keys should include geography and why merges of high-similarity pairs should corroborate against a secondary field, registration number, tax ID, address, or contract entity, before they commit.

Audit requirements. When a compliance reviewer asks why two vendor records were merged in Q3, "cosine similarity was 0.91" is not an answer, and neither is the model version alone. Audit and compliance contexts need an explainable merge record: which stage fired, which rule or threshold, on which field evidence. That means logging the decision path per merge, storing the threshold values in force at decision time, and keeping the merge reversible. A raw score cannot satisfy this; the decision layer around it can.

Build Order, Checklist, and When to Recalibrate

The build order, compressed:

1. Normalize, hash, group. Measure yield and cost.
2. Add two or three overlapping block keys. Measure candidates produced and recall risk taken.
3. Score within blocks with cheap fuzzy metrics. Pile the remainder into an ambiguous band.
4. Embed only the band. Calibrate label-free: distribution valley, 20 to 50 seeded hand-checks, precision-first cutoff.
5. Cluster with anti-chain safeguards, corroborate name-only matches against a secondary field, and log an explainable record for every merge.
6. Keep the ledger current. Every architecture argument ends when someone opens the table.

Recalibrate when the corpus shifts materially (a new source system, a fresh country's data), when you swap embedding models, since scores are model-dependent and the old threshold is void, or when the score distribution's valley visibly moves. Any of those invalidates a cutoff that was only ever calibrated for the world it was measured in.

The takeaway is the one the opening pair of 91s set up. A similarity score is not a fact about two records; it is evidence, uncalibrated and context-dependent, waiting for a decision process. Build the deterministic stages that make evidence cheap, reserve the expensive adjudicator for what it alone can settle, and keep numbers on every stage so you can defend the order to anyone who asks. That is the whole pipeline, and it is the difference between deduplication you trust and deduplication you hope.