De-duplicating Customer Records in MDM | Dataford Interview Questions

Write a SQL query to identify and resolve duplicate customer records within an enterprise Informatica MDM system.

Treat records as duplicates when they share a normalized nonblank email address, or when no email is available and they share a normalized phone number. Select one surviving record per duplicate set using completeness, source priority, most recent update, and record ID as tie-breakers.

Output

One row per non-surviving duplicate record.
Return duplicate_record_id, survivor_record_id, match_key, duplicate_source_system, and survivor_source_system.
Order by match_key, then duplicate_record_id.

Schema

customer_records

Column	Type	Description
record_idPK	INT	Customer record identifier
source_id	INT	Originating source system
full_name	VARCHAR(150)	Customer name
email	VARCHAR(255)	Customer email address
phone	VARCHAR(30)	Customer phone number
address	VARCHAR(255)	Customer mailing address
updated_at	TIMESTAMP	Last source update timestamp

source_systems

Column	Type	Description
source_idPK	INT	Source system identifier
system_name	VARCHAR(100)	Source system name
priority	INT	Lower values indicate higher source trust

Your question is De-duplicating Customer Records in MDM. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem