SnowPro Associate: Platform Question 37
Single answer○ SQLA data engineer needs to load customer records from a staging table into a target table in Snowflake. The target table CUSTOMER_DIM should contain one row per CUSTOMER_ID. If a CUSTOMER_ID already exists, the engineer must update the customer's EMAIL and STATUS. If the CUSTOMER_ID does not exist, a new row should be inserted. Which SQL statement is the most appropriate solution?
- A
MERGE INTO CUSTOMER_DIM t USING STG_CUSTOMER s ON t.CUSTOMER_ID = s.CUSTOMER_ID WHEN MATCHED THEN UPDATE SET EMAIL = s.EMAIL, STATUS = s.STATUS WHEN NOT MATCHED THEN INSERT (CUSTOMER_ID, EMAIL, STATUS) VALUES (s.CUSTOMER_ID, s.EMAIL, s.STATUS)
- B
UPDATE CUSTOMER_DIM SET EMAIL = s.EMAIL, STATUS = s.STATUS FROM STG_CUSTOMER s WHERE CUSTOMER_DIM.CUSTOMER_ID = s.CUSTOMER_ID; INSERT INTO CUSTOMER_DIM (CUSTOMER_ID, EMAIL, STATUS) SELECT CUSTOMER_ID, EMAIL, STATUS FROM STG_CUSTOMER
- C
INSERT OVERWRITE INTO CUSTOMER_DIM SELECT CUSTOMER_ID, EMAIL, STATUS FROM STG_CUSTOMER
- D
COPY INTO CUSTOMER_DIM FROM STG_CUSTOMER FILE_FORMAT = (TYPE = CSV)
Show answer and explanation
Correct answer: A
Explanation
The correct answer is the MERGE statement because Snowflake supports MERGE for conditional DML operations that combine UPDATE and INSERT logic in one command. This is the standard SQL pattern for implementing upserts when matching records should be updated and non-matching records inserted. In Snowflake documentation, MERGE is specifically described for synchronizing two tables based on matching conditions. By contrast, UPDATE plus INSERT requires careful filtering to avoid duplicates, INSERT OVERWRITE replaces data rather than performing row-level reconciliation, and COPY INTO is intended for loading from staged files, not table-to-table upserts. For SnowPro Associate, candidates should recognize MERGE as the best-practice SQL solution for this common warehouse-loading scenario.
- A. Correct.
Correct. MERGE is designed for this exact upsert pattern in Snowflake SQL. It compares source and target rows using the specified join condition, updates matching rows, and inserts non-matching rows in a single statement. This is the most appropriate and reliable SQL approach when existing rows must be updated and new rows inserted.
- B. Incorrect.
Incorrect. The UPDATE portion would modify existing matching rows, but the following INSERT statement would insert all rows from STG_CUSTOMER again, including rows that already exist in CUSTOMER_DIM, unless additional filtering logic were added. This could create duplicates or constraint issues. A candidate might choose this because it appears to handle both update and insert operations, but as written it does not correctly implement the required one-row-per-CUSTOMER_ID logic.
- C. Incorrect.
Incorrect. INSERT OVERWRITE replaces the contents of the target table or affected partition scope rather than selectively updating existing rows and inserting only new ones. In this scenario, the requirement is to preserve the target table while applying row-level upsert logic. A candidate might pick this if they confuse table replacement with incremental loading.
- D. Incorrect.
Incorrect. COPY INTO loads data from staged files into a Snowflake table; it is not used to compare rows between two tables and perform conditional updates/inserts. This option reflects a common misconception between data loading commands and DML operations. Since the source is a staging table, not staged files, COPY INTO is not the appropriate SQL command.