Cleaning and Validating Messy Data | Dataford Interview Questions

Walk me through how you would clean, validate, and resolve data quality issues in a messy dataset before running statistical analysis.

Write a PostgreSQL query that identifies valid records after applying the stated data-quality rules and summarizes the results by normalized segment.

Output

One row per observed normalized segment
Columns: segment, valid_record_count, invalid_record_count, avg_metric, min_metric, and max_metric
Include segments with no valid records, show unavailable statistics as NULL, and order by segment ascending

messy_analysis_records

Column	Type	Description
record_idPK	INT	Unique raw record identifier
customer_id	INT	Customer identifier from the source file
event_date_text	VARCHAR(20)	Event date supplied as raw text
metric_text	VARCHAR(30)	Analysis metric supplied as raw text
segment_text	VARCHAR(40)	Raw customer segment
source_row_key	VARCHAR(40)	Source-system key used to detect duplicate records
loaded_at	TIMESTAMP	Timestamp when the source record was loaded

customer_reference

Column	Type	Description
customer_idPK	INT	Canonical customer identifier
customer_name	VARCHAR(100)	Canonical customer name
expected_segment	VARCHAR(40)	Canonical segment used for validation

Your question is Cleaning and Validating Messy Data. 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.