RapidFuzz

What this extension is for

Anywhere you have two strings and want a numeric answer to "how similar are they?" — record linkage across customer or vendor lists, dedup of free-text fields, spell-check against a dictionary, ranking of candidate matches.

• • Record linkage / fuzzy join: Two datasets that should match on name or address but don't share clean keys: score every candidate pair with rapidfuzz_jaro_winkler_normalized_similarity or rapidfuzz_ratio, filter by threshold, keep the best per row with QUALIFY ROW_NUMBER().
• • Dedup messy free-text: Cluster near-duplicate rows in a customer or product table — pick a ratio variant that matches your data semantics (token-set for reordered or stuttering text, partial-ratio for fragments) and emit pairs above a threshold.
• • Spell-check / typo tolerance: Score an input against a known-good dictionary with rapidfuzz_osa_distance or rapidfuzz_jaro_winkler_normalized_similarity and pick the closest match. Handles single-character typos and adjacent-key swaps directly.
• • Similarity ranking: Score a query string against every row in a small table and ORDER BY the score — the simplest "which of these is closest?" pattern, no separate search index.

How it works

RapidFuzz is a C++ implementation of the same algorithms exposed by Python's rapidfuzz and the older fuzzywuzzy. This extension binds those algorithms as DuckDB scalar functions — every call is a vectorized in-process C++ kernel; no Python, no FFI, no per-row interpreter overhead.

• • Two function families: Ratio scores (rapidfuzz_ratio, rapidfuzz_partial_ratio, rapidfuzz_token_sort_ratio, rapidfuzz_token_set_ratio, rapidfuzz_partial_token_set_ratio) return 0–100 similarity, matching Python rapidfuzz.fuzz. Distance algorithms (Jaro, Jaro-Winkler, Hamming, Indel, Levenshtein/OSA, LCS, prefix, postfix) each ship four variants — _distance, _similarity, _normalized_distance, _normalized_similarity.
• • Pick distance vs. normalized: Raw _distance / _similarity return integer edit counts — useful when the strings are the same length or you want a numeric threshold (e.g. "differ by ≤ 2 characters"). The _normalized_* variants scale to [0, 1] and divide by the longer string's length, so they don't penalize length mismatches; reach for these for cross-pair comparisons.
• • Vectorized over columns: Every function is scalar with (VARCHAR, VARCHAR) -> DOUBLE. Use it in SELECT, WHERE, JOIN ... ON, ORDER BY, QUALIFY — anywhere a scalar fits. DuckDB pipelines and multi-threads the call.
• • Behavior parity with upstream: Outputs match the upstream Python library, so the RapidFuzz docs are an authoritative reference for algorithm semantics. The naming scheme is rapidfuzz_<algo>[_normalized]_<distance|similarity> — predictable across all eight algorithm groups.

Cost and scaling

Every comparison is cheap. Comparing every row to every other row is not — pairwise self-joins are O(n²) and that's the limit you hit on real datasets.

• • Self-joins are O(n²): 100k rows × 100k rows is 10 billion pairs — still tractable on a fast machine for the simpler ratios, but will dominate query time. 1M × 1M is not. Filter the pair space before scoring.
• • Use blocking to prune candidates: Blocking (a.k.a. indexing) is the standard record-linkage technique: only compare rows that share a cheap key. First letter, length bucket, normalized prefix, or a phonetic code (e.g. soundex via the fuzzy extension) — join on the block, score on the strings. Reduces O(n²) to O(n·k) where k is the average block size.
• • Pre-normalize once: Lowercase, strip punctuation, collapse whitespace before scoring — every per-row transformation you can hoist out of the join saves it from running on every pair. Pair with the inflector extension when you need consistent casing across both sides.
• • Threshold inside the join: Push the similarity threshold into the WHERE/ON clause so DuckDB can short-circuit. For top-1 per left row, layer QUALIFY ROW_NUMBER() OVER (PARTITION BY left_id ORDER BY score DESC) = 1.

Common Use Cases

Headline ratio (0–100, like Python rapidfuzz.fuzz.ratio)

SELECT rapidfuzz_ratio('hello world', 'helo wrld');  -- 90.0

Match with reordered words

SELECT rapidfuzz_token_sort_ratio('world hello', 'hello world');  -- 100.0

Pick best match per row

SELECT a.id, b.id,
       rapidfuzz_ratio(a.name, b.name) AS score
FROM left_records a, right_records b
WHERE rapidfuzz_ratio(a.name, b.name) > 85
ORDER BY a.id, score DESC;