Home / DE Professional practice test / Data Modelling / Question

Databricks Data Engineer Professional · free practice question

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…

Exam-style question on Data Modelling from the free DE Professional practice test, with the answer and a full explanation below.

Updated November 16, 2026

Data Modelling Easy

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?

  1. ASCD Type 1: overwrite the existing customer row in place with Delta MERGE (WHEN MATCHED THEN UPDATE), keeping one row per customer that holds the latest address.
  2. BSCD Type 2: use AUTO CDC INTO in Lakeflow Spark Declarative Pipelines, which adds a new row with an effective date range for each change and keeps the previous record.
  3. CSCD Type 3: add previous_address and current_address columns to dim_customer and use Delta MERGE to shift the current address into the previous column on each change.
  4. DSCD Type 0: leave dim_customer unchanged once loaded and rely on the Delta Lake transaction log, which blocks row-level updates and thereby preserves the historical records.
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.

Take the full DE Professional practice test free →

Would you pass today?

This question is one of 60 in the free, timed Databricks Data Engineer Professional practice test. Take it and get a section-by-section score.

Start the free test →