SnowPro Associate: Platform Question 275
Single answer● Use INSERT statements to load dataA data engineer needs to load a few corrected rows from a staging table into a target table in Snowflake. The target table SALES has columns in this order: (ORDER_ID NUMBER, CUSTOMER_ID NUMBER, ORDER_DATE DATE, AMOUNT NUMBER(10,2)). The staging table SALES_FIXES contains the same data but its columns are ordered as: (CUSTOMER_ID NUMBER, ORDER_ID NUMBER, AMOUNT NUMBER(10,2), ORDER_DATE DATE). The engineer wants to use a single SQL statement to insert all rows correctly without changing the table definitions. Which statement should be used?
- A
INSERT INTO SALES SELECT * FROM SALES_FIXES;
- B
INSERT INTO SALES (ORDER_ID, CUSTOMER_ID, ORDER_DATE, AMOUNT) SELECT ORDER_ID, CUSTOMER_ID, ORDER_DATE, AMOUNT FROM SALES_FIXES;
- C
INSERT OVERWRITE INTO SALES SELECT ORDER_ID, CUSTOMER_ID, ORDER_DATE, AMOUNT FROM SALES_FIXES;
- D
COPY INTO SALES FROM SALES_FIXES;
Show answer and explanation
Correct answer: B
Explanation
When using INSERT statements in Snowflake to load data from one table into another, column mapping is determined by position unless you explicitly define the target columns and select the source columns in the intended order. In real-world workloads, relying on SELECT * is risky because source and target schemas may have different column ordering. Best practice is to specify the target column list and use a SELECT that returns columns in the exact order required. COPY INTO is for loading staged files, while INSERT INTO ... SELECT is the standard approach for loading from existing tables or query results. Snowflake documentation for INSERT describes inserting from queries and emphasizes proper column alignment between target and source expressions.
- A. Incorrect.
Incorrect. INSERT ... SELECT * relies on the positional order of the selected columns unless the source query explicitly rearranges them. Because SALES_FIXES stores columns in a different order than SALES, this statement would attempt to place CUSTOMER_ID into ORDER_ID, ORDER_ID into CUSTOMER_ID, AMOUNT into ORDER_DATE, and ORDER_DATE into AMOUNT, leading to incorrect mapping or type conversion errors.
- B. Correct.
Correct. This statement explicitly lists the target columns and selects the matching source columns by name in the correct order. That is the safest and recommended approach when the source and target column orders differ. It loads all rows from SALES_FIXES into SALES using standard INSERT semantics.
- C. Incorrect.
Incorrect. INSERT OVERWRITE is not the right choice for this requirement because the engineer needs to load corrected rows into the existing target table, not replace all existing data in it. Even though the selected columns are ordered correctly, OVERWRITE would remove existing data in the table before inserting the new result set, which changes the business outcome.
- D. Incorrect.
Incorrect. COPY INTO is used to load data from staged files such as internal or external stages, not directly from another table. Since SALES_FIXES is a table, the appropriate pattern is INSERT INTO ... SELECT ...