SnowPro Associate: Platform Question 225
Single answer○ INSERTA data engineer needs to load a small set of corrected customer records from one Snowflake table into another existing table. The target table CUSTOMER_MASTER has the columns (CUSTOMER_ID, FULL_NAME, EMAIL, STATUS). The source table CUSTOMER_FIXES has the columns (EMAIL, CUSTOMER_ID, STATUS, FULL_NAME) in a different order. The engineer wants to insert all rows from CUSTOMER_FIXES into CUSTOMER_MASTER without mismatching values across columns. Which approach should be used?
- A
Run INSERT INTO CUSTOMER_MASTER SELECT * FROM CUSTOMER_FIXES;
- B
Run INSERT INTO CUSTOMER_MASTER (CUSTOMER_ID, FULL_NAME, EMAIL, STATUS) SELECT CUSTOMER_ID, FULL_NAME, EMAIL, STATUS FROM CUSTOMER_FIXES;
- C
Run UPDATE CUSTOMER_MASTER SET * = (SELECT * FROM CUSTOMER_FIXES);
- D
Run MERGE INTO CUSTOMER_MASTER USING CUSTOMER_FIXES ON CUSTOMER_MASTER.CUSTOMER_ID = CUSTOMER_FIXES.CUSTOMER_ID WHEN NOT MATCHED THEN INSERT VALUES (CUSTOMER_FIXES.CUSTOMER_ID, CUSTOMER_FIXES.FULL_NAME, CUSTOMER_FIXES.EMAIL, CUSTOMER_FIXES.STATUS);
Show answer and explanation
Correct answer: B
Explanation
In Snowflake, an INSERT statement maps values by position unless the target column list and corresponding SELECT list are explicitly defined. When source and target tables have the same columns in a different order, using SELECT * can cause data to land in the wrong columns. Best practice is to specify the target columns and provide the source columns in the exact intended order. This aligns with Snowflake SQL DML behavior for INSERT INTO ... SELECT. MERGE is useful for synchronized loading or upsert scenarios, but for a straightforward insert from one table to another, an explicit INSERT with column lists is the most appropriate and least error-prone solution.
- A. Incorrect.
This is incorrect because INSERT ... SELECT * relies on positional column matching, not column name matching. Since the source table columns are ordered differently from the target table, values would be inserted into the wrong target columns. This is a common mistake when moving data between tables with the same column names but different physical column order.
- B. Correct.
This is correct because explicitly listing the target columns and selecting the source columns in the matching order ensures the data is inserted into the intended target columns. This is the recommended and safest approach when source and target column orders differ.
- C. Incorrect.
This is incorrect because UPDATE modifies existing rows and does not perform an insert of rows from one table into another in this manner. Also, Snowflake does not support a generic SET * = (SELECT * ...) syntax for bulk row insertion or update across tables.
- D. Incorrect.
This is incorrect for this scenario because although MERGE can insert rows when they do not already exist, it is unnecessary complexity when the requirement is simply to insert all rows from one table into another. MERGE is typically used for upsert logic or conditional insert/update/delete operations based on matching criteria.