RapidFuzz

Comparing records

A similarity score helps identify records to review. It does not establish that two records refer to the same person or company. Choose a threshold using examples from your own data, and check both false matches and missed matches.

Architecture

This extension binds the RapidFuzz C++ library — the same engine behind Python’s rapidfuzz package and the spiritual successor to fuzzywuzzy — as a set of DuckDB scalar functions. Each function takes (VARCHAR, VARCHAR) and returns a DOUBLE. There is no extra state, no secrets, no ATTACH interface; calls are pure functions composable into any SELECT, WHERE, ON, or ORDER BY clause.

Two function families coexist:

• Ratio scores (rapidfuzz_ratio and friends) return 0–100, mirroring the Python rapidfuzz.fuzz family.
• Distance algorithms (Jaro, Jaro-Winkler, Hamming, Indel, Levenshtein / OSA, LCS, Prefix, Postfix) ship four variants each — _distance, _similarity, _normalized_distance, _normalized_similarity. The naming is mechanical: rapidfuzz_<algo>[_normalized]_<distance|similarity>.

Behavior matches the upstream library closely enough that the RapidFuzz documentation is the authoritative reference for algorithm semantics.

Pick the right algorithm

For cross-pair comparisons where strings differ in length, prefer the _normalized_* variants — raw distance penalizes longer strings just for being long. See the Wikipedia article on Jaro–Winkler distance for the formal definitions of the name-matching variants.

Blocking strategies

A cross join of two tables with 100,000 rows produces 10 billion candidate pairs. Restrict those pairs before scoring. Each restriction trades fewer comparisons for a risk of missing a match:

Length bucket. Two strings that differ in length by more than k characters can’t have a Levenshtein distance ≤ k. Filter on length(a) BETWEEN length(b) - k AND length(b) + k.

First-letter (or first-N) prefix. Cheap join key. Misses pairs where the first letter is itself the typo (rare for names, common for OCR’d text).

Length + first-letter combined. Combine both rules when they fit your data. Measure the remaining candidate count before running the full join.

Phonetic prefix (soundex / metaphone). When typos are phonetic (“Smyth” vs “Smith”). DuckDB’s built-in fuzzy extension supplies the codes; group by code and only score within a group.

Token-based blocking. For multi-word strings, require at least one token in common (e.g. via a normalized array intersection). Excellent for company names where the legal suffix differs but the brand word is shared.

Put the candidate restrictions in the join condition and inspect the query plan when performance matters.

Pre-normalize once, score many

Hoist deterministic transformations out of the join so they run O(n) instead of O(n²):

WITH normalized AS (
  SELECT id,
         regexp_replace(lower(name), '[^a-z0-9 ]', '', 'g') AS name_n,
         length(name) AS len,
         left(lower(name), 1)              AS block_letter
  FROM customers
)
SELECT a.id, b.id,
       rapidfuzz_token_set_ratio(a.name_n, b.name_n) AS score
FROM normalized a
JOIN normalized b
  ON a.id < b.id
 AND a.block_letter = b.block_letter
 AND a.len BETWEEN b.len - 3 AND b.len + 3
WHERE rapidfuzz_token_set_ratio(a.name_n, b.name_n) > 90;

When you also need consistent casing, pluralization, or struct-key rewrites, the sibling inflector extension covers the cleanup side of the same workflow — both sit in the matching-and-casing string-munging bucket.

Top-1 per left row

Record-linkage usually wants exactly one best match per left record. Use QUALIFY (DuckDB’s window-filter clause):

SELECT a.id AS left_id,
       b.id AS right_id,
       rapidfuzz_jaro_winkler_normalized_similarity(a.name, b.name) AS score
FROM left_records  a
JOIN right_records b
  ON left(a.name, 1) = left(b.name, 1)            -- blocking
WHERE rapidfuzz_jaro_winkler_normalized_similarity(a.name, b.name) > 0.92
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY a.id
  ORDER BY rapidfuzz_jaro_winkler_normalized_similarity(a.name, b.name) DESC, b.id
) = 1;

When to reach for something else

• Trigram indexes / FTS. For a single query string against millions of rows, a trigram or full-text index returns top-N candidates in sub-linear time. Score the candidates with RapidFuzz, but don’t expect this extension to replace the index.
• Embeddings / vector search. If “similarity” really means semantic similarity (paraphrase, translation), edit distance is the wrong tool. Reach for an embedding model and a vector index instead.
• Production fuzzy joins on >10M rows. Even with aggressive blocking, very large self-joins benefit from purpose-built record-linkage tooling (Splink, Dedupe). RapidFuzz is the right primitive for the inner-loop score; a record-linkage framework manages the candidate generation, blocking, and resolution around it.

Quick reference

Record linkage

Find the best match for each row in left against right, scored by Jaro-Winkler:

SELECT a.id   AS left_id,
       b.id   AS right_id,
       rapidfuzz_jaro_winkler_normalized_similarity(a.name, b.name) AS sim
FROM left_records  a,
     right_records b
WHERE rapidfuzz_jaro_winkler_normalized_similarity(a.name, b.name) > 0.92
ORDER BY a.id, sim DESC;

For the single best match per left row, wrap with QUALIFY:

SELECT *
FROM (
  SELECT a.id AS left_id, b.id AS right_id,
         rapidfuzz_jaro_winkler_normalized_similarity(a.name, b.name) AS sim,
         ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY rapidfuzz_jaro_winkler_normalized_similarity(a.name, b.name) DESC) AS rn
  FROM left_records a, right_records b
)
WHERE rn = 1 AND sim > 0.92;

Dedupe

Cluster rows by similarity threshold:

WITH pairs AS (
  SELECT a.id AS l, b.id AS r,
         rapidfuzz_token_set_ratio(a.title, b.title) AS sim
  FROM titles a, titles b
  WHERE a.id < b.id
    AND rapidfuzz_token_set_ratio(a.title, b.title) > 90
)
SELECT * FROM pairs ORDER BY sim DESC;

Use the pair list to assign cluster IDs (graph-walk in Python or with a recursive CTE).

Spell check / typo tolerance

For a known-good dictionary, score a candidate against every entry and pick the closest:

SELECT word
FROM dictionary
WHERE rapidfuzz_osa_distance(word, :input) <= 2
ORDER BY rapidfuzz_osa_distance(word, :input)
LIMIT 5;

OSA (Optimal String Alignment) handles single-character typos and adjacent transpositions naturally.

Ratio choice — visual guide

input1: 'New York City'
input2: 'new york new york city'

rapidfuzz_ratio                    → 56  (different overall)
rapidfuzz_partial_ratio            → 100 (one is a substring)
rapidfuzz_token_sort_ratio         → 78  (sorted tokens differ in count)
rapidfuzz_token_set_ratio          → 100 (token sets identical)
rapidfuzz_partial_token_set_ratio  → 100 (sets identical, partial fallback)

The differences matter — pick the one that matches your data semantics. For names with reordered words, token_sort_ratio is usually right. For names with stray duplicates, token_set_ratio. For substring matching, partial_ratio.

Algorithm function naming

Each of the 8 algorithms exposes 4 variants:

• rapidfuzz_<algo>_distance(a, b) — raw edit count.
• rapidfuzz_<algo>_similarity(a, b) — raw match count.
• rapidfuzz_<algo>_normalized_distance(a, b) — [0, 1], 0 = identical.
• rapidfuzz_<algo>_normalized_similarity(a, b) — [0, 1], 1 = identical.

For comparisons across pairs of unequal length, the normalized_* variants are usually what you want — they don’t punish longer strings.