Fuzzy Matching in DuckDB: Match and Review Customer Names

Which customer is “tools ACME”? You can probably spot Acme Tools. Now try Cedar Works: two customer IDs share that exact name. Both earn a perfect similarity score. Which one gets the invoice?

Let’s build a matching queue that exposes those decisions. You’ll run RapidFuzz in DuckDB, rescue a match that an equality join misses, and catch a “perfect” match that needs a person. Try the three cases below, then reproduce the scores yourself.

Meet your slightly messy CRM export

These are all the input records, not a preview of a hidden dataset. id identifies a record; country is its recorded country code. Before running anything, find the reordered name, the split word, and the two Cedar Works customers.

Choose a score for the kind of mess you have

The setup defines name_key: lowercase, trim, and collapse repeated whitespace. Accents and punctuation stay intact. Removing them or stripping legal suffixes needs a business rule; normalization can erase meaningful distinctions.

Our hand-checked pairs are leads 101 → customer 2, 102 → 1, and 103 → 4. The first query compares their names. Predict which scorer handles “Blue Bird” better, then run it:

SELECT l.name AS incoming, c.name AS reference,
       round(rapidfuzz_ratio(name_key(l.name), name_key(c.name)), 1) AS ratio,
       round(rapidfuzz_token_sort_ratio(name_key(l.name), name_key(c.name)), 1) AS token_sort
FROM leads l JOIN labels t ON l.id = t.lead_id
JOIN customers c ON c.id = t.customer_id
WHERE l.id IN (101, 102, 103)
ORDER BY l.id;

rapidfuzz_token_sort_ratio sorts words before comparing them. That fixes the first pair. It hurts the last pair: splitting “Bluebird” changes the tokens and their sorted order. The RapidFuzz scorer documentation explains these different comparison rules.

We will use token sort below to make its limitations visible. In your own workflow, evaluate scorers against labeled matches and nonmatches. A score of 96 is a similarity measure, not a 96% probability of identity.

Reduce the candidate set before scoring

Comparing every incoming record with every customer creates a cross product. Here that is only 63 pairs. Two tables of 100,000 rows would produce ten billion.

A blocking rule selects plausible pairs first. Our example requires equal country codes:

SELECT
  (SELECT count(*) FROM leads CROSS JOIN customers) AS all_pairs,
  (SELECT count(*) FROM leads l JOIN customers c
     ON l.country = c.country) AS candidate_pairs;

The result is 26 candidate pairs instead of 63. This is a count of comparisons in a small fixture, not a runtime benchmark. The setup’s candidates view applies that country join and scores each surviving pair. Its ranked view uses row_number() to choose an order and lead(score) to retain the runner-up.

Empty or missing normalized names are excluded from scoring; their incoming records remain in the final queue. Two empty strings should never become a confident match.

Blocking depends on reliable country codes. Normalize missing, stale, or inconsistent codes separately: otherwise the correct match can disappear before scoring.

Keep the runner-up and the unmatched rows

Our demonstration rules require a score of at least 85 and a gap of at least 10 points from the next candidate. These are teaching thresholds, not production recommendations.

SELECT l.id, l.name, r.customer,
       round(r.score, 1) AS score,
       round(r.score - r.runner_up, 1) AS gap,
       CASE
         WHEN r.customer_id IS NULL THEN 'no candidates'
         WHEN r.score < 85 THEN 'below threshold'
         WHEN r.runner_up IS NOT NULL AND r.score - r.runner_up < 10
           THEN 'review: close scores'
         ELSE 'suggested match'
       END AS decision
FROM leads l LEFT JOIN ranked r
  ON l.id = r.lead_id AND r.rank = 1
ORDER BY l.id;

Lead 104 has no candidate, so its customer, score, and gap are all NULL. Lead 103 has a score but no runner-up; only its gap is NULL. Cedar Works has two perfect candidates, giving a gap of zero.

The LEFT JOIN preserves records that have no candidates. Dropping them would make the output look cleaner while hiding failed matches.

Ordering tied scores by ID is reproducible, but does not resolve identity. Cedar Works stays in review. A lone candidate still needs independent corroboration.

SELECT customer_id, customer, round(score, 1) AS score
FROM candidates
WHERE lead_id = 105
  AND score = (SELECT max(score) FROM candidates WHERE lead_id = 105)
ORDER BY customer_id;

Test the blocking rule against known answers

The labels table is our answer key: 101 → 2, 102 → 1, 103 → 4, 104 → 6, 105 → 7, and 107 → 2. Lead 106 has no match. One known pair never reaches scoring:

SELECT t.lead_id, l.country AS recorded_country,
       c.id AS true_customer_id, c.country AS true_country
FROM labels t
JOIN leads l ON l.id = t.lead_id
JOIN customers c ON c.id = t.customer_id
WHERE NOT EXISTS (
  SELECT 1 FROM candidates p
  WHERE p.lead_id = t.lead_id AND p.customer_id = t.customer_id
);

The result is lead 104: its recorded country is MX, but its labeled customer is in CA. Five of six known pairs survive blocking. Changing the score threshold cannot recover the excluded pair.

For a real evaluation, label matches and nonmatches, measure candidate recall and suggestion accuracy, and inspect a held-out set. This tiny fixture demonstrates failure modes; it cannot estimate production accuracy.

Find possible duplicates within one table

For duplicate review, compare the customer table with itself and keep only a.id < b.id. That removes self-comparisons and mirrored pairs:

SELECT a.id AS first_id, b.id AS second_id,
       a.name AS first_name, b.name AS second_name
FROM customers a JOIN customers b
  ON a.country = b.country AND a.id < b.id
WHERE nullif(name_key(a.name), '') IS NOT NULL
  AND nullif(name_key(b.name), '') IS NOT NULL
  AND rapidfuzz_token_sort_ratio(
    name_key(a.name), name_key(b.name)
  ) >= 95;

Customer IDs 7 and 9 both say Cedar Works; review their independent identifiers before merging. Similarity is not transitive: A matching B and B matching C does not establish that A and C are the same customer.

Keep original IDs, strings, and scoring rules so matching decisions stay reversible. Continue with the RapidFuzz reference or Query.Farm consulting for larger integrations.