SnowPro Associate: Platform Question 272
Single answer● Use INSERT statements to load dataA data engineer needs to append a small set of corrected customer records from a staging table into the production table. The production table PROD_CUSTOMERS has columns (CUSTOMER_ID NUMBER, FULL_NAME STRING, EMAIL STRING, UPDATED_AT TIMESTAMP_NTZ). The staging table STG_CUSTOMER_FIXES has the same four columns plus an extra column LOAD_FILE_NAME STRING. The engineer wants to use a single SQL statement to insert only the matching four columns into PROD_CUSTOMERS without changing the production table structure. Which statement should be used?
- A
INSERT INTO PROD_CUSTOMERS SELECT * FROM STG_CUSTOMER_FIXES;
- B
INSERT INTO PROD_CUSTOMERS (CUSTOMER_ID, FULL_NAME, EMAIL, UPDATED_AT) SELECT CUSTOMER_ID, FULL_NAME, EMAIL, UPDATED_AT FROM STG_CUSTOMER_FIXES;
- C
INSERT OVERWRITE INTO PROD_CUSTOMERS SELECT CUSTOMER_ID, FULL_NAME, EMAIL, UPDATED_AT FROM STG_CUSTOMER_FIXES;
- D
COPY INTO PROD_CUSTOMERS FROM STG_CUSTOMER_FIXES;
Show answer and explanation
Correct answer: B
Explanation
The correct answer is Option 2 because INSERT INTO ... (column list) SELECT ... is the proper way to append rows from one table to another when the source contains extra columns. In Snowflake, INSERT statements require that the number and compatible types of selected expressions align with the target columns being inserted. Explicitly listing target columns is a best practice because it prevents errors caused by schema differences or column-order assumptions. Option 1 fails because SELECT * includes the extra LOAD_FILE_NAME column. Option 3 is inappropriate because the requirement is to append, not replace data. Option 4 is incorrect because COPY INTO loads from staged files, not directly from another table. This aligns with Snowflake SQL DML guidance for INSERT and general data loading best practices.
- A. Incorrect.
Incorrect. Using SELECT * would attempt to return all columns from STG_CUSTOMER_FIXES, including LOAD_FILE_NAME. Since PROD_CUSTOMERS has only four columns, the column count would not match and the INSERT would fail unless the source query returned exactly the target column list. This is a common mistake when source and target schemas are similar but not identical.
- B. Correct.
Correct. This explicitly lists the target columns and selects the corresponding four source columns from the staging table. This is the recommended approach when loading data with INSERT statements because it avoids dependency on column order, ignores extra source columns, and makes the mapping clear and maintainable.
- C. Incorrect.
Incorrect. INSERT OVERWRITE replaces the contents of the target table or target partitions depending on platform semantics, and it is not the right choice when the requirement is to append corrected records. In Snowflake, the intent here is to add rows, not overwrite existing production data. Choosing this option reflects confusion between append and replace operations.
- D. Incorrect.
Incorrect. COPY INTO is used to load data from staged files into a table, not from one table directly into another table. Since the source is an existing staging table, INSERT ... SELECT is the appropriate SQL pattern. This distractor targets the misconception that COPY INTO is the general-purpose method for all Snowflake data loads.