SnowPro Associate: Platform Question 283
Single answer● Use COPY INTO <table> statementsA data engineering team receives daily CSV files in an internal stage named @sales_stage. The files sometimes contain a header row, fields may be enclosed in double quotes, and occasionally a few rows have bad numeric values in the AMOUNT column. The team needs to load all valid rows into the SALES table while skipping the header and continuing past bad records so the load does not fail. Which COPY INTO
statement best meets these requirements?- A
COPY INTO SALES FROM @sales_stage FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1 FIELD_OPTIONALLY_ENCLOSED_BY = '"') ON_ERROR = CONTINUE;
- B
COPY INTO SALES FROM @sales_stage FILE_FORMAT = (TYPE = CSV PARSE_HEADER = TRUE FIELD_DELIMITER = ',') ON_ERROR = ABORT_STATEMENT;
- C
COPY INTO SALES FROM @sales_stage FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1) VALIDATION_MODE = RETURN_ERRORS;
- D
COPY INTO SALES FROM @sales_stage ON_ERROR = SKIP_FILE FILE_FORMAT = (TYPE = JSON STRIP_OUTER_ARRAY = TRUE);
Show answer and explanation
Correct answer: A
Explanation
The best answer is Option 1 because COPY INTO
supports inline file format options and error-handling behavior that are commonly used for practical data ingestion. For CSV data, SKIP_HEADER = 1 is the standard way to ignore a single header row. FIELD_OPTIONALLY_ENCLOSED_BY = '"' is appropriate when values may be wrapped in double quotes. ON_ERROR = CONTINUE allows Snowflake to continue loading rows after encountering row-level parsing or conversion issues, which is suitable when a small number of bad records should not block ingestion of valid data. By contrast, ABORT_STATEMENT stops the load on error, VALIDATION_MODE checks data without loading it, and using a JSON file format for CSV input is invalid. These behaviors align with Snowflake documentation for COPY INTO- A. Correct.
Correct. This statement uses a CSV file format, skips the first row with SKIP_HEADER = 1, correctly handles optionally quoted fields with FIELD_OPTIONALLY_ENCLOSED_BY = '"', and uses ON_ERROR = CONTINUE so rows with conversion errors, such as bad numeric values in AMOUNT, do not cause the entire load to fail. This matches the requirement to load valid rows and continue past bad records.
- B. Incorrect.
Incorrect. PARSE_HEADER is used when loading data with MATCH_BY_COLUMN_NAME in certain contexts, not as the primary way to skip a CSV header row for a standard positional load into a table. More importantly, ON_ERROR = ABORT_STATEMENT causes the COPY operation to fail on the first error, which does not meet the requirement to continue loading valid rows.
- C. Incorrect.
Incorrect. VALIDATION_MODE = RETURN_ERRORS validates files and returns errors instead of loading data into the table. While SKIP_HEADER = 1 is appropriate for ignoring the header row, this option does not perform the actual load, so it does not satisfy the business requirement.
- D. Incorrect.
Incorrect. This option uses a JSON file format even though the source files are CSV. In addition, ON_ERROR = SKIP_FILE would skip an entire file if errors occur, which is more aggressive than needed when the goal is to load valid rows from files that may contain only a few bad records.