Data Reconciliation and Normalization

What's being tested

This evaluates entity resolution and record linkage: grouping records that represent the same real-world entity, normalizing fields, and producing a single canonical record with deterministic conflict resolution. Interviewers want to see practical SQL/algorithm patterns, tradeoffs between deterministic vs. probabilistic matching, and attention to correctness and scale.

Patterns & templates

• Deterministic rules first — exact-match keys (normalized email/ID) as highest-precision join; O(n) when indexed, simple to audit.

• Pre-normalize fields using LOWER(), TRIM(), canonical date formats, and hash keys to make joins stable and idempotent.

• Blocking / bucketing to reduce pairwise comparisons: group by first-letter, domain, or locality; reduces comparisons from $O(n^2)$ to near-linear.

• SQL window functions for canonical pick: ROW_NUMBER() OVER (PARTITION BY entity_key ORDER BY score DESC, last_updated DESC).

• Fuzzy matching for soft joins: Levenshtein() or token Jaccard for names; compute similarity thresholds and validate on sample.

• Merge policies: precedence lists (source A > B), most-recent last_updated, or highest-confidence score — encode as deterministic rules.

• Audit trails & reproducibility — store source_ids[], merge-reason, and hashing of input set to allow deterministic backfills.

Common pitfalls

Pitfall: Relying only on fuzzy similarity without blocking leads to $O(n^2)$ pairwise comparisons and unpredictable performance on millions of rows.

Pitfall: Breaking idempotency by updating canonical records destructively; always write new canonical rows or maintain tombstones.

Pitfall: Using ambiguous tie-breakers (random, unstable order) — prefer explicit ORDER BY columns like source_rank, last_updated.

Practice these

The practice cards below cover the canonical variants — solve all of them and time yourself.