Home / DE Associate practice test / Data Transformation and Modeling / Question

Databricks Data Engineer Associate · free practice question

A data engineer receives a daily batch of customer records containing both brand-new customers and updates to existing ones, keyed by customer_id. They must…

Exam-style question on Data Transformation and Modeling from the free DE Associate practice test, with the answer and a full explanation below.

Updated November 10, 2026

Data Transformation and Modeling

A data engineer receives a daily batch of customer records containing both brand-new customers and updates to existing ones, keyed by customer_id. They must insert the new customers and overwrite changed attributes for existing ones in a single atomic Delta operation. Which approach fits?

  1. ARun INSERT INTO for the whole batch and rely on a PRIMARY KEY constraint on customer_id to reject rows whose key already exists, leaving just the new customers appended to the target table.
  2. BRun DELETE for target rows whose customer_id appears in the batch and then INSERT INTO for the full batch, as two statements, on the view that Delta has no single conditional upsert command.
  3. CRun COPY INTO with force = true over the batch files, relying on the command to match customer_id values, overwrite the existing customers' rows and insert the new customers in one load.
  4. DRun MERGE INTO target USING source ON target.customer_id = source.customer_id WHEN MATCHED THEN UPDATE SET * WHEN NOT MATCHED THEN INSERT *, applying updates and inserts in one transaction.
  5. ERun CREATE OR REPLACE TABLE from the incoming batch, relying on the command to merge rows by customer_id and keep the unchanged historical customers that are absent from today's delivery.
Answer & explanation

Correct answer: D

WHY D: MERGE INTO matches on customer_id, updating matched rows and inserting unmatched ones in a single atomic upsert. This is the standard CDC/SCD pattern.

WHY NOT A: Delta does not enforce primary-key uniqueness (the constraint is informational), so INSERT INTO would append duplicates rather than reject them, and existing customers would not be updated.

WHY NOT B: Delete-then-insert can produce the right rows, but it is two statements rather than one atomic operation, and the premise is false: MERGE combines conditional inserts and updates in one statement.

WHY NOT C: COPY INTO has no key-based upsert. It loads files (force = true simply reloads files already loaded) and does not update existing rows by key.

WHY NOT E: CREATE OR REPLACE TABLE overwrites the entire table with the batch, dropping historical rows that are not in today's delivery rather than preserving them.

Take the full DE Associate practice test free →

Would you pass today?

This question is one of 45 in the free, timed Databricks Data Engineer Associate practice test. Take it and get a section-by-section score.

Start the free test →