Parse Raw Data Into Structured Data | Dataford Interview Questions

Parse raw data into structured data.

Use the provided raw employee records and department reference data. Return only records with exactly five fields, valid required values, non-negative numeric salaries, valid ISO dates, and department codes found in the reference data.

Output

One row per valid raw record, ordered by employee_id, then raw_record_id.
Columns: raw_record_id, employee_id, full_name, department_code, annual_salary, start_date, and department_name.
Exclude malformed, incomplete, invalid, and unknown-department records.

Schema

raw_employee_records

Column	Type	Description
raw_record_idPK	INTEGER	Identifier for the imported raw record
raw_payload	TEXT	Pipe-delimited employee data
loaded_at	TIMESTAMP	Timestamp when the raw record was loaded

departments

Column	Type	Description
department_codePK	VARCHAR(10)	Reference code for a department
department_name	VARCHAR(100)	Human-readable department name

Your question is Parse Raw Data Into Structured 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