Operational recipe for expense-history dedupe & vendor canonicalization: keys, scoring, SQL and validation tests

Most dedupe projects die in the same spot: someone exports three years of expense history, eyeballs a few hundred rows, writes a GROUP BY vendor_name, amount query, and then discovers that "Amazon," "AMZN Mktp US," and "Amazon Web Services" are three different vendors to the database — but the first two are the same real-world merchant and the third is genuinely separate. Then they find a refund that looks like a duplicate charge, a multi-currency invoice that matches on amount only by coincidence, and a CSV re-import that silently doubled 1,400 rows.

This is the engineering brief for getting past that. If you've already read the prioritization side of this problem in this prioritized cleanup recipe for SMBs, this is the implementation layer underneath it: the actual keys, the scoring math, the SQL, and the validation tests you run before anything touches your production ledger.

Scope is deliberately narrow. This covers historical expense deduplication and vendor canonicalization for a one-time (or occasional) import, run by a small data or IT team. Not live AP matching, not approval flows — just getting dirty history into a shape automation can trust.

Start with the three dedupe layers (they are not the same problem)

The single biggest mistake engineers make is treating "duplicate rows" and "duplicate vendors" as one job. They have different keys, different failure modes, and different blast radius if you get them wrong.

There are really three layers, and you solve them in order:

1. 1
   Exact transaction duplicates — the same real-world payment imported twice. Deterministic. High confidence. Safe to collapse automatically.
2. 2
   Near-duplicate transactions — the same spend recorded through two systems (card feed + manually entered receipt), so fields differ slightly. Probabilistic. Needs scoring.
3. 3
   Vendor duplication — many spellings of one merchant. Entity resolution, not row dedup. Feeds everything above.

If you try to fuzzy-match everything at once, you'll merge a legitimate recurring $49.00 monthly charge into a single row and delete eleven months of history. Do the deterministic layer first, lock it, then move up.

Layer 1: Deterministic keys and tie-break rules

The deterministic layer should catch the overwhelming majority of real duplicates in a historical import — usually re-imported files and double-synced feeds. These don't need machine learning. They need a good composite key and a clear rule for which row survives.

Keys that actually hold up

A note on attachment_hash: hash the raw bytes, not extracted text. Two scans of the same receipt produce different OCR text but, if it's a re-uploaded file, identical bytes. If your source re-encodes images on upload, fall back to a perceptual hash and demote the match to the scoring layer.

Tie-break rules (which row wins)

1. 1
   Row with a non-null banktxnid beats one without.
2. 2
   Row with an attachment beats one without.
3. 3
   Earlier created_at wins (the original entry, not the re-import).
4. 4
   If still tied, lowest sourcerowid wins — arbitrary but deterministic, which is the whole point.

The rule that matters: tie-breaks must be fully ordered and reproducible. If two engineers run the dedupe on the same data and get different survivors, your audit trail is worthless. Every rule above resolves to a single winner.

Normalization: the step everyone underinvests in

Vendor matching is only as good as your normalization. vendornorm is the field that makes vendornorm + invoice_number + amount work, so it deserves real attention.

vendornorm = LOWER(rawvendor) vendornorm = regexpreplace(vendornorm, '\\s+', ' ') -- collapse whitespace vendornorm = trim(vendornorm) -- strip common corporate suffixes vendornorm = regexpreplace(vendornorm, '\\b(inc|llc|ltd|limited|corp|co|gmbh|sa|plc|pty)\\b\\.?', '') -- strip payment-processor noise vendornorm = regexpreplace(vendornorm, '\\b(sq|sp|tst|paypal|pp|amzn mktp|visa purchase)\\b', '') vendornorm = regexpreplace(vendornorm, '[^a-z0-9 ]', '') -- drop punctuation vendornorm = trim(vendornorm)

Two things people consistently forget:

1. 1
   Keep the raw value. Normalization is lossy. Store rawvendor and vendornorm side by side so a reviewer can see what was actually collapsed.
2. 2
   Processor prefixes are poison. SQ BLUE BOTTLE, TST BLUE BOTTLE, and PAYPAL *BLUEBOTTLE are the same coffee vendor. If you don't strip SQ, TST, and PAYPAL tokens, you'll end up with three separate vendor records and never catch it.

“

Store rawvendor and vendornorm side by side so reviewers can see what was actually collapsed.

Normalization is lossy. Store rawvendor and vendornorm side by side so a reviewer can see what was actually collapsed.

Layer 2 & 3: blocking, then pairwise scoring

You cannot compare every row to every other row. Ten thousand transactions is 50 million pairs — slow and mostly garbage comparisons. Blocking cuts that down by only comparing rows that share a cheap, coarse key.

Generate block keys, compare only within a block:

