Cleaning Large Messy Data | Dataford Interview Questions

How would you approach a data cleaning task involving large, messy datasets?

Write a PostgreSQL query that returns only transaction records with valid, standardized values, active customers, and a positive amount. Ignore records with NULL identifiers, invalid dates or amounts, unsupported statuses, inactive or unknown customers, and duplicate transaction references. Rows with NULL transaction references are ignored.

Output

One row per retained transaction, with customer_id, customer_name, transaction_ref, transaction_date, amount, and status
Keep only the newest record for each standardized transaction reference
Order by transaction_date ascending, then transaction_ref ascending, then source_record_id ascending

Schema

raw_transactions

Column	Type	Description
source_record_idPK	INT	Unique identifier for the ingested source row
customer_id	INT	Customer identifier supplied by the source
transaction_ref	VARCHAR(40)	Raw transaction reference
amount_text	VARCHAR(30)	Transaction amount as raw text
transaction_date_text	VARCHAR(20)	Transaction date as raw text
status_text	VARCHAR(20)	Raw transaction status
ingested_at	TIMESTAMP	Timestamp when the source row was ingested

customer_accounts

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer display name
account_status	VARCHAR(20)	Current account status

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

Problem