In which of the following scenarios should a data engineer use the MERGE INTO command instead of the INSERT INTO command?

The MERGE INTO command is specifically designed for "upsert" (update or insert) scenarios. It atomically modifies a target Delta table based on a join with a source dataset. Its core strength lies in its conditional logic: it can UPDATE rows that already exist (WHEN MATCHED) and INSERT new rows that do not (WHEN NOT MATCHED). This functionality is essential for maintaining data integrity by preventing the creation of duplicate records based on a specified key, a task that a simple INSERT INTO command cannot perform as it only appends new rows.

A. Changing a table's physical data location is a metadata operation handled by commands like ALTER TABLE ... SET LOCATION, not a DML operation like MERGE INTO.
B. Both MERGE INTO and INSERT INTO can operate on a target external Delta table. The table being external does not dictate the choice between these commands.
C. The lifecycle of the source table (i.e., whether it is deleted after the operation) is a separate workflow step and is irrelevant to the choice between MERGE and INSERT.
E. The source for a MERGE INTO operation can be any table, view, or subquery that produces a set of rows, not exclusively a Delta table.

1. Databricks SQL Language Reference

MERGE INTO: "Merges a set of updates

insertions

and deletions based on a source table into a target Delta table. ... The MERGE operation allows you to combine INSERT

UPDATE

and DELETE operations into a single atomic statement." This highlights the conditional logic used to manage records

which is the basis for preventing duplicates.

2. Databricks Documentation

"Upsert into a Delta table using merge": "You can upsert data from a source table

view

or DataFrame into a target Delta table using the MERGE SQL operation. Delta Lake supports inserts

updates

and deletes in MERGE... The most common use case is to upsert a set of new records into an existing Delta table." This directly addresses the use case of inserting new records while handling existing ones to avoid duplication.

3. Databricks Documentation

"Table deletes

updates

and merges": This document explains that MERGE is an atomic operation that simplifies mixed insert

update

and delete patterns. The example provided shows a typical SCD Type 1 implementation

which inherently relies on matching keys to prevent duplicate entities and update existing ones.