DAA-C01 Question 81
Single answerPerform general DML (INSERT, UPDATE, and DELETE)A data analyst maintains a curated SALES_FACT table in Snowflake. Each morning, a cleaned incremental file is loaded into a temporary STG_SALES_DELTA table. The business requires the analyst to apply three changes from the staging table to SALES_FACT in one repeatable SQL operation: insert new orders, update existing orders when AMOUNT or STATUS changed, and remove rows from SALES_FACT when the staging row is marked IS_DELETED = TRUE. The analyst wants to avoid running separate INSERT, UPDATE, and DELETE statements because that would increase orchestration complexity and may scan the target multiple times. Which approach best meets these requirements?
- A
Use a single MERGE statement on SALES_FACT with matched clauses for UPDATE and DELETE, and a not-matched clause for INSERT.
- B
Run an INSERT OVERWRITE on SALES_FACT from a join between SALES_FACT and STG_SALES_DELTA so all changes are applied in one statement.
- C
Create a stream on SALES_FACT and query the stream directly to automatically apply inserts, updates, and deletes from STG_SALES_DELTA.
- D
Use COPY INTO SALES_FACT from STG_SALES_DELTA with ON_ERROR = CONTINUE so changed rows are updated and deleted rows are removed.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use MERGE. In Snowflake, MERGE supports combining conditional UPDATE, DELETE, and INSERT logic into a single DML statement, making it well suited for applying incremental changes from a staging table to a curated target. This matches the scenario requirement to handle new rows, changed rows, and logical deletions in one repeatable operation while avoiding multiple passes over the target. A representative pattern is: MERGE INTO SALES_FACT t USING STG_SALES_DELTA s ON t.ORDER_ID = s.ORDER_ID WHEN MATCHED AND s.IS_DELETED = TRUE THEN DELETE WHEN MATCHED AND (t.AMOUNT <> s.AMOUNT OR t.STATUS <> s.STATUS) THEN UPDATE SET AMOUNT = s.AMOUNT, STATUS = s.STATUS WHEN NOT MATCHED AND s.IS_DELETED = FALSE THEN INSERT (...). This aligns with Snowflake SQL command behavior documented for MERGE, whereas COPY INTO is for loading data, streams expose change data rather than applying it, and INSERT OVERWRITE is a table replacement approach rather than an efficient incremental synchronization pattern.
- A. Correct.
Correct. MERGE is the Snowflake DML statement designed to conditionally INSERT, UPDATE, and DELETE rows in a target table based on matches with a source dataset. In this scenario, the analyst can match on the business key such as ORDER_ID, use WHEN MATCHED AND IS_DELETED = TRUE THEN DELETE, use another WHEN MATCHED condition to UPDATE changed values, and use WHEN NOT MATCHED AND IS_DELETED = FALSE THEN INSERT. This is the most direct and maintainable way to apply mixed row-level changes from a staging table in a single operation.
- B. Incorrect.
Incorrect. INSERT OVERWRITE replaces the contents of an existing table with the result of a query. While it is a valid DML command in Snowflake, it is intended for replacing the full target contents, not for targeted row-level synchronization of inserts, updates, and deletes from a delta set. Using it here would require rebuilding the full target result set each time, which does not align with the requirement to efficiently apply incremental changes.
- C. Incorrect.
Incorrect. A stream records change data for a source object and is commonly used to capture CDC-style changes for downstream consumption. However, creating a stream on SALES_FACT does not apply changes from STG_SALES_DELTA into SALES_FACT. Streams do not automatically synchronize one table into another; they expose change records that must still be consumed by DML such as MERGE.
- D. Incorrect.
Incorrect. COPY INTO loads data from staged files into a table. It does not perform row-level UPDATE or DELETE logic against existing rows in the target table. ON_ERROR controls load error handling, not change application semantics. Someone might choose this option because the source data originated from a file, but once the delta is in STG_SALES_DELTA, COPY INTO is not the correct mechanism for reconciling target rows.