A data team is implementing a star-schema dimensional model for e-commerce analytics. A key dimension table dim_customer changes slowly over time — when a customer updates their home address, the team needs to preserve the full historical record (including the old address) with effective date ranges so that historical sales can always be linked to the correct customer address at the time of purchase. Which Slowly Changing Dimension (SCD) type should they use, and which Delta Lake feature natively supports it?
Answer & explanation
Correct answer: B
WHY B: The requirement to preserve full historical records with effective date ranges so that historical fact joins reflect the dimension state at the time of the event is the canonical definition of SCD Type 2. Under SCD Type 2, when a customer's address changes, a new row is inserted for that customer with a new surrogate key, effective_start_date, effective_end_date, and an is_current flag, and the old row's end date is updated to mark it as expired. Historical fact rows, which reference the old surrogate key, continue to join to the historical address. Databricks supports this natively: 'Lakeflow Spark Declarative Pipelines has native support for tracking and applying SCD Type 1 and Type 2. Use AUTO CDC ... INTO with Lakeflow Spark Declarative Pipelines to ensure that out of order records are handled correctly when processing CDC feeds.'
WHY NOT A: SCD Type 1 overwrites the existing row in place (via MERGE WHEN MATCHED THEN UPDATE). It is the simplest result but destroys historical attribute values: with one row per customer holding the newest address, a historical sales order cannot be attributed to the old address.
WHY NOT C: SCD Type 3 adds a fixed number of 'previous value' columns (e.g., previous_address, current_address) to the dimension row. It retains exactly ONE level of history: after three address changes, the two most recent addresses are preserved. That does not meet the requirement for full history with effective date ranges.
WHY NOT D: SCD Type 0 means no changes are applied to dimension records after the initial load, so an address change is not reflected at all. In addition, the Delta transaction log does not block row-level updates: Delta supports UPDATE, MERGE, and DELETE, and ACID compliance means these operations are atomic and consistent, not that they are blocked.