Ingesting CustomerMaster as a Type 2 slowly changing dimension, which step ensures you keep the latest record for each Customer ID?

Ingesting CustomerMaster as a Type 2 slowly changing dimension, which step ensures you keep the latest record for each Customer ID?

• A. Use a flag IsCurrent and filter
• B. Sort by EffectiveDate descending and deduplicate by Customer ID
• C. Max of EffectiveDate per Customer ID
• D. Use ROW_NUMBER partitioning and filter

Answer: (B)

When you model a CustomerMaster as a Type 2 slowly changing dimension, you keep multiple versions of a customer, each with its own EffectiveDate. To obtain the current view, you want the most recent version for every Customer ID. Sorting by EffectiveDate in descending order and deduplicating by Customer ID does exactly that: you retain the first row per customer, which is the version with the latest effective date. This gives one row per Customer ID representing the current state. If there are ties on the effective date, add a deterministic tie-breaker (for example, a surrogate key or version number) to ensure you consistently pick the same row. Other approaches are more prone to errors or require extra steps: using a flag requires maintaining and updating IsCurrent across rows; taking the max of the date tells you the date but not which full row to return; and while ROW_NUMBER would also yield the latest per customer, the described method cleanly captures the intended current row by date alone.

When you model a CustomerMaster as a Type 2 slowly changing dimension, you keep multiple versions of a customer, each with its own EffectiveDate. To obtain the current view, you want the most recent version for every Customer ID. Sorting by EffectiveDate in descending order and deduplicating by Customer ID does exactly that: you retain the first row per customer, which is the version with the latest effective date. This gives one row per Customer ID representing the current state.

If there are ties on the effective date, add a deterministic tie-breaker (for example, a surrogate key or version number) to ensure you consistently pick the same row.

Other approaches are more prone to errors or require extra steps: using a flag requires maintaining and updating IsCurrent across rows; taking the max of the date tells you the date but not which full row to return; and while ROW_NUMBER would also yield the latest per customer, the described method cleanly captures the intended current row by date alone.