Databricks-DE-Assoc Data Transformation and Modeling Practice Question
A data engineer is implementing a Type 2 slowly changing dimension in Delta Lake for a customers table. The table has columns customer_id, name, address, effective_date, end_date, and is_current. When a customer's address changes, the engineer wants to expire the existing current row and insert a new current row in a single atomic operation. Which Delta Lake feature should the engineer use?
⚠ Common exam trap
The trap here is assuming that separate DELETE and INSERT statements or a full table overwrite are equivalent to a single MERGE, when only MERGE provides atomic expire-and-insert semantics.
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 the customers table using a source of changed records, with WHEN MATCHED UPDATE to set end_date and is_current on the existing row and WHEN NOT MATCHED INSERT to add the new version
A Type 2 dimension change requires atomically expiring the current row and inserting a new current row. Delta Lake MERGE INTO expresses both actions in one statement, and its ACID transaction guarantees that readers see either the old state or the new state, never a partial update. INSERT OVERWRITE, separate DELETE and INSERT, and OPTIMIZE do not provide the same incremental, atomic behavior.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Use OPTIMIZE with ZORDER BY customer_id to reorganize the table so that new versions are placed next to old versions
Why it's wrong here
OPTIMIZE and ZORDER improve file layout and data skipping for reads; they do not modify row values or implement the expire-and-insert semantics of a Type 2 dimension. Using OPTIMIZE here would leave the dimension rows unchanged, so the address change would never be recorded as a new version.
- ✗
Use INSERT OVERWRITE to replace the entire customers table with a new snapshot that includes the expired and new rows
Why it's wrong here
INSERT OVERWRITE rewrites the entire table, which is expensive and destroys the history of unaffected customers unless the full snapshot is carefully reconstructed. It also makes concurrent reads see a complete replacement rather than an incremental change. For a Type 2 dimension, MERGE is the appropriate incremental, atomic operation.
- ✓
MERGE INTO the customers table using a source of changed records, with WHEN MATCHED UPDATE to set end_date and is_current on the existing row and WHEN NOT MATCHED INSERT to add the new version
Why this is correct
MERGE INTO supports updating matched rows and inserting unmatched rows in one atomic transaction, which is exactly what a Type 2 dimension requires: expire the current row and add the new version. Because Delta Lake provides ACID guarantees, readers never see a state where the old row is expired but the new row is missing.
- ✗
Use DELETE followed by INSERT in two separate statements to expire the old row and add the new one
Why it's wrong here
Two separate statements are not atomic as a unit; a failure between them leaves the table with the old row deleted and the new row missing, breaking the dimension's history. Even wrapping them in a transaction is less efficient than a single MERGE, which expresses the expire-and-insert logic declaratively and is optimized by Delta Lake.
About these practice questions
This Databricks-DE-Assoc question is part of Courseiva's 276-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-Assoc 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-Assoc exam.