Duplicate Records and Data Quality | Dataford Interview Questions

How do you handle duplicate records and ensure data quality before performing your analysis?

Use raw_records and record_sources to identify duplicate external record keys. Retain one quality-valid record per duplicate group, prioritizing valid data, then the most recently updated record.

Output

One row per duplicate group with external_record_key, canonical_record_id, duplicate_count, and quality_issue_count
Exclude groups with no quality-valid record
Sort by external_record_key ascending

Schema

record_sources

Column	Type	Description
source_idPK	INT	Unique source identifier
source_name	VARCHAR(100)	Name of the originating source
is_active	BOOLEAN	Whether the source is currently trusted

raw_records

Column	Type	Description
record_idPK	INT	Unique raw record identifier
external_record_key	VARCHAR(50)	Business key used to identify duplicate records
source_id	INT	Originating source identifier
customer_email	VARCHAR(255)	Customer email address
event_date	DATE	Date associated with the record
amount	DECIMAL(10,2)	Recorded amount
updated_at	TIMESTAMP	Timestamp of the latest source update

Your question is Duplicate Records and Data Quality. 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