How to deduplicate products across supplier catalogs

To deduplicate products in a catalog, match in three passes: first on hard keys (a GTIN padded to 14 digits, or normalized manufacturer plus normalized MPN), then on fuzzy and attribute similarity for records with no clean key, and finally send the ambiguous middle band to a human reviewer. Merge each confirmed cluster into one record using written rules for which source wins each attribute, and keep every retired SKU and supplier part number as a cross-reference so orders, search, and integrations keep resolving.

Most of the risk sits in normalization and merging, not in the matching algorithm.

Why one part ends up as three SKUs

A distributor onboards the same 3/8-16 x 1 inch hex cap screw from a manufacturer feed, a master distributor, and a legacy ERP import. For illustration, the three rows might read:

• HCS-38161-Z, brand ACME FASTENER, description HEX CAP SCREW 3/8-16X1 ZP
• HCS38161Z, brand Acme Fastener Co., description Hex Cap Screw, Zinc, 3/8 in-16 x 1 in
• ACM-HCS38161Z-100, brand ACME, no description, pack of 100

Same part, three MPN spellings, three brand spellings, one packaging variant. None collide on an exact string match, so customers see two prices, inventory splits, and every attribute fix gets made twice.

Deduplicating duplicate SKUs on GTIN and manufacturer part number

Start with the keys that are designed to be unique, because a deterministic match on a clean key is more reliable than a similarity score.

GTIN first. GTINs arrive as 8, 12, 13, or 14 digits. Pad every one to 14 with leading zeros before comparing, so a UPC-A and the same number stored as a GTIN-14 collide (GTIN formats and padding). Validate the mod-10 check digit and quarantine anything that fails rather than matching on it.

Then respect packaging levels. Under the GS1 GTIN Management Standard, a unique GTIN is assigned at every existing level of the packaging hierarchy above the base unit, and changing the number of items in a case requires a new GTIN (GS1 GTIN Management Standard). So a case GTIN and an each GTIN for the same screw are not duplicates. They are a pack relationship, and merging them corrupts pricing and unit-of-measure conversions.

Manufacturer plus MPN second. An MPN is only unique within a manufacturer, so the key is the pair. Build a brand alias table (ACME, ACME FASTENER, Acme Fastener Co. all resolve to one manufacturer ID) before you touch part numbers. Then normalize the MPN.

MPN normalization pitfalls

This is where most dedupe projects either miss matches or, worse, merge different products. The safe pattern is to keep the raw MPN untouched, generate a separate mpn_match_key, and make every stripping rule specific to a manufacturer.

Raw MPN pattern	What to do with it	The trap
HCS-38161-Z vs HCS38161Z vs HCS 38161 Z	Strip dashes, spaces, dots, slashes; uppercase	A manufacturer may use the dash meaningfully; check its catalog before stripping
ACM-HCS38161Z	Strip a known distributor line-code prefix	Only strip prefixes on your confirmed list; a 3-letter prefix can be part of the real MPN
HCS38161Z-100, HCS38161Z/BX	Move pack suffixes to a pack_qty field, link as a pack variant	Suffixes like -BLK, -120V, -SS are real differences: finish, voltage, material
0HCS38161Z, letter O vs zero	Normalize only where the manufacturer's own catalog shows the variant is a typo	Blind O-to-0 swaps create false merges across product lines

For each manufacturer, keep a small suffix dictionary: which trailing codes mean packaging (strip and record), and which mean a different product (never strip).

Product matching with entity resolution and record linkage tools

Rows that survive pass one with no confident key match go to fuzzy matching. The general technique is called entity resolution or record linkage: Splink's documentation describes it as using the information within records to decide whether they refer to the same entity, within one dataset (deduplication) or across datasets (linkage) (Splink: why record linkage).

The mechanics are the same whichever tool you pick:

