Design an entity-resolution pipeline for production identifiers. False merges are worse than unmatched records. Every merge must be reversible and attributable.
Entities: [customer, account, device, household, company]
Source systems and native keys: [list]
Match fields: [email, phone, name, address, device, tax id]
Known collision patterns: [shared emails, recycled phones, test accounts]
Latency need: [batch daily / near-real-time]
Privacy constraints: [jurisdictions, hashing, purpose limitation]
Current matching notes or SQL:
[Paste]
Downstream consumers: [warehouse, CRM, ML features]
Deliver:
1. Golden-record model: surviving key, source keys, confidence, and evidence
2. Blocking / indexing strategy so the job is not N^2
3. Deterministic match rules with counterexamples
4. Probabilistic features, thresholds, and human-review queue
5. Split, merge, and unmerge operations with audit log schema
6. Idempotent incremental updates when a source row is corrected
7. Handling of deleted, merged, and recycled source identifiers
8. Quality metrics: precision/recall on labeled pairs, plus operational volume
9. Privacy: what must not be used as a match key, and hashed comparison rules
10. Replay and backfill plan when a rule change would re-cluster history
Never silently overwrite a human-reviewed unlink. Prefer a pending_match table over auto-merging below a stated confidence.