SnowPro Associate: Platform Question 274
Single answer● Use INSERT statements to load dataA data engineer needs to load a small set of manually curated correction records into an existing Snowflake table named SALES_CORRECTIONS with the columns (ORDER_ID NUMBER, REGION STRING, ADJUSTMENT_AMOUNT NUMBER(10,2)). The source values are known at the time the SQL is written, and the engineer wants to insert all rows in a single statement without using stages or files. Which SQL statement best meets this requirement?
- A
INSERT INTO SALES_CORRECTIONS (ORDER_ID, REGION, ADJUSTMENT_AMOUNT) VALUES (101, 'WEST', 25.50), (102, 'EAST', -10.00), (103, 'CENTRAL', 5.25);
- B
COPY INTO SALES_CORRECTIONS FROM VALUES (101, 'WEST', 25.50), (102, 'EAST', -10.00), (103, 'CENTRAL', 5.25);
- C
INSERT VALUES INTO SALES_CORRECTIONS (101, 'WEST', 25.50), (102, 'EAST', -10.00), (103, 'CENTRAL', 5.25);
- D
PUT '101,WEST,25.50\n102,EAST,-10.00\n103,CENTRAL,5.25' @%SALES_CORRECTIONS; INSERT INTO SALES_CORRECTIONS;
Show answer and explanation
Correct answer: A
Explanation
For loading a small set of explicit records that are already known when writing the SQL, Snowflake best practice is to use INSERT INTO with a VALUES clause. Snowflake allows multiple rows to be inserted in one statement by separating row tuples with commas. This is practical for manual corrections, seed data, or limited ad hoc inserts. By contrast, COPY INTO is designed for loading from staged files and is more appropriate for bulk ingestion workflows. The PUT command is used to upload local files to an internal stage before loading, so it is unnecessary and syntactically incorrect for inline literal data. This aligns with Snowflake SQL command usage documented for INSERT and data loading patterns.
- A. Correct.
Correct. Snowflake supports INSERT INTO ... VALUES with multiple row value lists in a single statement. This is the appropriate choice when the data is already known in the SQL text and only a small number of rows need to be added directly to a table.
- B. Incorrect.
Incorrect. COPY INTO is used to load data from staged files, not directly from an inline VALUES list in this format. A candidate might choose this because COPY INTO is a common Snowflake loading command, but it is intended for bulk loading from internal or external stages.
- C. Incorrect.
Incorrect. This is not valid Snowflake SQL syntax. The correct syntax begins with INSERT INTO <table_name> [(column_list)] VALUES (...). A common misconception is that VALUES can appear immediately after INSERT without the INTO keyword and table reference in the proper order.
- D. Incorrect.
Incorrect. PUT uploads files from a client machine to an internal stage; it does not accept raw inline row data as shown here. In addition, the standalone INSERT INTO SALES_CORRECTIONS; statement is incomplete because it lacks either a VALUES clause or a SELECT statement.