Databricks-DE-Pro Data Modelling Practice Question
A healthcare company uses a Databricks Lakehouse. The Silver layer contains a table patient_visits that is updated with late-arriving data. The table is partitioned by visit_date. The data engineering team needs to efficiently merge new data that may include updates to existing records and inserts of new records. They want to minimize the impact on existing data and ensure ACID compliance. Which Delta Lake operation should they use?
⚠ Common exam trap
The trap here is using INSERT OVERWRITE or a delete-then-insert pattern, which can lead to data loss or non-atomic operations.
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 patient_visits USING new_data ON patient_visits.visit_id = new_data.visit_id WHEN MATCHED THEN UPDATE SET * WHEN NOT MATCHED THEN INSERT *
The MERGE statement is designed for upserts, allowing updates to existing records and inserts of new records in one atomic operation. It minimizes data rewriting by only touching affected files and maintains ACID compliance. Other options either overwrite data, are non-atomic, or do not apply changes.
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 Delta Lake change data feed to apply changes.
Why it's wrong here
Change data feed is used to track row-level changes for downstream consumers, not to apply updates to the table itself. It does not provide a mechanism to merge new data into the target table. Thus, it is not the appropriate operation for this scenario.
- ✗
INSERT OVERWRITE patient_visits SELECT * FROM new_data
Why it's wrong here
INSERT OVERWRITE replaces the entire table or specified partitions with new data, which would delete existing records not present in new_data. This is not suitable for incremental updates and would cause data loss. It also does not handle updates to existing records.
- ✗
DELETE FROM patient_visits WHERE visit_id IN (SELECT visit_id FROM new_data); INSERT INTO patient_visits SELECT * FROM new_data
Why it's wrong here
This two-step approach deletes matching records and then inserts all new data. It is not atomic; if the insert fails, the deletes are already committed, leading to data loss. It also rewrites entire partitions and is less efficient than a single MERGE operation.
- ✓
MERGE INTO patient_visits USING new_data ON patient_visits.visit_id = new_data.visit_id WHEN MATCHED THEN UPDATE SET * WHEN NOT MATCHED THEN INSERT *
Why this is correct
The MERGE operation allows for efficient upserts by matching on visit_id. It updates existing records and inserts new ones in a single ACID transaction, minimizing data rewriting. This is the standard approach for handling late-arriving data in Delta Lake and ensures atomicity and consistency.
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.