UPSERT Incremental Load | Dataford Interview Questions

Write a query to perform incremental data loading from a staging table to a target dimension table, ensuring that you handle both inserts and updates (UPSERT).

Use the provided staging and target tables. For duplicate business keys in staging, only the newest record should be considered. Do not overwrite a target row with an older staging record.

Output

Return one row for each inserted or updated dimension record.
Include customer_id, customer_name, email, status, and updated_at.
Order by customer_id ascending.

Schema

dim_customer_staging

Column	Type	Description
customer_id	INT	Business key for the customer
customer_name	VARCHAR(100)	Customer name from the source batch
email	VARCHAR(255)	Customer email address
status	VARCHAR(20)	Current customer status
updated_at	TIMESTAMP	Source modification timestamp
load_idPK	INT	Unique staging record identifier and tie-breaker

dim_customer

Column	Type	Description
customer_idPK	INT	Business key for the customer
customer_name	VARCHAR(100)	Customer name stored in the dimension
email	VARCHAR(255)	Customer email address
status	VARCHAR(20)	Current customer status
updated_at	TIMESTAMP	Most recent applied source modification timestamp

Your question is UPSERT Incremental Load. 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