# When Your Supplier List Breaks: A Practical Guide to Finding Duplicates in Noisy Data
## The Problem Nobody Wants to Talk About
Every organization that maintains a list of external partners — vendors, publishers, affiliates, or suppliers — eventually faces the same quiet crisis: the list grows dirty. The same company shows up under multiple spellings, with added subdomains, country-code variations, or random tracking strings bolted onto the URL. Each duplicate means you might pay the same invoice twice, or worse, lose the pricing history you negotiated over time because nobody can search for the name you’ve misspelled.
This is not a glamorous engineering problem, but it is an expensive one. In a dataset of roughly ten thousand records, the cost of a bad merge — or a missed duplicate — compounds quietly across finance, procurement, and reporting.
## Building a Realistic Test Set From Scratch
Before tackling the cleanup, you need something to measure against. Since real vendor lists are confidential, I constructed a synthetic one that mimics the kinds of corruption most organizations experience. Starting from seven thousand fictional publisher brands, I introduced several layers of messiness:
– **Surface-level variants**: schemes like `http://`, `https://`, the `www` prefix, random capitalization, and tracking parameters appended to URLs.
– **Subdomain rows**: entries like `blog.` and `m.` prepended to domains, creating entries that belong to the same underlying organization but look superficially different.
– **Country-domain siblings**: the same brand name registered under different country extensions, such as `.com` sitting next to `.de` or `.co.uk`.
– **Typos**: deliberate one-character entry errors simulating how real data gets created by hand.
– **Near-miss traps**: domain names that differ by a single edit but belong to entirely unrelated vendors, because any honest benchmark must account for false positives.
The final synthetic set contained 11,531 rows describing 7,180 distinct vendors. The entire generation process required less than fifty lines of code combining word lists with deliberate corruption.
## Stage One: Stripping the Noise
The first cleanup step is deliberately simple: lowercase everything, remove the URL scheme, discard paths and query strings, and strip any leading `www.` prefix. This normalization reduced the list from 11,531 rows to 9,080 unique hostnames in under a second. More than two thousand obvious duplicates disappeared without any fuzzy matching, machine learning, or threshold tuning. The lesson here is that the most impactful work often looks boring.
## Stage Two: Understanding Domain Hierarchy
The next challenge is subdomain flattening. When you see `blog.solartravelmag.com`, the registered organization is `solartravelmag.com`. But the reverse is not true for country-code domains like `solartravelmag.co.uk`, where the registered domain is the full `solartravelmag.co.uk` — you cannot simply split on dots.
Using a library that wraps the maintained catalog of valid public suffixes, I collapsed every hostname to its registered domain form. This eliminated another 873 entries, bringing the working set down to 8,207 registered domains. Combined, the two deterministic stages removed roughly three thousand duplicate rows, representing 76% of the total duplicate problem.
The remaining hard cases were entry typos and country-domain siblings — the situations where two rows describe the same vendor but look genuinely different to a naive eye.
## The Candidate Generation Step
With 8,207 domains, the total number of possible pairwise comparisons is approximately 33.7 million. For a dataset this size, computing all pairwise similarity scores directly is fast — under two seconds using modern fuzzy matching libraries, producing a matrix of roughly 67 megabytes.
However, as datasets grow to a hundred thousand entries, that same approach becomes impractical. More importantly, a scored similarity matrix is not itself a to-do list. What you actually want is a manageable set of candidate pairs that can be reviewed, ranked, and acted on. This is where blocking helps.
I constructed candidate pairs by grouping domains on two keys: the first two characters of the label (the part before the suffix), and the last three characters. The two-character prefix alone captured 93% of true duplicates. Adding the three-character suffix key pushed that to 99.8%, while growing the candidate pool from roughly 865,000 pairs to about 1.7 million. The slight increase in computation time was negligible, and only two genuine duplicate pairs escaped both filters.
## Why No Single Threshold Is Safe
Here is where most tutorials stop and where the real operational difficulty begins. After scoring all candidate pairs, I split them by ground truth and examined the distribution.
Scores below 80 contain zero true duplicates. Scores above 97 are almost entirely clean, except for one critical category: country-domain siblings. The scoring function is certain that `brand.com` and `brand.de` are identical strings — and they are. But whether those two domains belong to the same organization is a business question, not a text question. The string match gives you no information about corporate ownership.
The problematic region sits between scores of 88 and 97, where true duplicates and unrelated vendors overlap heavily. Running blind auto-merging at a threshold of 85 yields a precision of roughly 35%, meaning roughly two out of every three merges would be incorrect. At 90, precision improves to about 76%, but one wrong merge in four is still unacceptable. At 95, precision climbs above 89% but recall drops below 74%. Only at 98 does precision reach 100%, but recall collapses to under half — and every remaining pair at that level is a country-domain sibling requiring human judgment anyway.
## The Review Queue as the Real Output
Once no threshold is safe for automatic merging, the score’s purpose shifts entirely. It becomes a tool for prioritizing human review. The score range between 88 and 99 contains roughly 1,350 pairs, of which about 565 are genuine duplicates. Adding the 551 country-domain siblings that require manual decision brings the total review queue to just under two thousand pairs.
At a pace of six pairs per minute, that is just over five hours of work — a single working day to resolve what might represent a decade of accumulated vendor mess. Widening the range to include scores from 85 adds several more hours and recovers a handful of additional true duplicates, but the diminishing returns are clear. Someone with budget authority can now choose exactly how many hours to invest and understand precisely what each additional hour yields.
Two operational practices made this review day affordable. First, sorting the queue by the monetary value of the placements involved ensures the most expensive relationships get reviewed first. Second, every merge is kept reversible: both original rows are preserved, a merge log is written, and no record is ever overwritten. A human review loop loses most of its value if the corrections it produces are permanent mistakes.
## What Generalizes Beyond Publisher Lists
The patterns in this exercise apply broadly. Any entity list — vendor names, customer accounts, affiliate partners, product SKUs from different marketplaces — fails in roughly the same shapes. Surface-level noise that normalization eliminates, hierarchical relationships that canonicalization collapses, and a residual set of near-matches where the text alone cannot decide the answer.
When your data contains more than just a domain or a name, spend that extra information in the review interface rather than inside the scoring function. A shared contact email or a bank detail can resolve an ambiguous pair in seconds, but folding it into a composite similarity score only pushes the ambiguity somewhere harder to detect. And if your entities are web domains, respecting the public suffix list is not optional — splitting on dots is how you accidentally treat a country-code second-level domain as if it were a top-level domain.
## Conclusion
The pipeline that cleaned the noisy list is unglamorous, and that is precisely its strength. Normalize aggressively, collapse each hostname to its registered domain, use blocking to keep the candidate set manageable, score everything once, and route by a simple rule set: exact matches are already resolved, obvious non-matches are ignored, and the ambiguous middle becomes a review queue a person can finish in a single day. On short strings like domain names, a similarity score is evidence for a human to weigh, not a verdict to act on automatically. Rank the queue by business impact, size it to the hours you have, keep every merge reversible, and let the deterministic stages earn their credit.
## Frequently Asked Questions
**What is entity resolution, and why does it matter?**
Entity resolution is the process of determining whether two records in a dataset refer to the same real-world entity. It matters because duplicate records in a vendor or partner list lead to double payments, fragmented pricing histories, and inaccurate reporting. Without resolution, organizations cannot reliably track costs or maintain clean business relationships.
**Why not just use a machine learning model to classify duplicates?**
On small, well-structured datasets like domain names, deterministic normalization and simple string comparison are faster, cheaper, and more transparent than training a model. The hard part of entity resolution is rarely the string comparison itself — it is deciding what to do with the results when the text match is ambiguous. A model trained on the boundary cases would need labeled data for every edge case, and those edge cases are exactly where business context matters more than text similarity.
**How do I choose a blocking strategy for my own data?**
The right blocking strategy depends on the length and structure of your keys. Short keys like domain names benefit from prefix and suffix blocking. Longer strings like full addresses or company names might use token-based blocking, phonetic hashing, or locality-sensitive hashing. The critical measurement is recall: what percentage of true duplicates does your blocking strategy actually surface? You should estimate this on a labeled sample before relying on the approach at scale.
**What does precision and recall mean in the context of duplicate detection?**
Precision answers: when the system says two records are the same, how often is it right? Recall answers: of all the true duplicate pairs that exist, how many did the system actually find? In practice, raising the similarity threshold improves precision at the cost of recall, and vice versa. The key insight from this analysis is that on these types of data, there is no threshold where both metrics are acceptable enough for automatic action.
**How do country-domain siblings cause problems?**
The same brand name can be registered under different country extensions — `brand.com` and `brand.de`, for example. A string similarity score will say these are a near-perfect match, but they may belong to entirely different companies that happened to register the same word. Without business-level knowledge about ownership, no text-based method can distinguish a legitimate subsidiary from a coincidental name collision. These pairs must always go to a human reviewer.
**What tools are needed to implement this kind of pipeline?**
The core components are straightforward. A scripting language like Python for data manipulation, a fuzzy matching library for similarity scoring, and a library that understands domain name structure for normalization. The entire pipeline described here runs on a single cloud compute instance and completes in minutes. The bottleneck is never hardware — it is designing the right decision rules for the ambiguous middle.
**How do I maintain a clean list once it is clean?**
Prevention is easier than cure. Enforce normalization at the point of data entry wherever possible. Require canonical domain forms in any system that ingests vendor data. Schedule periodic reruns of the detection pipeline against new entries, and keep every merge reversible so that errors can be unwound without data loss. A clean list stays clean through consistent entry standards and regular maintenance, not through heroic one-time cleanup efforts.
Thank you for reading
