DAA-C01 Question 82
Single answerPerform general DML (INSERT, UPDATE, and DELETE)A retail analytics team maintains a dimension table named DIM_CUSTOMER in Snowflake. Each night, a staging table STG_CUSTOMER contains the latest full extract from the source system. The team must apply three changes to DIM_CUSTOMER in a single SQL statement: insert customers that do not yet exist, update existing customers when attributes such as EMAIL or STATUS have changed, and delete customers that are no longer present in the source extract. Which approach best meets this requirement?
- A
Use a MERGE statement with MATCHED clauses for UPDATE and DELETE, and a NOT MATCHED clause for INSERT, joining DIM_CUSTOMER to STG_CUSTOMER on CUSTOMER_ID.
- B
Use an INSERT OVERWRITE statement on DIM_CUSTOMER from STG_CUSTOMER, because it updates changed rows, inserts new rows, and deletes missing rows automatically while preserving table history.
- C
Use a single UPDATE statement with a LEFT JOIN to STG_CUSTOMER, because Snowflake UPDATE can also insert rows that are not matched and remove rows that no longer exist in the source.
- D
Use a DELETE statement first to remove all rows from DIM_CUSTOMER that are not in STG_CUSTOMER, then rely on Time Travel to automatically reinsert new rows and update changed rows.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use MERGE. In Snowflake, MERGE is designed for conditional DML against a target table using a source dataset. It allows a single statement to evaluate matching and nonmatching rows and then perform UPDATE, DELETE, or INSERT actions accordingly. This aligns directly with the scenario: synchronize DIM_CUSTOMER with the latest STG_CUSTOMER extract by updating changed customers, inserting new ones, and deleting customers no longer present in the source. This is a common real-world pattern for dimension maintenance and source-to-target synchronization. By contrast, UPDATE only changes existing rows, DELETE only removes rows, and Time Travel is for querying or restoring historical data rather than applying new DML changes. Snowflake documentation for MERGE, UPDATE, DELETE, and INSERT describes these command boundaries and supports MERGE as the appropriate single-statement solution for this type of synchronization.
- A. Correct.
Correct. In Snowflake, MERGE is the standard DML command for synchronizing a target table with a source dataset in one statement. It supports conditional WHEN MATCHED THEN UPDATE, WHEN MATCHED THEN DELETE, and WHEN NOT MATCHED THEN INSERT logic based on a join condition such as CUSTOMER_ID. This is the most appropriate approach when the business requirement is to upsert and remove rows in a single operation.
- B. Incorrect.
Incorrect. INSERT OVERWRITE replaces the contents of a table or partition-like target scope with the result of a query, but it is not the right answer to a requirement specifically asking for general DML logic in one statement that conditionally updates, inserts, and deletes matching rows. It does not perform row-by-row update logic in the way MERGE does, and the statement about preserving table history as part of its purpose is misleading.
- C. Incorrect.
Incorrect. UPDATE only modifies existing rows in the target table. It cannot insert new nonmatching rows, and it cannot delete rows that are absent from the source. A candidate might choose this option if they confuse joined UPDATE syntax with broader synchronization capabilities, but those capabilities belong to MERGE, not UPDATE.
- D. Incorrect.
Incorrect. DELETE can remove rows, but Time Travel is a recovery feature used to access or restore historical data; it does not automatically apply inserts or updates from a staging table. This option mixes a valid DML operation with an incorrect assumption about what Time Travel does.