The Deduplication That Ate the Good Rows
A customer-master workflow standardizes names and addresses, then deduplicates on a fuzzy match of full name plus postal code. Historically it removed about 2% of records. After a recent upstream change, the cleanse step began uppercasing and trimming all name fields, and the dedupe step now removes 11% of records. Downstream reporting shows a sudden drop in active customer counts. The team wants to loosen the fuzzy-match threshold to recover the lost records. There are 1.4M rows; the run takes 22 minutes and most of that is the dedupe step.
1. Is loosening the fuzzy threshold the right fix? Explain what likely changed and what you would inspect.
Read the worked answer
Loosening the threshold would make the over-merging worse, not better - a lower (more permissive) threshold merges more aggressively. The jump from 2% to 11% coincides with the cleanse change, so the cleanse is the prime suspect.
Inspect:
- What the cleanse actually did. Uppercasing and trimming should be harmless for matching, but if the cleanse also collapsed internal whitespace, removed punctuation, or truncated names, the match key changed semantics. A truncated name field makes many distinct people look identical.
- The match key composition. Fuzzy match on full name + postal code is sensitive to how the name is normalized. If the cleanse now strips suffixes (Jr., Sr.) or middle initials, 'JOHN SMITH JR' and 'JOHN SMITH SR' may collapse.
- Sample the merged pairs. Pull 20–50 records that are now being merged and eyeball whether they are genuinely the same customer. That single sample usually settles the question.
Diagnose the cleanse change first; the dedupe parameters are probably fine and were tuned against the old normalization.
2. The dedupe step dominates runtime. How would you approach performance without changing match semantics?
Read the worked answer
Reduce the comparison space before fuzzy matching, and only then consider tuning.
- Blocking: partition candidates by a cheap exact key (e.g., postal code, or first letter of normalized surname) so fuzzy comparison only runs within blocks. This is the biggest lever and preserves semantics if the block key is a superset of true matches.
- Pre-filter exact duplicates: remove exact-key duplicates with a fast exact match before the fuzzy step, so fuzzy only handles the residual.
- Trim the payload: carry only the columns the match needs into the fuzzy step, then join the match result back to the full record. Less data per row speeds comparisons.
- Measure before tuning thresholds or algorithms - changing the algorithm changes results, which is exactly what you are trying to avoid.
Validate any blocking scheme by confirming that all known true-match pairs still land in the same block; otherwise you have reintroduced silent data loss in the name of speed.