Cross-Source Product Matching: A Cascade, Not a Model

Every catalog you don't control disagrees with you about what a product is called. One source lists "Brand A Adult Chicken & Rice 30lb," another lists "Brand A® Adult Chicken/Rice Formula — 13.6kg," and a third has neither name but does have a GTIN. If you're merging two or more of these into one system — a marketplace sync, a price comparison, a recommendation engine that has to know "this" and "that" are the same SKU — the question you're actually answering, over and over, at scale, is: are these two records the same real-world thing? Get it wrong in one direction and you show a customer duplicate listings for an item they already own. Get it wrong in the other direction and you silently merge two products that aren't the same, and now your price history or your nutrient data is contaminated by an item that was never actually equivalent.

This is a well-studied problem — the literature calls it record linkage or entity resolution — and the good news is that it doesn't require a machine-learning pipeline to get most of the way there. What it requires is a cascade: cheap, unambiguous matching first, a cheap-but-fuzzy pass for everything that survives, and an honest admission that some fraction of records shouldn't be auto-matched at all. Below is the version of that cascade we've shipped, plus the normalization work that has to happen before "similar" and "same" mean anything.

§ 01Start with the key you can trust completely

The instinct when facing a matching problem is to reach for fuzzy logic immediately, because that's the interesting part. Resist it. The first tier of any matching cascade should be a deterministic join on the strongest identifier both sources actually populate — a barcode, a GTIN, a manufacturer SKU, an internal ID a previous integration already assigned. This is blocking in the record-linkage sense: partitioning the search space so you only ever compare records that share a candidate key, instead of running an O(n²) comparison of every record against every other record.

Barcode matching on a product catalog is close to free, both computationally and in terms of false-positive risk: two different physical products essentially never share a barcode, so a hit is a hit. In a product data platform we built, that first tier looked like this — collect every populated barcode from the incoming batch, and resolve it against the catalog in a single set-based query rather than one round trip per record:

// tier 1 — exact key match, confidence 1.0, no ambiguity
const incomingBarcodes = incoming
  .flatMap(p => p.variants)
  .map(v => v.barcode?.trim())
  .filter(Boolean);

const hits = await sql`
  SELECT id, barcode FROM products
  WHERE barcode = ANY(${incomingBarcodes})
`;
// only what tier 1 misses is worth spending fuzzy-match cycles on
const unresolved = incoming.filter(p =>
  !p.variants.some(v => hits.has(v.barcode))
);

The discipline worth naming here is that tier 1 isn't just "the fast path" — it's the tier that defines your ground truth. Every accuracy number you report on the fuzzy tier is only meaningful relative to how much of the catalog tier 1 already resolved with certainty. If 70% of records carry a clean barcode, you've turned a hard matching problem into a hard matching problem for the remaining 30% — a much better place to spend a similarity algorithm's budget.

§ 02The messy middle: fuzzy similarity on names and brands

