Deduplication and Incremental Updates | Dataford Interview Questions

Given a specific table schema, write the logic to handle deduplication and incremental updates.

Use the source_orders table as the incoming data and update fact_orders. For each order, retain the newest source record, then insert new orders or update existing orders only when the incoming updated_at is newer.

Output

Return changed rows with order_id, customer_id, status, amount, and updated_at.
Return one row per inserted or updated order, ordered by order_id ascending.

Schema

source_orders

Column	Type	Description
ingestion_idPK	INT	Unique identifier for the incoming record
order_id	INT	Business identifier of the order
customer_id	VARCHAR(20)	Customer identifier associated with the order
status	VARCHAR(20)	Current order status
amount	DECIMAL(10,2)	Order amount
updated_at	TIMESTAMP	Source modification timestamp

fact_orders

Column	Type	Description
order_idPK	INT	Business identifier of the order
customer_id	VARCHAR(20)	Customer identifier associated with the order
status	VARCHAR(20)	Current order status
amount	DECIMAL(10,2)	Order amount
updated_at	TIMESTAMP	Latest applied modification timestamp

Your question is Deduplication and Incremental Updates. 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