blockkey1 = left(vendornorm, 4) || '|' || round(amount, -1) -- prefix + amount bucket blockkey2 = soundex(vendornorm) -- phonetic blockkey3 = last4card || '|' || datetrunc('month', txn_date)

A pair becomes a candidate if it shares any block key. This catches "Blue Bottle" vs "Blu Bottle" (same soundex) and "Netflix $15.99" across months (same prefix + amount bucket), without comparing a plumber to a SaaS tool.

Pairwise scoring with tunable defaults

score = 0 score += 0.35 jarowinkler(vendornorma, vendornormb) score += 0.25 (amounta == amountb ? 1 : 0) score += 0.15 dateproximity(datea, dateb) -- 1.0 if same day, decays to 0 at 7 days score += 0.15 (last4a == last4b ? 1 : 0) score += 0.10 * (invoicea == invoiceb ? 1 : 0)

1. 1
   score ≥ 0.90 → auto-merge candidate (still logged, still reversible)
2. 2
   0.70 ≤ score < 0.90 → human review queue
3. 3
   score < 0.70 → not a match, drop the pair

Keep the date window tight — 7 days — for transaction dedup, and lean on invoicenumber and banktxn_id to distinguish legitimate repeats.

Clustering: transitive closure, carefully

Pairwise matches aren't enough. If A matches B and B matches C, you need A, B, and C in one vendor cluster even if A never directly scored against C. That's transitive closure — a union-find over your matched pairs.

parent = {} def find(x): ... # path-compressed root def union(a, b): parent[find(a)] = find(b) for (a, b, score) in matchedpairs: if score >= 0.90: union(a, b) clusters = groupby_root(parent)

The danger: one bad high-score edge can chain two unrelated clusters together. "Delta" (airline) and "Delta" (plumbing supply) sharing a soundex block plus a coincidental amount match can merge your travel spend into your facilities vendor.

1. 1
   Cap cluster size. Any cluster over roughly 25 members gets flagged for manual inspection — real SMB vendors rarely have that many name variants.
2. 2
   Require two independent signals for an auto-merge edge (e.g., name similarity and matching last4), never name alone.

Two guardrails worth adding: cap cluster size and require two independent signals for auto-merge edges.

The canonical vendor master

Dedup output has to land somewhere stable. A minimal vendor master schema:

CREATE TABLE vendormaster ( vendorid uuid PRIMARY KEY, canonicalname text NOT NULL, -- the display name humans chose vendornorm text NOT NULL, taxid text, -- EIN / VAT where known defaultcurrency char(3), country char(2), status text DEFAULT 'active', -- active | merged | blocked mergedinto uuid, -- set when this vendor is folded into another enrichmentsrc text, -- where taxid/country came from createdat timestamptz, updated_at timestamptz );

