SnowPro Associate: Platform Question 228
Single answer○ INSERTA data engineer needs to load new rows from a staging table into a target table in Snowflake. The target table SALES has columns in this order: SALE_ID, CUSTOMER_ID, AMOUNT, SALE_DATE. The staging table STG_SALES was created separately and has the columns in this order: CUSTOMER_ID, SALE_ID, SALE_DATE, AMOUNT. To avoid loading values into the wrong columns when inserting all rows from STG_SALES into SALES, what is the best approach?
- A
Run INSERT INTO SALES SELECT * FROM STG_SALES;
- B
Run INSERT INTO SALES (SALE_ID, CUSTOMER_ID, AMOUNT, SALE_DATE) SELECT SALE_ID, CUSTOMER_ID, AMOUNT, SALE_DATE FROM STG_SALES;
- C
Run COPY INTO SALES FROM STG_SALES;
- D
Run INSERT OVERWRITE INTO SALES SELECT * FROM STG_SALES;
Show answer and explanation
Correct answer: B
Explanation
In Snowflake, INSERT statements load data into target columns based on positional mapping within the statement, not automatic matching by column name. When source and target tables have different column orders, using SELECT * is risky and can silently insert incorrect values into the wrong columns. The best practice is to explicitly specify the target column list and write the SELECT list in the same intended order. This improves correctness, readability, and maintainability. Snowflake documentation for INSERT emphasizes standard SQL INSERT semantics, and Snowflake best practices generally discourage SELECT * in production ETL patterns when schema differences or future schema changes could cause data quality issues.
- A. Incorrect.
Incorrect. INSERT ... SELECT * maps values by column position, not by column name. Because the source and target tables have different column orders, this can place CUSTOMER_ID values into SALE_ID, SALE_ID into CUSTOMER_ID, and so on. This is a common mistake when using SELECT * for inserts between tables with different schemas or column ordering.
- B. Correct.
Correct. Explicitly listing the target columns and selecting the source columns in the matching logical order is the safest and recommended approach. Snowflake INSERT statements map the selected values to the target columns by position in the statement, so specifying both sides clearly avoids accidental misalignment and makes the SQL easier to maintain.
- C. Incorrect.
Incorrect. COPY INTO is used to load data from staged files into a table, not directly from another table. Since STG_SALES is described as a staging table rather than an external or internal file stage, COPY INTO is not the appropriate command for this scenario.
- D. Incorrect.
Incorrect. INSERT OVERWRITE replaces the contents of the target table with the results of the query, which is not the requirement here. In addition, using SELECT * would still risk column-order mismatches. This option combines an inappropriate overwrite behavior with unsafe column selection.