Fuzzy Near-Duplicate Customers — Solution & ExplanationProblems / Fuzzy Near-Duplicate Customers / EditorialApproach

Normalize into a CTE. Compute norm_name = lower(trim(regexp_replace(name, '[.,-]', '', 'g'))) and norm_email = lower(email). The 'g' flag matters: without it DuckDB replaces only the first punctuation character, so 'J.R. Ewing' would normalize to 'jr. ewing' and miss its match. Emails are only lowercased — stripping dots from an email would merge genuinely different addresses.

Self-join on the normalized keys. Join the CTE to itself on a.norm_email = b.norm_email AND a.norm_name = b.norm_name AND a.id < b.id. The strict inequality does double duty: it removes self-matches and it canonicalizes each unordered pair to a single row with the lower id first.

Order by canonical_id, duplicate_id.

In PySpark, project the normalized DataFrame twice with renamed columns (a_id/b_id, ...) and join on the three conditions — renaming avoids ambiguous-column errors in the self-join.Key InsightFuzzy deduplication is really two separate design decisions: what to normalize and what must agree. Normalization must be aggressive enough to unify 'John Smith' / 'john smith.' / 'John- Smith', but the match condition must stay strict enough not to over-merge: the data plants a shared inbox (info@corp.com used by Alice Wong and Bob Trent) that punishes email-only matching, and a repeated name (David Kim at two different emails) that punishes name-only matching. Requiring both keys — plus the a.id < b.id trick for clean pair output — is the standard shape of a pairwise dedupe query, and a 3-record cluster naturally emits all C(3,2) = 3 pairs.Hints

Exact matching finds nothing here: 'John Smith', 'john smith.' and 'John- Smith' are all the same person. Build a normalized key first — a CTE with the punctuation-stripped/trimmed/lowercased name and the lowercased email.

DuckDB's regexp_replace only replaces the FIRST match unless you pass the 'g' flag: regexp_replace(name, '[.,-]', '', 'g'). Without it, 'J.R. Ewing' keeps its second dot.

Self-join the normalized CTE on BOTH normalized columns with a.id < b.id. The strict inequality kills self-matches AND emits each pair exactly once, with the lower id in the canonical position. A 3-record cluster produces 3 pairs.

Matching on email alone is a planted trap: two different people share info@corp.com. Requiring the normalized name to match too keeps them apart.

Solve Fuzzy Near-Duplicate Customers yourself →