CREATE TABLE vendoralias ( aliasid uuid PRIMARY KEY, vendorid uuid REFERENCES vendormaster(vendorid), rawvalue text, -- every spelling we've seen source text );

The vendor_alias table is the quiet hero. It records every raw spelling that ever mapped to a canonical vendor, which means next month's import matches instantly against known aliases instead of re-running the full scoring pass. It's also your audit answer to "why did these three charges merge?"

Governance checklist for the master

1. 1
   Every merged vendor has a non-null merged_into pointing at an active record
2. 2
   No active vendor shares a tax_id with another active vendor
3. 3
   Canonical names are human-approved, never auto-generated for anything above a spend threshold
4. 4
   Each alias row records its source (import file, feed, manual)
5. 5
   Merges are reversible — the alias history lets you split a vendor back out
6. 6
   One named owner signs off on merges above a spend materiality line

The governance list might look like overhead, but most of these are just a few extra columns and one extra reviewer step. The teams that skip it spend weeks untangling bad merges after year-end close.

Multi-currency and amount matching

Amount is a strong match signal right up until currency breaks it. A €100 invoice and a $100 invoice are not duplicates, and a $108 card charge might be the same €100 invoice after FX.

1. 1
   Never compare raw amount across different currencies. Compare (amount, currency) as a pair, or compare a normalized base-currency amount with a tolerance band.
2. 2
   For base-currency comparison, use a tolerance of roughly ±2–3% to absorb FX and fee drift, and require a matching date window plus vendor match before trusting it.
3. 3
   Store both originalamount/originalcurrency and baseamount/basecurrency. Dedup on original when currencies match; fall back to base-amount-with-tolerance only as a scoring signal, never a deterministic key.

Multi-currency edge cases have a way of showing up late — usually when someone spots a "duplicate" on a cross-border invoice and starts pulling the thread. Building these rules in from the start is a lot cheaper than untangling them after the fact.

Enrichment sources (use them to confirm, not to guess)

Enrichment helps collapse vendor variants you'd otherwise miss — but it's a confirmation tool, not a decision-maker.

1. 1
   Tax ID / registry lookups (EIN, VAT number)

   the gold standard. Two records with the same verified tax ID are the same legal vendor, full stop.

2. 2
   Domain/email from receipts

   @stripe.com across variants is a strong cluster signal.

3. 3
   Card-network merchant category codes

   useful for sanity checks — a "Delta" with a travel MCC should not merge with one carrying a hardware MCC.

The pattern that works: use enrichment to raise or veto a score, not to originate a match. A tax-ID match bumps a borderline 0.78 into auto-merge territory; a tax-ID mismatch vetoes an otherwise-high name score.

Staging validation test-suite (run before anything merges)

Nothing writes to your canonical tables until the staging run passes these. Treat them like unit tests for your data.

1. 1
   Row conservation — count(superseded) + count(surviving) == count(input). Nothing vanished.
2. 2
   Amount conservation — sum of surviving + superseded amounts per currency equals input sum. Catches accidental deletes.
3. 3
   No orphan merges — every merged_into resolves to an active vendor.
4. 4
   Recurring-charge sanity — flag any cluster where members are evenly spaced ~28–31 days apart; these are likely not duplicates and should not be collapsed.
5. 5
   Cluster-size ceiling — assert no vendor cluster exceeds the configured max without manual sign-off.
6. 6
   Idempotency — run the pipeline twice on the same input; the second run produces zero new merges.

That last one is underrated. If a second run keeps finding "new" duplicates, your normalization isn't deterministic, and you've got a time bomb for every future import.

Rollback design

Assume you'll get a merge wrong and build for it from day one.

1. 1
   Never hard-delete. Superseded rows keep a supersededby pointer and a mergebatch_id.
2. 2
   Batch everything. Every merge run gets one mergebatchid, so a single statement can reverse an entire run.
3. 3
   Reversal = status flip, not restore. To roll back, set superseded rows to active and the batch's merges to reversed. No data was destroyed, so there's nothing to rebuild.

Keep rollbacks available for the full reconciliation cycle after import — at least through the next close.

Human-review UX and audit trail

The review queue is where borderline (0.70–0.90) pairs get a human decision. What makes reviewers fast and accurate:

1. 1
   Show both raw records side by side, highlighting which fields matched and which didn't.
2. 2
   Show the computed score and the component contributions — a reviewer trusts "name 0.33, amount 0.25, last4 0.15" far more than a bare "0.84."
3. 3
   Three actions only

   merge, not a match, merge and remember this alias. The third one teaches the system.

4. 4
   Every decision writes an audit record

   reviewer, timestamp, before/after state, score, and batch. This is what you hand an auditor when they ask why two line items became one.

One thing worth stealing from teams that got this right: log the score components, not just the verdict. When someone questions a merge six months later, "these matched on verified tax ID plus exact amount" ends the conversation. "The algorithm was 84% confident" starts an argument.

Metrics for automation-readiness

You're done not when the data looks clean, but when it's clean enough that automation can actually run on top of it without misfiring. Track:

You're done not when the data looks clean, but when it's clean enough that automation can actually run on top of it without misfiring.

A realistic run

A 20-person services firm pulled roughly 14,000 historical expense rows from two card feeds and a receipts tool for a system migration. The raw export showed about 2,100 distinct vendor strings.

The deterministic layer — mostly banktxnid + amount + date and attachment_hash — collapsed around 900 straight-up duplicate rows from an old double-import nobody had noticed. Blocking plus scoring surfaced roughly 400 candidate vendor pairs; about 70% auto-merged above 0.90, and the rest — call it 120 or so — went to a reviewer who cleared them in an afternoon because the UX showed matched fields and score components.

Final canonical vendor count: a little over 1,300, down from 2,100. The payoff wasn't the row count — it was that their auto-categorization rules, which had been quietly misfiring because "AMZN Mktp" and "Amazon" were separate vendors, suddenly fired correctly on the clean master.

When this full pipeline makes sense — and when it doesn't

Do this when: you're migrating multi-year history into a new system, you have multiple expense sources that overlap, or your downstream automation (categorization, budgets, forecasting) keeps breaking on vendor noise.

Skip the scoring layers when: you've got a few hundred rows from a single clean source. Run the deterministic keys, eyeball the vendor list, fix it by hand. Building blocking and union-find for 300 rows is engineering theater.

Who should NOT run the auto-merge above 0.90 unattended: anyone without the alias table and batch-rollback in place. Auto-merge without reversibility is how you quietly corrupt a ledger and find out at year-end.

The thread running through all of it: deterministic before probabilistic, supersede before delete, and log why before you log what. Get those three right and a historical expense cleanup stops being a scary one-way migration and becomes a repeatable, testable, reversible pipeline — which is exactly what you need before any automation runs on top of it.