1. Block. Only compare records that share something cheap, such as manufacturer ID plus category.
2. Compare attributes. Normalized MPN by edit distance, title by token similarity, and specs (thread size, length, voltage, finish) by exact match after unit normalization.
3. Score and cluster. Combine the comparisons into a match score, then group connected pairs into clusters.

Two tools come up most for this:

• Splink is an open-source Python package for probabilistic record linkage based on the Fellegi-Sunter model, built for datasets that lack unique identifiers. Its README says it can link a million records on a laptop in around a minute, runs on DuckDB by default, and offers Spark and PostgreSQL backends for larger jobs (Splink on GitHub). Use link_type="dedupe_only" for one catalog, or link_and_dedupe to match supplier feeds against each other and themselves at once (Splink link types). The Splink tutorial walks through blocking rules, parameter estimation, prediction, and evaluation.
• AWS Entity Resolution offers rule-based matching as a waterfall of rules you configure, and assigns a Match ID plus the rule that produced each match. The Simple rule type supports exact matching only; the Advanced type adds fuzzy functions (AWS rule-based matching). In Advanced rules, a fuzzy function (Cosine, Levenshtein, or Soundex) must be combined with an exact function using AND, and you can create up to 25 rules (AWS Advanced rule type).

One caution for product data: both tools' worked examples lean on person records. AWS's built-in normalization, for instance, covers grouped name, address, and phone fields (AWS Advanced rule type). Neither tool's built-in normalization knows that 3/8-16 and 0.375-16 UNC are the same thread. You bring the product normalization; the tool brings the matching engine. A rule like exact manufacturer ID AND Levenshtein on mpn_match_key within 1 fits the AWS constraint, but it only helps after pass one has cleaned both fields.

Human review of the ambiguous band

Set two thresholds. Above the upper one, auto-merge. Below the lower one, auto-reject. Everything between goes to a person.

For illustration: 40,000 candidate pairs with 5% landing in the band is 2,000 pairs. At 30 seconds each, that is about 17 hours of review, which is manageable if the screen is built for it. Show the two records side by side with the deciding fields highlighted (MPN key, pack quantity, finish, the spec that differs), not two full product pages.

Treat every reviewer decision as data. A rejected pair that differed only on -SS tells you to add -SS to that manufacturer's never-strip list, which shrinks the band on the next feed.

Merge rules: which source wins

A confirmed cluster still holds conflicting values: one source says zinc plated, another says plain, the ERP says nothing. Decide survivorship per attribute, in writing, before the first merge. One defensible order is manufacturer spec sheet, then manufacturer website, then authorized distributor feed, then legacy ERP text, with recency as a tiebreaker. The result is the golden record for that product. Our post on attribute trust hierarchies and conflicts covers how to rank sources when they disagree.

The rule that matters most: when two high-trust sources disagree, flag the conflict instead of letting the last write win.

Keep cross-references after a merge

Never hard-delete the losing SKUs. Mark them inactive with a superseded_by pointer to the survivor, and carry over:

• every supplier part number and distributor line code, so inbound feeds keep matching
• customer part numbers and contract pricing keys
• every GTIN in the cluster, mapped to its packaging level
• old product URLs, redirected to the surviving page

Persist the cluster or Match ID from your matching run alongside your own survivor ID. That mapping is what lets you re-run matching next quarter without re-merging or splitting what you already decided, and it is a core part of any master data management setup.

Who keeps it deduplicated

Dedupe is not finished after the first cleanup. Every new supplier feed reintroduces the same spelling problems, so the alias table, suffix dictionaries, and review queue need an owner. Anglera works alongside whatever PIM, ERP, or MDM already holds the catalog, or from a flat CSV export: it extracts attributes from spec sheets and manufacturer sites, normalizes keys like MPN and pack quantity, quality-scores each value, and flags conflicts for review rather than inventing a winner. You can see the flow on how Anglera works.

Matching is only as good as the attributes underneath it. Your PIM stores the data; Anglera does the extraction, normalization, and upkeep that keep duplicates from coming back, with typical implementation in 30 days or less.