SnowPro Associate: Platform Question 227
Single answer○ INSERTA data engineer needs to load transformed rows from a staging table into a target table in Snowflake. The target table SALES has columns in this order: ORDER_ID, CUSTOMER_ID, ORDER_TOTAL, ORDER_DATE. The staging table STG_SALES contains the same data but its columns are ordered differently: CUSTOMER_ID, ORDER_ID, ORDER_DATE, ORDER_TOTAL. To avoid loading values into the wrong target columns, which approach should the engineer use?
- A
Use INSERT INTO SALES SELECT * FROM STG_SALES because Snowflake automatically matches columns by name when the table structures are similar.
- B
Use INSERT INTO SALES (ORDER_ID, CUSTOMER_ID, ORDER_TOTAL, ORDER_DATE) SELECT ORDER_ID, CUSTOMER_ID, ORDER_TOTAL, ORDER_DATE FROM STG_SALES.
- C
Use INSERT OVERWRITE SALES SELECT * FROM STG_SALES because OVERWRITE reorders source columns to match the target table definition.
- D
Use MERGE INTO SALES USING STG_SALES ON SALES.ORDER_ID = STG_SALES.ORDER_ID WHEN NOT MATCHED THEN INSERT VALUES (STG_SALES.ORDER_ID, STG_SALES.CUSTOMER_ID, STG_SALES.ORDER_TOTAL, STG_SALES.ORDER_DATE), because MERGE is required whenever source and target column orders differ.
Show answer and explanation
Correct answer: B
Explanation
In Snowflake, INSERT statements load values into target columns based on position unless you explicitly specify the target column list. When source and target tables contain the same logical fields but in different physical column orders, using SELECT * is risky because it can misplace data. The best practice is to name the target columns explicitly and align the SELECT list to that order. INSERT OVERWRITE changes table replacement behavior, not column-mapping behavior. MERGE is appropriate for matched/not-matched logic such as upserts, but it is unnecessary for a straightforward insert from a staging table. This aligns with Snowflake SQL best practices for INSERT syntax and column mapping.
- A. Incorrect.
Incorrect. In an INSERT ... SELECT statement, Snowflake maps selected expressions to target columns by position unless an explicit target column list is provided. Using SELECT * when the source column order differs from the target can place data into the wrong columns.
- B. Correct.
Correct. Providing an explicit target column list and selecting source columns in the intended order is the safest and recommended approach. This avoids positional mismatches and makes the statement resilient to differing source table column order.
- C. Incorrect.
Incorrect. INSERT OVERWRITE replaces the contents of the target table, but it does not solve column-order mismatches by automatically matching columns by name. If SELECT * is used and the source order differs, values can still be misaligned.
- D. Incorrect.
Incorrect. MERGE can be used for upserts and conditional inserts/updates, but it is not required simply because source and target column orders differ. A standard INSERT with an explicit target column list is sufficient and simpler for this use case.