What survives tier 1 is exactly the population where identifiers are missing, inconsistent, or simply weren't in the feed — which in practice is most cross-source catalog work, because plenty of upstream sources never populate a barcode field at all. For this tier, trigram similarity is the right tool for a specific reason: the mismatches you're correcting for are typographic and formatting noise, not semantic ones. "Brand B's Science Formula" vs. "Brand Bs Science Formula," "30lb" folded into the title vs. broken out as a separate field, curly quotes vs. straight ones, a trailing " — Adult Formula" one source includes and the other doesn't. A trigram index breaks each string into overlapping three-character sequences and scores similarity by the overlap between two strings' trigram sets — which is exactly forgiving of that class of noise and exactly unforgiving of the failure mode fuzzy matching is famous for (matching two products that happen to share a lot of words but aren't the same item).

PostgreSQL's pg_trgm extension makes this a query, not a project — trigram-generate at write time via a GIN index, and rank candidates by the built-in similarity() function at read time:

-- tier 2 — fuzzy name match, ranked by trigram similarity
CREATE INDEX idx_products_name_trgm
  ON products USING gin (product_name gin_trgm_ops);

SELECT id, brand_name, product_name,
       similarity(product_name, $1) AS sim
FROM products
WHERE similarity(product_name, $1) > 0.3
ORDER BY sim DESC
LIMIT 1;

Two details make this reliable instead of merely clever. First, the floor — 0.3 in the query above — isn't decoration; below it, trigram overlap stops correlating with "plausibly the same product" and starts returning coincidental matches on common words like "adult" or "chicken." Second, the index makes this cheap enough to run per-record inline in a sync job instead of as an offline batch: a GIN trigram index turns the similarity scan into an index lookup rather than a sequential scan across the whole catalog, which is the difference between this running in a webhook handler and this running in a nightly cron job somebody has to babysit.

§ 03Don't collapse a score into a boolean — tier the confidence

The mistake that turns a good fuzzy-matching system into a slow-motion data-quality incident is treating the similarity score as a yes/no gate at a single threshold. A score is a continuous signal; forcing it through one cutoff throws away the information that should route different records to different handling. The fix is a second dimension on top of the similarity tier: a confidence band that decides whether a match auto-confirms, gets queued for a human, or gets discarded as noise.

• sim > 0.9 — auto-confirm. High enough that manual review is a waste of a person's time; false positives at this band are rare enough to eat as a known error rate rather than gate on.
• 0.3 < sim ≤ 0.9 — route to human review. Plausible, not certain. This is the band where "Chicken & Rice Formula" and "Chicken & Rice Recipe, Small Breed" collide — genuinely different products that share most of their trigrams.
• sim ≤ 0.3 — treat as no match. Below the floor, record it as unmatched and let a separate new-product ingestion path handle it, rather than force a bad merge.

The review queue is not a failure of the pipeline — it's the pipeline's honesty mechanism. A system that auto-confirms every fuzzy hit above some single threshold will always have a silent error rate nobody's watching; a system that stages the ambiguous band in front of a human converts that silent error rate into a visible, shrinking backlog, and the review decisions themselves become labeled data you can use later to tighten the thresholds. In production this tiering runs identically one layer down the schema too — matching raw ingredient or attribute text to a canonical table follows the same cascade (exact match on canonical name, then a known-alias table, then trigram fallback), gated behind a minimum string length so a stray single-character fragment doesn't fuzzy-match onto something absurd.

§ 04Matching isn't real until the units agree

A name-and-brand match tells you two records probably describe the same product line. It says nothing about whether the specific unit for sale is comparable — and across catalogs, it usually isn't. One source lists price per 30lb bag, another per 13.6kg bag, a third sells the same formula in a 3.5lb trial size that will always look artificially expensive next to the bulk bag if you compare list price directly. Loose or weighed goods make this worse: produce, deli, bulk bins — priced per kilogram or per unit at checkout, with no fixed package size to match on at all.

The fix is the same in both cases: normalize every matched pair to a common per-unit basis before you compare anything, and treat "same product, different package size" as a first-class case rather than a matching failure. Concretely:

1. Parse size out of free text where it isn't a structured field — "30lb," "13.6 kg," "3.5 lb" all need to resolve to one canonical unit (grams, say) before two listings are comparable at all.
2. Convert every price to a per-unit rate — price ÷ normalized weight — so a 30lb bag and a 13.6kg bag of the functionally identical formula land on the same axis instead of looking like unrelated numbers.
3. For loose or weighed goods, match on category and package-size tier rather than an exact size string, then price by the source's own per-kilogram rate multiplied by the target weight — because there's no barcode and often no fixed package size to key off at all, only a category and a rate.
4. Flag, don't silently accept, cross-brand substitutions — when a size or brand doesn't exist in one catalog and you fall back to the nearest mainstream equivalent by category and size, that's a lower-confidence match than an exact brand-and-size hit, and it should carry a visibly lower confidence score, not the same 1.0 a barcode gets.

Skip this step and a downstream matching or comparison layer built on top will confidently produce nonsense: a "cheaper" flag on a product that's actually more expensive per unit, or a duplicate-detection pass that misses two records because 30lb and 13.6kg never string-compared as equal in the first place. The two problems — is this the same product, and is this the same unit of it — are separable, and treating them as one step is where a lot of otherwise-careful matching pipelines quietly go wrong.

Deciding two records describe the same product and deciding they're priced on the same basis are two different questions. Answer them in that order.

§ 05What this cascade is worth, and where it stops working

None of the four tiers above is individually sophisticated — that's the point. A deterministic key match, an indexed trigram score, a confidence-banded review queue, and a per-unit normalization pass are all things you can build and reason about with plain SQL and a bit of parsing logic, no model in the loop. That matters operationally: the failure modes are legible. When a match is wrong, you can point at which tier produced it and why, instead of debugging an opaque similarity model. It's the same reason a product data platform we built leaned on this exact cascade for its catalog-sync layer rather than reaching for embeddings on day one.

Where it stops being enough is the genuinely semantic case — two descriptions of the same product that don't share vocabulary at all, translated listings, or a source that describes products by use case rather than name. Trigram similarity is measuring character overlap, not meaning, so it has nothing to offer once the mismatch is semantic rather than typographic; that's embedding-similarity territory, a different tool with a different cost profile, worth reaching for only once the cheap tiers have already done their job on the majority of the catalog. Reach for it too early — before exhausting the deterministic and trigram tiers — and you're spending model calls on records a barcode lookup would have resolved for free.

— End of essay. Reconciling catalogs across sources that don't agree on identifiers? Start a project →