ETL, Data Cleaning, and SQL | Dataford Interview Questions

Describe your experience with ETL, data cleaning, and SQL.

Write a SQL query that separates valid staged customer events from invalid ones, based on the rules below. Use the provided tables to identify which events can be loaded into the target model and which should be rejected.

Output

One row per staged event
Columns: event_id, customer_email, event_date, status, rejection_reason
Include all staged rows
Sort by event_id ascending

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
email	VARCHAR(255)	Customer email address
status	VARCHAR(20)	Customer account status

staged_events

Column	Type	Description
event_idPK	INT	Staged event identifier
customer_email	VARCHAR(255)	Email captured in the staging feed
event_date	DATE	Event date from the source system
amount	NUMERIC(10,2)	Event amount

Your question is ETL, Data Cleaning, and SQL. 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