Data Cleaning and Validation | Dataford Interview Questions

How do you approach data cleaning and validation when dealing with large, unstructured datasets?

Write a PostgreSQL query that standardizes raw usage fields, checks each record against the subscriber reference data, and identifies invalid records. Treat blank values, malformed dates, unsupported event types, negative usage, unknown subscribers, and inactive subscribers as validation failures.

Output

Return one row per invalid usage event.
Include event_id, cleaned subscriber and event fields, validation_status, and validation_reason.
Order by event_id ascending.

Schema

raw_usage_events

Column	Type	Description
event_idPK	INT	Unique raw usage event identifier
raw_subscriber_id	VARCHAR(30)	Unstandardized subscriber identifier
raw_event_type	VARCHAR(30)	Unstandardized usage event type
raw_event_date	VARCHAR(30)	Event date stored as raw text
raw_usage_mb	VARCHAR(30)	Usage amount stored as raw text in megabytes

subscriber_accounts

Column	Type	Description
subscriber_idPK	VARCHAR(30)	Reference subscriber identifier
account_status	VARCHAR(20)	Current subscriber account status

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