Cleaning and Transforming Disparate Data | Dataford Interview Questions

How do you handle data cleaning and transformation when working with disparate sources like Workday or internal databases?

Write a SQL query that standardizes employee records from Workday and an internal HR database, then returns one reconciled row per employee. Treat blank strings as missing values, normalize department names to a single canonical value, and prefer the most recent non-null hire date when both sources disagree.

Output

One row per employee_id
Columns: employee_id, full_name, canonical_department, hire_date, source_status
Include employees present in either source
Sort by employee_id

Schema

workday_employees

Column	Type	Description
employee_idPK	INT	Workday employee identifier
full_name	VARCHAR(255)	Employee full name as stored in Workday
department	VARCHAR(255)	Department name in Workday
hire_date	DATE	Hire date in Workday

internal_employees

Column	Type	Description
employee_idPK	INT	Internal employee identifier
full_name	VARCHAR(255)	Employee full name as stored internally
department	VARCHAR(255)	Department name in the internal system
hire_date	DATE	Hire date in the internal system

Your question is Cleaning and Transforming Disparate 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