Databricks-DE-Assoc Data Transformation and Modeling Practice Question
A data engineer maintains a Delta table where each row represents a customer record, and updates arrive continuously as change data capture events. The engineer needs to apply inserts, updates, and deletes from a staging table into the target table in a single atomic operation, matching records on customer_id. Which Delta Lake operation should be used?
⚠ Common exam trap
The trap here is treating a delete-plus-insert sequence or an append pattern as equivalent to an atomic key-based upsert.
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 target table using the staging table as the source, with matched update/delete clauses and a not-matched insert clause.
MERGE INTO is the Delta Lake primitive designed for upsert and delete semantics in one atomic transaction. By matching on customer_id, the engineer can update or delete matched rows and insert new ones from the staging table, applying CDC events idempotently. This avoids data gaps, prevents duplicate versions, and keeps the target table representing current state for downstream consumers.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Run a DELETE followed by an INSERT on the target table inside the same notebook cell.
Why it's wrong here
Two separate statements are two separate transactions, so concurrent readers could observe the table after the delete but before the insert, producing a temporary data gap. This approach also fails to distinguish matched updates from new inserts efficiently and is not atomic as a single unit of work. MERGE is the intended single-transaction primitive.
- ✓
MERGE INTO the target table using the staging table as the source, with matched update/delete clauses and a not-matched insert clause.
Why this is correct
MERGE INTO supports exactly this pattern: matched rows can be updated or deleted, and unmatched source rows can be inserted, all within one ACID transaction. Matching on customer_id applies CDC changes idempotently, and the operation is atomic so readers never see a partially applied batch.
- ✗
Append the staging rows to the target table and deduplicate later with a window function during reads.
Why it's wrong here
Appending CDC events without applying them leaves the target table with multiple versions per customer and does not honor deletes. Pushing deduplication to read time increases query cost and complexity, and it cannot express a delete unless a tombstone is separately interpreted. The target table would no longer represent current state.
- ✗
Use INSERT OVERWRITE to replace all partitions touched by the staging table with the staged rows.
Why it's wrong here
INSERT OVERWRITE replaces entire partitions or the whole table and cannot selectively update or delete individual matched rows by key. It would also drop target rows that are not present in the current staging batch, causing data loss. This is unsuitable for key-based CDC where only changed records should be affected.
About these practice questions
Courseiva writes every Databricks-DE-Assoc question from scratch — 276 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or 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.