Databricks-DE-Pro Data Modelling Practice Question
A data engineer is designing a Gold layer table that must support slowly changing dimension (SCD) Type 2 for a customer dimension. The source data arrives daily with updates to customer attributes. The engineer wants to implement this using Delta Lake. Which two features or techniques are essential for maintaining SCD Type 2? (Choose two.)
⚠ Common exam trap
Candidates often confuse time travel with SCD Type 2 maintenance; time travel is for reading historical snapshots, not for managing dimension 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
✓
MERGE INTO with condition on business key and effective dates
SCD Type 2 requires a mechanism to expire old records and insert new ones, which is achieved with MERGE INTO. Additionally, the dimension table must include columns to track the validity period of each version, such as effective dates and a current flag. Time travel, generated columns, and partitioning are not essential for implementing SCD Type 2.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Using Delta Lake's generated columns for surrogate keys
Why it's wrong here
Generated columns are for automatically computing values based on other columns, but they cannot generate unique surrogate keys for each version. SCD Type 2 typically requires a surrogate key that changes with each new version, which is usually handled via monotonically increasing IDs or hashes. Generated columns are not designed for this purpose.
- ✗
Partitioning the dimension table by effective_start_date
Why it's wrong here
Partitioning by effective_start_date can lead to a large number of partitions and is not a requirement for SCD Type 2. In fact, it may cause performance issues due to small files. SCD Type 2 maintenance focuses on row-level updates and inserts, not on partitioning strategy. Partitioning is an optimization technique, not a core SCD requirement.
- ✓
MERGE INTO with condition on business key and effective dates
Why this is correct
MERGE INTO is essential for SCD Type 2 because it allows updating existing records (e.g., setting end dates) and inserting new versions in a single atomic operation. By joining on the business key and comparing effective dates, you can expire old rows and add new ones. This ensures historical accuracy and ACID compliance in Delta Lake.
- ✗
Delta Lake time travel to query previous versions
Why it's wrong here
Time travel is useful for auditing and reproducing past states, but it is not used to maintain SCD Type 2. SCD Type 2 requires active management of current and historical rows via updates and inserts, not just querying old snapshots. Time travel does not help in constructing the dimension table itself; it is a read-only feature.
- ✓
Adding columns for effective start date, end date, and current flag
Why this is correct
SCD Type 2 requires tracking the validity period of each version. Columns like effective_start_date, effective_end_date, and is_current are fundamental to represent the history. Without them, you cannot distinguish between current and expired records or perform point-in-time joins. These columns are populated during the MERGE operation.
About these practice questions
One of 267 original Databricks-DE-Pro practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. 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.