Data Cleaning for Large Datasets | Dataford Interview Questions

Describe how you clean and validate large datasets before conducting analysis, and how you ensure data quality for Gartner's analyses.

Write a PostgreSQL query that normalizes raw analysis records and produces a data-quality result for every record. Identify missing fields, invalid dates, nonnumeric metrics, unknown source systems, and duplicate business keys.

Output

One row per raw record with the record ID, normalized fields, source name, parsed date, metric value, duplicate count, issue count, and validation status.
Retain records with null input fields so they can be reviewed.
Order by record_id ascending.

Schema

raw_analysis_data

Column	Type	Description
record_idPK	INT	Unique raw analysis record identifier
source_code	VARCHAR(20)	Source system code as received
record_key	VARCHAR(40)	Business key for the analysis record
event_date_text	VARCHAR(10)	Event date supplied as text
metric_text	VARCHAR(30)	Analysis metric supplied as text

source_systems

Column	Type	Description
source_codePK	VARCHAR(20)	Registered source system code
source_name	VARCHAR(100)	Registered source system name

Your question is Data Cleaning for Large Datasets. 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