SOL-C01 Question #165: Real Exam Question with Answer & Explanation

Question

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 price is different from the existing price. If the price is the same, you want to update the 'LAST UPDATED' timestamp. Which of the following approaches would be the most efficient in Snowflake to achieve this?

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

Explanation

Option C is the most efficient because the 'MERGE statement is specifically designed for scenarios where you need to conditionally insert or update data based on a join condition. It avoids the overhead of multiple `SELECT statements or a stored procedure iterating through the data. A and B perform row-by- row operations which is slow in Snowflake. Although D is valid, it requires you to create temporary tables, populate and then join against the original table, which takes more time and code. Option E overwrites the whole table which is not needed since we only need to update records if a record for the 'PRODUCT_ID' does not exist.

No community discussion yet for this question.