Databricks-DE-Pro Data Modelling Practice Question
A retail company is designing a Gold-layer dimension table in Delta Lake for its product catalog. The catalog changes slowly: a product's category is occasionally reclassified, but historical sales fact rows must continue to reflect the category that was valid at the time of each sale. The team wants to avoid duplicating the entire product row for every change. Which Delta Lake modeling technique should the engineer implement?
⚠ Common exam trap
The trap here is assuming that overwriting the category in place is acceptable because the requirement only mentions the current catalog view, when the point-in-time accuracy clause specifically demands versioned history.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Type 2 slowly changing dimension with effective-date and current-flag columns populated via MERGE INTO
Preserving point-in-time accuracy for a slowly changing attribute requires versioned dimension rows with validity ranges and a current flag, which is the defining behavior of a Type 2 slowly changing dimension. A MERGE INTO statement can close the existing row and insert a new one atomically, letting fact rows resolve the correct historical category through an effective-date join or surrogate key.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
A junk dimension that collapses category combinations into a separate table keyed by a surrogate
Why it's wrong here
A junk dimension is used to group low-cardinality flags and status attributes into a single table to reduce fact-table width; it is not a mechanism for versioning a changing attribute over time. Using it here would not preserve the effective-dated history of a product's category, so historical sales could not be resolved to the category valid at transaction time.
- ✗
Type 1 slowly changing dimension implemented with MERGE INTO that overwrites the category column in place
Why it's wrong here
A Type 1 update overwrites the attribute in place, which destroys the prior category value needed to preserve point-in-time accuracy for older fact rows. Because the historical sales facts join to the current dimension row, past transactions would suddenly appear to belong to the new category, breaking the requirement that each sale reflect the category valid at sale time.
- ✗
A Type 3 dimension that adds a previous_category column to the existing product row
Why it's wrong here
Type 3 tracks only a limited number of prior values by adding extra columns to the same row, so it cannot represent an arbitrary number of reclassifications over time. Once a product changes category more than once, the previous_category column is overwritten and the earliest historical state is lost, failing the requirement to preserve point-in-time category for all past sales.
- ✓
Type 2 slowly changing dimension with effective-date and current-flag columns populated via MERGE INTO
Why this is correct
A Type 2 dimension inserts a new versioned row whenever the category changes and closes the prior row with an end date, preserving history. Fact rows can then join on the surrogate key or on the effective-date range valid at the transaction timestamp, so each sale continues to show the correct historical category without duplicating the whole catalog for unchanged attributes.
Visual reference
About these practice questions
This Databricks-DE-Pro question is part of Courseiva's 267-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Databricks exam blueprint
This Databricks-DE-Pro practice question is part of Courseiva's free Databricks certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the Databricks-DE-Pro exam.