Structuring and Cleaning for Prediction | Dataford Interview Questions

Given a raw dataset, how would you structure and clean it for a predictive model?

Use the provided encounter and facility data. Retain the newest record for each encounter, exclude records with missing required dates or invalid facility references, normalize payer and age fields, and preserve missingness indicators where appropriate.

Output

One row per valid encounter with the columns listed in the output schema.
Include normalized patient, facility, age, payer, length-of-stay, and readmission fields.
Order by admission timestamp ascending, then encounter ID ascending.

Schema

raw_encounters

Column	Type	Description
raw_idPK	INT	Unique raw ingestion row identifier
encounter_id	VARCHAR(20)	Business identifier for the encounter
facility_code	VARCHAR(10)	Raw facility identifier
patient_id	VARCHAR(20)	Patient identifier
admitted_at	TIMESTAMP	Admission timestamp
discharge_at	TIMESTAMP	Discharge timestamp
age_text	VARCHAR(10)	Age as received in the raw extract
payer_text	VARCHAR(40)	Unstandardized payer label
length_of_stay_text	VARCHAR(10)	Raw length of stay value
readmission_30d	BOOLEAN	Thirty-day readmission target
ingested_at	TIMESTAMP	Timestamp when the raw row was ingested

facility_reference

Column	Type	Description
facility_codePK	VARCHAR(10)	Validated facility identifier
facility_name	VARCHAR(100)	Facility name
region	VARCHAR(40)	Facility operating region

Your question is Structuring and Cleaning for Prediction. 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