SCD Type 2 in Warehouse | Dataford Interview Questions

Explain how you would handle slowly changing dimensions (SCD Type 2) in a cloud data warehouse environment for Roku customer and device attributes.

Write a PostgreSQL query that converts Roku customer-device change events into versioned dimension rows. Preserve only actual attribute changes, assign each version an effective period, identify the current version, and retain events even when device registry metadata is unavailable.

Output

One row per customer-device attribute version.
Include the customer and device attributes, registry metadata, effective_from, effective_to, and is_current.
Order by customer_id, device_id, and effective_from ascending.

Schema

roku_customer_device_events

Column	Type	Description
event_idPK	INT	Unique change event identifier
customer_id	INT	Roku customer identifier
device_id	INT	Roku device identifier
change_ts	TIMESTAMP	Timestamp when the attribute snapshot was recorded
customer_segment	VARCHAR(30)	Customer segment at the event timestamp
subscription_tier	VARCHAR(30)	Customer subscription tier
device_model	VARCHAR(50)	Device model at the event timestamp
device_os	VARCHAR(30)	Roku operating system version
region	VARCHAR(30)	Customer region

roku_device_registry

Column	Type	Description
device_idPK	INT	Registered Roku device identifier
serial_number	VARCHAR(40)	Device serial number
model_family	VARCHAR(40)	Broad device family

Your question is SCD Type 2 in Warehouse. 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