You have a table 'PRODUCT_PRICES' defined as 'CREATE TABLE PRODUCT_PRICES (PRODUCT_ID INT, PRICE LAST UPDATED TIMESTAMP NTZ)'. You want to insert new prices for some products, but only if the new pric

Question

Options

• APerform a 'SELECT statement for each 'PRODUCT_ID to check the existing price, then either
• BUse a stored procedure that iterates through the new data and performs 'INSERT or 'UPDATE
• CUse `MERGE statement to update the 'LAST UPDATED timestamp and insert new records if a
• DFirst 'INSERT all the new prices into a temporary table, then use a 'JOIN' with the original table to
• ECreate a new table with all the product IDs that need to be updated and use 'INSERT

• A
  10% (2)
• B
  5% (1)
• C
  67% (14)
• E
  19% (4)

Explanation

Option C is correct because Snowflake's MERGE statement is purpose-built for exactly this "upsert" pattern - it evaluates a join condition in a single pass and can branch into different DML actions (WHEN MATCHED THEN UPDATE, WHEN NOT MATCHED THEN INSERT), making it both declarative and highly efficient for bulk operations. Options A and B are wrong because row-by-row checking via SELECT loops or stored procedure iteration is an anti-pattern in set-based databases - it generates excessive round-trips and ignores Snowflake's parallel processing engine. Option D is wrong because loading into a temp table and then joining is an extra step that MERGE handles natively in one statement. Option E is wrong for a similar reason - creating an auxiliary table of IDs to drive updates adds unnecessary complexity and DML overhead when MERGE already handles conditional logic inline.

Memory tip: Think of MERGE as a traffic cop at an intersection - it looks at each incoming row, checks if it matches existing traffic (MATCHED), and directs it to either update the lane or the insert lane, all in one coordinated operation. If you ever see "insert or update based on a condition," that's your MERGE signal.

No community discussion yet for this question.