SnowPro Associate: Platform Question 226
Single answer○ INSERTA data engineer needs to load only new web order records from a staging table into a target table in Snowflake. The target table, ORDERS, has columns (ORDER_ID, CUSTOMER_ID, ORDER_TOTAL, ORDER_TS). The staging table, STG_ORDERS, contains the same columns plus a LOAD_BATCH_ID column. The engineer wants to insert rows from the most recent batch only, while avoiding errors caused by column order changes in the staging table. Which SQL statement is the best choice?
- A
INSERT INTO ORDERS SELECT * FROM STG_ORDERS WHERE LOAD_BATCH_ID = (SELECT MAX(LOAD_BATCH_ID) FROM STG_ORDERS);
- B
INSERT INTO ORDERS (ORDER_ID, CUSTOMER_ID, ORDER_TOTAL, ORDER_TS) SELECT ORDER_ID, CUSTOMER_ID, ORDER_TOTAL, ORDER_TS FROM STG_ORDERS WHERE LOAD_BATCH_ID = (SELECT MAX(LOAD_BATCH_ID) FROM STG_ORDERS);
- C
INSERT OVERWRITE INTO ORDERS SELECT ORDER_ID, CUSTOMER_ID, ORDER_TOTAL, ORDER_TS FROM STG_ORDERS WHERE LOAD_BATCH_ID = (SELECT MAX(LOAD_BATCH_ID) FROM STG_ORDERS);
- D
INSERT INTO ORDERS VALUES (SELECT ORDER_ID, CUSTOMER_ID, ORDER_TOTAL, ORDER_TS FROM STG_ORDERS WHERE LOAD_BATCH_ID = (SELECT MAX(LOAD_BATCH_ID) FROM STG_ORDERS));
Show answer and explanation
Correct answer: B
Explanation
The best answer is the explicit INSERT INTO ... (column list) SELECT ... pattern. In Snowflake, this is the recommended approach when inserting from one table into another because it avoids problems caused by schema drift, extra columns, or changes in column order. Using SELECT * is especially risky in ETL pipelines when staging tables may contain audit or metadata columns such as LOAD_BATCH_ID. Snowflake supports INSERT INTO
[(column_list)] SELECT ... for inserting query results, and specifying the target column list is a best practice for maintainability and correctness. This question tests practical understanding of INSERT syntax, column mapping, and safe loading patterns commonly used in Snowflake data engineering workflows.- A. Incorrect.
Incorrect. Although this may appear convenient, using SELECT * is risky because it depends on the source column order and includes the extra LOAD_BATCH_ID column from STG_ORDERS. Since ORDERS has only four columns and STG_ORDERS has five, this statement would fail due to a column count mismatch. Even if the counts matched, relying on * is not a best practice for stable INSERT operations.
- B. Correct.
Correct. This statement explicitly lists the target columns and selects the matching source columns in the desired order. That makes the INSERT resilient to source column order changes and excludes the extra LOAD_BATCH_ID column. Filtering on the maximum LOAD_BATCH_ID ensures only the most recent batch is inserted.
- C. Incorrect.
Incorrect. INSERT OVERWRITE replaces the existing contents of the target table or target partitions depending on the platform semantics, and in Snowflake it is not the appropriate choice when the requirement is to append only new rows from the latest batch. This would risk removing previously loaded rows from ORDERS.
- D. Incorrect.
Incorrect. In Snowflake, VALUES is used for literal row value lists, not for inserting the result set of a SELECT query in this format. To insert query results, the correct pattern is INSERT INTO ... SELECT ...