Cleaning a Dataset | Dataford Interview Questions

How would you approach cleaning a dataset?

Using the provided shift and facility tables, write a query that returns valid, standardized, deduplicated shift records. Exclude incomplete, invalid, unmatched, or negative-pay records, and retain the most recently updated duplicate.

Output

One row per retained shift, with cleaned_shift_id, caregiver_name, facility_name, shift_start, shift_end, status, and pay_rate.
Order by shift_start, then cleaned_shift_id.

Schema

shift_records

Column	Type	Description
record_idPK	INT	Unique source record identifier
caregiver_name	VARCHAR(150)	Caregiver name as received from the source system
facility_id	INT	Referenced facility identifier
shift_start	TIMESTAMP	Shift start timestamp
shift_end	TIMESTAMP	Shift end timestamp
status	VARCHAR(30)	Raw shift status
pay_rate	NUMERIC(8,2)	Hourly pay rate
updated_at	TIMESTAMP	Source record last-updated timestamp

facilities

Column	Type	Description
facility_idPK	INT	Unique facility identifier
facility_name	VARCHAR(150)	Facility display name

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