SCD 2 Query Validation | Dataford Interview Questions

Explain how you implement SCD 2 and write a query to verify the history is correct.

Assume the dimension table stores one row per version of an entity, with current and historical records side by side. Your answer should describe the SCD 2 loading approach and provide a SQL query that checks the version history for correctness.

Output

A brief explanation of the SCD 2 approach.
A PostgreSQL query that validates the history for each business key.
The verification result should flag broken timelines, overlapping effective dates, or multiple current rows.

Schema

dim_customer

Column	Type	Description
customer_skPK	BIGINT	Surrogate key for the dimension row
customer_id	VARCHAR(50)	Business key for the customer
customer_name	VARCHAR(200)	Customer name for the version
city	VARCHAR(100)	Customer city for the version
effective_from	DATE	Start date for this version
effective_to	DATE	End date for this version, null for current row
is_current	BOOLEAN	True for the active version

Your question is SCD 2 Query Validation. Start with the requirements and the one table 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

Explain how you implement SCD 2 and write a query to verify the history is correct.

Output

A brief explanation of the SCD 2 approach.
A PostgreSQL query that validates the history for each business key.
The verification result should flag broken timelines, overlapping effective dates, or multiple current rows.

Schema

dim_customer

Column	Type	Description
customer_skPK	BIGINT	Surrogate key for the dimension row
customer_id	VARCHAR(50)	Business key for the customer
customer_name	VARCHAR(200)	Customer name for the version
city	VARCHAR(100)	Customer city for the version
effective_from	DATE	Start date for this version
effective_to	DATE	End date for this version, null for current row
is_current	BOOLEAN	True for the active version

Your question is SCD 2 Query Validation. Start with the requirements and the one table 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.