Your customer database has 847,000 records. Marketing swears the list is clean. But when you run a quick analysis, you find "John Smith" at "123 Main St" and "Jon Smith" at "123 Main Street" — two rows that are almost certainly the same person. Multiply that across the entire dataset and you've got a mess: inflated customer counts, duplicate mailings, split purchase histories, and loyalty points that will never reach their rightful owner. This is one of the most common data quality problems in real-world analytics, and it's one that exact-match SQL queries can't solve alone.
Fuzzy matching is the practice of finding records that are similar but not identical — accounting for typos, abbreviations, name variations, and inconsistent formatting. Deduplication is the downstream process of collapsing those near-duplicates into canonical records. Together, they sit at the heart of entity resolution, master data management, and virtually every serious data integration project. The bad news is that naive fuzzy matching is computationally brutal — comparing every record against every other record scales as O(n²), which means a million-row table generates half a trillion comparisons. The good news is that smart blocking strategies can reduce that to something manageable.
By the end of this lesson, you'll be able to design and execute a complete fuzzy deduplication pipeline in SQL — one you could actually deploy on a production dataset.
What you'll learn:
You should be comfortable writing multi-table joins, window functions, CTEs, and aggregate queries. You don't need prior experience with fuzzy matching, but you should understand indexing basics and have a rough intuition for query performance. The examples use PostgreSQL (with the pg_trgm and fuzzystrmatch extensions), but the concepts translate directly to Snowflake, BigQuery, DuckDB, and SQL Server with dialect-specific adjustments noted along the way.
Before reaching for similarity functions, it's worth understanding why exact matching breaks down. Records that represent the same real-world entity diverge in databases for a handful of predictable reasons:
The moment you try to join two tables with ON a.name = b.name, all of these cases silently fail. You get a clean-looking result with missing rows, and without ground truth you may never know.
The solution space has three main layers:
Let's build each layer from the ground up.
Levenshtein distance counts the minimum number of single-character edits — insertions, deletions, substitutions — needed to transform one string into another. "Smith" to "Smithe" is distance 1. "Katherine" to "Kathryn" is distance 2.
In PostgreSQL, enable the fuzzystrmatch extension:
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;
Then use it directly:
SELECT
levenshtein('Katherine', 'Kathryn') AS raw_distance,
levenshtein('Katherine', 'Kathryn') * 1.0 /
GREATEST(LENGTH('Katherine'), LENGTH('Kathryn')) AS normalized_distance;
raw_distance | normalized_distance
-------------+--------------------
2 | 0.2222...
The raw distance is useful for short strings but meaningless across different lengths. Normalizing by the longer string gives you a value between 0 (identical) and 1 (completely different). You'll often see this inverted as a similarity score between 0 and 1, where 1 is a perfect match:
-- Normalized Levenshtein similarity (0 = no match, 1 = perfect match)
SELECT
1 - (levenshtein('Katherine', 'Kathryn') * 1.0 /
GREATEST(LENGTH('Katherine'), LENGTH('Kathryn'))) AS lev_similarity;
Levenshtein is great for short strings like names and codes, but it gets slow on longer text and doesn't handle transpositions (adjacent character swaps) elegantly. "teh" vs. "the" has a Levenshtein distance of 2, even though most people would call it a near-miss. For transpositions specifically, the Damerau-Levenshtein variant handles this better and is available via levenshtein() with some extensions.
A trigram is a sequence of three consecutive characters. The word "smith" generates trigrams: " s", " sm", "smi", "mit", "ith". Trigram similarity measures the overlap between two strings' trigram sets.
PostgreSQL's pg_trgm extension makes this effortless:
CREATE EXTENSION IF NOT EXISTS pg_trgm;
SELECT
similarity('Smith', 'Smithe') AS sim_1,
similarity('Smith', 'Smyth') AS sim_2,
similarity('Smith', 'Jones') AS sim_3,
similarity('123 Main St', '123 Main Street') AS sim_4;
sim_1 | sim_2 | sim_3 | sim_4
-------+--------+-------+------
0.7143 | 0.5556 | 0.0 | 0.6154
Trigrams shine on longer strings and partial matches. They're also the engine behind PostgreSQL's LIKE index acceleration (GIN indexes on pg_trgm can make LIKE '%pattern%' fast). The score ranges from 0 to 1, with 1 being identical.
Tip:
pg_trgmalso providesword_similarity()andstrict_word_similarity(), which are better for cases where you're looking for one string embedded within another — useful for company name matching where "Acme" might match "Acme Corporation."
Phonetic algorithms encode names by how they sound rather than how they're spelled. This handles the "Katherine/Catherine/Kathryn" problem cleanly — all three should encode to the same phonetic key.
-- Both available via fuzzystrmatch
SELECT
soundex('Katherine'),
soundex('Catherine'),
soundex('Kathryn'),
metaphone('Katherine', 10),
metaphone('Catherine', 10),
metaphone('Kathryn', 10);
soundex | soundex | soundex | metaphone | metaphone | metaphone
---------+----------+----------+-----------+-----------+-----------
K365 | C365 | K365 | KTRN | KTRN | KTRN
Notice that Soundex treats "Katherine" and "Catherine" as different (K365 vs. C365) because it encodes the first character directly. Metaphone handles this better by focusing on phonetic pronunciation rather than spelling. For English names, Double Metaphone (dmetaphone() in fuzzystrmatch) is generally the most accurate.
Phonetic encoding is a blunt instrument — it's best used as a blocking key (more on this shortly) rather than a final similarity score. Two words can sound alike and be completely unrelated entities.
Here's a practical guide for which metric to reach for first:
| Scenario | Best Approach |
|---|---|
| Short codes, IDs, product SKUs | Levenshtein |
| Person names with spelling variation | Double Metaphone + Trigram |
| Company names, addresses | Trigram (similarity) |
| Name pronunciation variants | Double Metaphone |
| Long free-text descriptions | Trigram or Jaccard on word tokens |
| Phone numbers after normalization | Levenshtein |
In practice, you'll almost always combine multiple metrics — a technique called composite scoring.
Here's the brutal math: a customer table with 100,000 records has ~5 billion possible pairs. Even if each comparison takes a microsecond, that's 83 hours of compute. Fuzzy matching is useless without a strategy to shrink the candidate space.
Blocking means defining a rule that two records must satisfy before you'll bother comparing them. The goal is to eliminate pairs that are obviously not duplicates while keeping pairs that plausibly could be.
A good blocking key is something both records will share even if they contain typos or variation elsewhere. If two records represent the same person, they'll probably agree on at least one of:
The key insight: you don't need a single blocking key to work. You use multiple blocking passes, and a pair only needs to survive one of them to get compared. This is called disjunctive blocking — and it lets you keep recall high while still cutting 99%+ of the comparison space.
Let's work with a realistic scenario. Suppose you have a customers table populated from multiple CRM imports:
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
first_name TEXT,
last_name TEXT,
email TEXT,
phone TEXT,
address_line1 TEXT,
city TEXT,
state TEXT,
zip TEXT,
created_at TIMESTAMPTZ
);
The table has 150,000 rows with the kinds of inconsistencies described earlier. Here's a blocking approach using multiple keys:
-- Step 1: Create a standardized staging view for blocking
CREATE OR REPLACE VIEW customer_normalized AS
SELECT
customer_id,
first_name,
last_name,
email,
-- Normalize phone to digits only
REGEXP_REPLACE(phone, '[^0-9]', '', 'g') AS phone_digits,
address_line1,
city,
state,
zip,
-- Blocking keys
LEFT(LOWER(TRIM(last_name)), 3) AS block_lastname_prefix,
dmetaphone(TRIM(last_name)) AS block_last_metaphone,
LEFT(REGEXP_REPLACE(phone, '[^0-9]', '', 'g'), 6) AS block_phone_prefix,
LEFT(TRIM(zip), 5) AS block_zip
FROM customers
WHERE last_name IS NOT NULL;
-- Step 2: Generate candidate pairs using disjunctive blocking
-- Each UNION arm represents one blocking pass
CREATE TABLE candidate_pairs AS
-- Block 1: Same last name prefix AND same ZIP
SELECT DISTINCT
a.customer_id AS id_a,
b.customer_id AS id_b,
'lastname_zip' AS block_source
FROM customer_normalized a
JOIN customer_normalized b
ON a.block_lastname_prefix = b.block_lastname_prefix
AND a.block_zip = b.block_zip
AND a.customer_id < b.customer_id -- avoid self-pairs and duplicates
UNION
-- Block 2: Same last name phonetic encoding
SELECT DISTINCT
a.customer_id,
b.customer_id,
'metaphone_last'
FROM customer_normalized a
JOIN customer_normalized b
ON a.block_last_metaphone = b.block_last_metaphone
AND a.block_last_metaphone <> ''
AND a.customer_id < b.customer_id
UNION
-- Block 3: Same phone prefix (first 6 digits of normalized phone)
SELECT DISTINCT
a.customer_id,
b.customer_id,
'phone_prefix'
FROM customer_normalized a
JOIN customer_normalized b
ON a.block_phone_prefix = b.block_phone_prefix
AND LENGTH(a.block_phone_prefix) = 6
AND a.customer_id < b.customer_id;
Warning: Don't use
ORconditions in a single join for disjunctive blocking. Databases rarely optimize OR-joined conditions well. TheUNIONapproach forces each blocking rule to use its own efficient index scan.
The a.customer_id < b.customer_id condition is important — it ensures each pair appears exactly once, and prevents a record from being compared against itself. Without it you'd generate both (42, 99) and (99, 42) and everything paired with itself.
After this step, deduplicate the candidate pairs table (since a pair might have been caught by multiple blocking rules):
-- Collapse multiple block sources for the same pair
CREATE TABLE candidate_pairs_deduped AS
SELECT
id_a,
id_b,
array_agg(block_source) AS matched_blocks
FROM candidate_pairs
GROUP BY id_a, id_b;
Now you have a manageable set of candidate pairs. Time to compute real similarity scores and decide which pairs are actual duplicates.
No single metric is reliable enough on its own. You want to score multiple fields and combine them into a weighted total. Here's a production-style scoring query:
CREATE TABLE scored_pairs AS
WITH pair_data AS (
SELECT
cp.id_a,
cp.id_b,
cp.matched_blocks,
a.first_name AS fn_a, b.first_name AS fn_b,
a.last_name AS ln_a, b.last_name AS ln_b,
a.email AS em_a, b.email AS em_b,
a.phone_digits AS ph_a, b.phone_digits AS ph_b,
a.address_line1 AS ad_a, b.address_line1 AS ad_b,
a.zip AS zp_a, b.zip AS zp_b
FROM candidate_pairs_deduped cp
JOIN customer_normalized a ON cp.id_a = a.customer_id
JOIN customer_normalized b ON cp.id_b = b.customer_id
),
similarity_scores AS (
SELECT
id_a,
id_b,
matched_blocks,
-- First name: trigram similarity
similarity(LOWER(TRIM(fn_a)), LOWER(TRIM(fn_b))) AS score_firstname,
-- Last name: average of trigram and metaphone match
(
similarity(LOWER(TRIM(ln_a)), LOWER(TRIM(ln_b))) * 0.7
+ CASE WHEN dmetaphone(ln_a) = dmetaphone(ln_b) THEN 1.0 ELSE 0.0 END * 0.3
) AS score_lastname,
-- Email: exact or trigram
CASE
WHEN LOWER(TRIM(em_a)) = LOWER(TRIM(em_b)) THEN 1.0
WHEN em_a IS NULL OR em_b IS NULL THEN 0.0
ELSE similarity(LOWER(TRIM(em_a)), LOWER(TRIM(em_b)))
END AS score_email,
-- Phone: Levenshtein on normalized digits
CASE
WHEN ph_a IS NULL OR ph_b IS NULL THEN 0.0
WHEN ph_a = ph_b THEN 1.0
ELSE 1.0 - (
levenshtein(ph_a, ph_b)::FLOAT
/ GREATEST(LENGTH(ph_a), LENGTH(ph_b))
)
END AS score_phone,
-- Address: trigram on address line
CASE
WHEN ad_a IS NULL OR ad_b IS NULL THEN 0.0
ELSE similarity(LOWER(TRIM(ad_a)), LOWER(TRIM(ad_b)))
END AS score_address,
-- ZIP: exact match
CASE WHEN LEFT(zp_a, 5) = LEFT(zp_b, 5) THEN 1.0 ELSE 0.0 END AS score_zip
FROM pair_data
)
SELECT
id_a,
id_b,
matched_blocks,
score_firstname,
score_lastname,
score_email,
score_phone,
score_address,
score_zip,
-- Weighted composite score
-- Email and phone are high-confidence; name/address are lower
(
score_firstname * 0.15
+ score_lastname * 0.20
+ score_email * 0.30
+ score_phone * 0.20
+ score_address * 0.10
+ score_zip * 0.05
) AS composite_score
FROM similarity_scores;
This is where art meets science. A composite score above 0.85 is almost certainly a duplicate. Below 0.50, almost certainly not. The 0.50–0.85 range is where your domain expertise matters.
A useful pattern for threshold tuning is to sample pairs at different score bands and manually review them:
-- Review sample pairs at different score bands to calibrate thresholds
SELECT
id_a,
id_b,
composite_score,
ROUND(composite_score::NUMERIC, 1) AS score_band,
score_firstname, score_lastname, score_email, score_phone
FROM scored_pairs
WHERE composite_score BETWEEN 0.50 AND 0.90
ORDER BY RANDOM()
LIMIT 50;
Look at what these records actually look like. You'll quickly develop intuition for where to draw the line, and you may notice that certain field combinations are more diagnostic than others for your specific data.
Tip: If email matches exactly (score_email = 1.0), that's often sufficient for a match regardless of name variation — people change their names but rarely their email addresses. Consider adding a "fast path" rule that flags pairs with identical emails as matches immediately, before even computing the composite.
Identifying pairs is only half the job. If record A matches B, and B matches C, then A, B, and C are all duplicates of each other — even if A and C weren't directly compared. You need transitive closure: grouping records that are connected through any chain of matches.
This is a graph problem. Each record is a node; each matched pair is an edge. You want connected components.
PostgreSQL doesn't have a native graph component algorithm, but you can implement one with a recursive CTE:
-- Create the match list first
CREATE TABLE confirmed_matches AS
SELECT id_a, id_b
FROM scored_pairs
WHERE composite_score >= 0.82; -- your chosen threshold
-- Recursive connected components
WITH RECURSIVE components AS (
-- Base case: each node starts in its own component
-- (using the lower ID as the component representative)
SELECT id_a AS node_id, id_a AS component_id
FROM confirmed_matches
UNION
SELECT id_b, id_b
FROM confirmed_matches
UNION ALL
-- Recursive case: propagate the minimum component ID through edges
SELECT
m.id_b AS node_id,
LEAST(c.component_id, m.id_a) AS component_id
FROM components c
JOIN confirmed_matches m ON c.node_id = m.id_a
WHERE LEAST(c.component_id, m.id_a) < c.component_id
),
-- Take the minimum component_id for each node (stable convergence)
final_components AS (
SELECT node_id, MIN(component_id) AS component_id
FROM components
GROUP BY node_id
)
SELECT
node_id AS customer_id,
component_id AS duplicate_group_id
FROM final_components
ORDER BY component_id, node_id;
Warning: Recursive CTEs for connected components can behave unexpectedly on large, dense graphs. If your match threshold is too low, you'll create chains where everything merges into one massive component. Always sanity-check the distribution of component sizes before proceeding to merge.
-- Sanity check: distribution of duplicate group sizes
SELECT
component_size,
COUNT(*) AS num_groups
FROM (
SELECT component_id, COUNT(*) AS component_size
FROM duplicate_groups
GROUP BY component_id
) t
GROUP BY component_size
ORDER BY component_size;
If you see a component with 10,000 members, your threshold is too permissive. Investigate by lowering the composite score and seeing what's driving it.
Once you have duplicate groups, you need to pick a canonical (surviving) record for each group. This decision should be principled, not arbitrary.
Common canonicalization strategies:
Here's a field-level fusion approach:
CREATE TABLE canonical_customers AS
WITH ranked AS (
SELECT
c.*,
dg.component_id AS duplicate_group_id,
-- Score completeness: count non-null key fields
(
CASE WHEN c.email IS NOT NULL THEN 1 ELSE 0 END
+ CASE WHEN c.phone IS NOT NULL THEN 1 ELSE 0 END
+ CASE WHEN c.address_line1 IS NOT NULL THEN 1 ELSE 0 END
+ CASE WHEN c.zip IS NOT NULL THEN 1 ELSE 0 END
) AS completeness_score,
ROW_NUMBER() OVER (
PARTITION BY dg.component_id
ORDER BY
-- Prefer more complete records
(CASE WHEN c.email IS NOT NULL THEN 1 ELSE 0 END
+ CASE WHEN c.phone IS NOT NULL THEN 1 ELSE 0 END
+ CASE WHEN c.address_line1 IS NOT NULL THEN 1 ELSE 0 END
+ CASE WHEN c.zip IS NOT NULL THEN 1 ELSE 0 END) DESC,
-- Break ties with most recent
c.created_at DESC
) AS rn
FROM customers c
JOIN duplicate_groups dg ON c.customer_id = dg.customer_id
),
-- Also include records that weren't matched to anything (singletons)
singletons AS (
SELECT c.*, NULL::INT AS duplicate_group_id, 0 AS completeness_score, 1 AS rn
FROM customers c
LEFT JOIN duplicate_groups dg ON c.customer_id = dg.customer_id
WHERE dg.customer_id IS NULL
)
SELECT * FROM ranked WHERE rn = 1
UNION ALL
SELECT * FROM singletons WHERE rn = 1;
This gives you a deduplicated customer list where each canonical record either:
Before running the candidate pair generation, make sure your blocking key columns are indexed:
-- On the normalized view, these indexes need to be on the underlying table
-- or use a materialized view instead
CREATE MATERIALIZED VIEW customer_normalized_mat AS
SELECT ... ; -- same query as before
CREATE INDEX idx_block_lastname ON customer_normalized_mat (block_lastname_prefix);
CREATE INDEX idx_block_metaphone ON customer_normalized_mat (block_last_metaphone);
CREATE INDEX idx_block_phone ON customer_normalized_mat (block_phone_prefix);
CREATE INDEX idx_block_zip ON customer_normalized_mat (block_zip);
If you're doing similarity-based lookups (not just blocking), pg_trgm supports GIN indexes that make similarity() and LIKE operations dramatically faster:
CREATE INDEX idx_trgm_lastname ON customers USING GIN (last_name gin_trgm_ops);
CREATE INDEX idx_trgm_firstname ON customers USING GIN (first_name gin_trgm_ops);
When using pg_trgm, you can use the % operator (similarity threshold operator) instead of calling similarity() in a WHERE clause. The % operator uses the GIN index:
-- Set minimum similarity threshold (default is 0.3)
SET pg_trgm.similarity_threshold = 0.5;
-- This uses the GIN index if one exists
SELECT a.customer_id, b.customer_id, similarity(a.last_name, b.last_name)
FROM customers a
JOIN customers b ON a.last_name % b.last_name
WHERE a.customer_id < b.customer_id;
This approach works well for smaller tables where blocking alone isn't sufficient to reduce the pair space.
| Feature | PostgreSQL | Snowflake | BigQuery | SQL Server |
|---|---|---|---|---|
| Edit distance | levenshtein() (fuzzystrmatch) |
EDITDISTANCE() |
UDF or JS | DIFFERENCE() (limited) |
| Trigram similarity | similarity() (pg_trgm) |
JAROWINKLER_SIMILARITY() |
UDF | DIFFERENCE() |
| Phonetic | soundex(), metaphone() |
SOUNDEX() |
SOUNDEX() |
SOUNDEX() |
| Jaro-Winkler | via extension | JAROWINKLER_SIMILARITY() |
UDF | via CLR |
Snowflake's JAROWINKLER_SIMILARITY() is worth highlighting — Jaro-Winkler is often more accurate than Levenshtein for name matching because it gives extra weight to matching characters near the beginning of strings, which is where names tend to agree even when they diverge later.
You'll build a deduplication pipeline for a vendor contact list that was assembled from three different spreadsheets. Download or create the following test dataset:
CREATE TABLE vendor_contacts (
contact_id SERIAL PRIMARY KEY,
company TEXT,
first_name TEXT,
last_name TEXT,
email TEXT,
phone TEXT,
source TEXT -- 'crm', 'marketing', 'finance'
);
INSERT INTO vendor_contacts (company, first_name, last_name, email, phone, source) VALUES
('Acme Corp', 'Jennifer', 'Williams', 'jwilliams@acmecorp.com', '555-310-4821', 'crm'),
('ACME Corporation', 'Jen', 'Williams', 'jwilliams@acmecorp.com', '5553104821', 'marketing'),
('Acme Corp.', 'Jennifer', 'Wiliams', 'j.williams@acmecorp.com', '555.310.4821', 'finance'),
('Globex Industries', 'Michael', 'Johnson', 'mjohnson@globex.com', '555-842-7700', 'crm'),
('Globex Ind.', 'Mike', 'Johnson', 'mike.j@globex.com', '5558427700', 'marketing'),
('Initech LLC', 'Sarah', 'Chen', 'schen@initech.com', '555-201-9934', 'crm'),
('Initech', 'Sara', 'Chen', 'schen@initech.com', '555-201-9934', 'finance'),
('Umbrella Co', 'Robert', 'Anderson', 'randerson@umbrella.co', '555-763-1200', 'crm'),
('Umbrella Company', 'Bob', 'Anderson', 'b.anderson@umbrella.co', '5557631200', 'marketing'),
('Vandelay Exports', 'George', 'Costanza', 'gcostanza@vandelay.com', '555-400-0001', 'crm');
Your tasks:
Create the normalized view with blocking keys: last name prefix, Double Metaphone of last name, normalized phone prefix, and first 4 characters of email (before the @).
Generate candidate pairs using at least two different blocking rules with the UNION approach.
Score each candidate pair using at least three fields. Include normalized Levenshtein for phone numbers and trigram similarity for names.
Apply a threshold at 0.70 composite score and examine your results. Do all three Acme Corp contacts collapse into one group?
Identify the canonical record for each duplicate group using the "most complete record, then most recent source" tie-breaking logic. Since all records were inserted simultaneously, use source = 'crm' as the preferred source.
Expected outcome: You should identify at least 3 duplicate groups (Acme, Globex, and Initech/Umbrella) with Vandelay Exports remaining as a singleton.
Mistake 1: Comparing raw values without normalization
Running similarity on "(555) 310-4821" vs. "5553104821" gives you a misleadingly low score because the formatting characters dominate the comparison. Always normalize before scoring — strip punctuation from phones, lowercase and trim names, expand common abbreviations.
-- Bad
similarity(phone_a, phone_b)
-- Good
similarity(
REGEXP_REPLACE(phone_a, '[^0-9]', '', 'g'),
REGEXP_REPLACE(phone_b, '[^0-9]', '', 'g')
)
Mistake 2: Forgetting the customer_id < b.customer_id constraint
Without it, you generate both (42, 99) and (99, 42), doubling your work, and you generate self-pairs like (42, 42) which will score 1.0 and pollute your results. Always enforce a directional constraint on the ID comparison.
Mistake 3: Using too-aggressive blocking and missing true duplicates
If your blocking key is too restrictive, you'll miss real matches. A common mistake is blocking on both first name prefix AND last name prefix — if either has a typo, the pair gets blocked out entirely. Block on one field at a time per rule, and use multiple passes.
Mistake 4: Setting a single threshold for all field combinations
A pair that has an identical email match should be treated differently than one that's matching only on name similarity. Consider separate thresholds for different "match profiles":
SELECT
id_a,
id_b,
composite_score,
CASE
WHEN score_email = 1.0 THEN 'definite_match' -- identical email
WHEN composite_score >= 0.85 THEN 'probable_match'
WHEN composite_score >= 0.70 THEN 'possible_match'
ELSE 'not_a_match'
END AS match_decision
FROM scored_pairs;
Mistake 5: Confusing similarity() and word_similarity()
similarity('Acme', 'Acme Corporation') scores around 0.30 because the trigram sets are very different in size. word_similarity('Acme', 'Acme Corporation') scores around 0.80 because it checks whether one string's trigrams are a subset of the other's. For company name matching, word_similarity is almost always what you want.
SELECT
similarity('Acme', 'Acme Corporation') AS full_sim,
word_similarity('Acme', 'Acme Corporation') AS word_sim;
-- full_sim: ~0.30 word_sim: ~0.80
Mistake 6: The transitive closure stack overflow
Recursive CTEs for connected components can hit recursion depth limits on large or dense graphs. If your data has large clusters or a low threshold, consider processing connected components in application code (Python's networkx library is excellent for this) and loading the results back into the database.
You've built a complete fuzzy deduplication pipeline from scratch. To recap the architecture:
UNION arms, one per blocking rule)This pipeline will handle the vast majority of real-world deduplication cases. For datasets above a few million rows, you'll want to evaluate dedicated entity resolution tools like Splink (which runs on DuckDB and Spark) or commercial MDM platforms — but understanding the SQL foundations makes you a much more effective user of any of those tools.
Where to go from here:
libpostal for normalizing address data before fuzzy matching — address fields are notoriously hard to match without pre-normalizationThe moment you've built this pipeline once on real data, you'll never look at a customer or vendor list the same way again.
Learning Path: Advanced SQL Queries