SnowPro Associate: Platform Question 273
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 CUSTOMER_FIXES with columns (CUSTOMER_ID NUMBER, EMAIL VARCHAR, IS_ACTIVE BOOLEAN). The source values are available immediately in the SQL worksheet, and the engineer wants the fastest approach without creating stages or external files. Which SQL statement best meets this requirement?
- A
INSERT INTO CUSTOMER_FIXES (CUSTOMER_ID, EMAIL, IS_ACTIVE) VALUES (101, 'a@example.com', TRUE), (102, 'b@example.com', FALSE), (103, 'c@example.com', TRUE);
- B
COPY INTO CUSTOMER_FIXES FROM VALUES (101, 'a@example.com', TRUE), (102, 'b@example.com', FALSE), (103, 'c@example.com', TRUE);
- C
PUT 'file://local/customer_fixes.csv' @%CUSTOMER_FIXES; INSERT INTO CUSTOMER_FIXES FROM @%CUSTOMER_FIXES;
- D
MERGE INTO CUSTOMER_FIXES USING VALUES (101, 'a@example.com', TRUE), (102, 'b@example.com', FALSE), (103, 'c@example.com', TRUE);
Show answer and explanation
Correct answer: A
Explanation
For a small set of records entered directly in a worksheet, Snowflake best practice is to use INSERT INTO ... VALUES. This approach avoids the overhead of creating files, stages, or file formats and is ideal for manual fixes, reference rows, or one-off data corrections. COPY INTO is designed for bulk loading from staged files, while PUT is only for uploading local files to internal stages. MERGE is useful when matching source and target data to perform upserts or conditional actions, but it is unnecessarily complex for this scenario. Snowflake documentation for INSERT describes inserting explicit row values, including multi-row INSERT syntax, while COPY INTO documentation focuses on loading from staged data sources.
- A. Correct.
Correct. INSERT ... VALUES is the appropriate way to add a small number of inline rows directly in SQL when the values are already known. Snowflake supports multi-row INSERT statements using a single VALUES clause with multiple row tuples, which is efficient and simple for manual corrections or small data loads.
- B. Incorrect.
Incorrect. COPY INTO is used to load data from staged files, not directly from an inline VALUES list. A candidate might choose this because COPY INTO is commonly associated with loading data, but it requires a stage or external/internal file source rather than manually typed rows in the SQL statement.
- C. Incorrect.
Incorrect. PUT uploads local files to an internal stage, which adds unnecessary steps and does not match the requirement to avoid stages or external files. Also, INSERT INTO ... FROM @stage is not valid syntax for loading staged files into a table; staged-file loading is typically done with COPY INTO.
- D. Incorrect.
Incorrect. MERGE is intended for conditional insert/update/delete logic based on matching source and target data. Although VALUES can be used as part of a source subquery in more advanced patterns, this option is incomplete and not the simplest or best choice for inserting a few known rows. A candidate might select it if they confuse upsert logic with straightforward row insertion.