SnowPro Associate: Platform Question 282
Single answer● Use COPY INTO <table> statementsA data engineering team receives daily CSV files in an internal stage named @sales_stage. The files include a header row and sometimes contain a few malformed records. The team must load as many valid rows as possible into the SALES_RAW table while skipping the header row and continuing past bad records instead of failing the entire load. Which COPY INTO statement best meets these requirements?
- A
COPY INTO SALES_RAW FROM @sales_stage FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1) ON_ERROR = CONTINUE;
- B
COPY INTO SALES_RAW FROM @sales_stage FILE_FORMAT = (TYPE = CSV PARSE_HEADER = TRUE) ON_ERROR = ABORT_STATEMENT;
- C
COPY INTO SALES_RAW FROM @sales_stage FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 0) VALIDATION_MODE = RETURN_ERRORS;
- D
COPY INTO SALES_RAW FROM @sales_stage MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1) ON_ERROR = SKIP_FILE;
Show answer and explanation
Correct answer: A
Explanation
The best answer is Option 1 because it directly addresses both operational requirements of the scenario: skipping the CSV header row and continuing to load valid rows despite some malformed records. In Snowflake, COPY INTO
supports file format options such as SKIP_HEADER for CSV files and error-handling options such as ON_ERROR. ON_ERROR = CONTINUE is appropriate when the business goal is to ingest as much valid data as possible and tolerate a limited number of bad rows. By contrast, ABORT_STATEMENT stops the load on error, SKIP_FILE rejects the whole file when an error is encountered, and VALIDATION_MODE performs validation rather than loading. This aligns with Snowflake documentation and common loading best practices for staged CSV ingestion.- A. Correct.
Correct. For CSV data with a header row, SKIP_HEADER = 1 skips the first row of each file. ON_ERROR = CONTINUE tells Snowflake to continue loading valid rows even when some rows are malformed, which matches the requirement to maximize loaded data instead of failing the entire operation.
- B. Incorrect.
Incorrect. PARSE_HEADER = TRUE is used in specific header-parsing scenarios and does not by itself satisfy the requirement to skip the header row in a standard COPY INTO load. More importantly, ON_ERROR = ABORT_STATEMENT causes the entire load to fail on the first error, which is the opposite of the stated requirement.
- C. Incorrect.
Incorrect. VALIDATION_MODE = RETURN_ERRORS validates files and returns errors instead of loading data into the target table. This is useful for testing loadability, but it does not perform the requested data load. Also, SKIP_HEADER = 0 would not skip the CSV header row.
- D. Incorrect.
Incorrect. MATCH_BY_COLUMN_NAME is generally associated with semi-structured data loading behavior and is not the key requirement for a straightforward CSV-to-table load in this scenario. In addition, ON_ERROR = SKIP_FILE skips an entire file if an error is encountered, which is too aggressive when the goal is to load as many valid rows as possible from files that contain only a few bad records.