COF-C03 Question 225
Single answerError handling optionsA data engineering team loads daily CSV files from an internal stage into a Snowflake table using a COPY INTO command. Occasionally, a few rows contain malformed numeric values, but the business wants the load to continue so that valid rows are ingested. The team also wants visibility into how many rows were rejected during each load. Which COPY option best meets these requirements?
- A
Set ON_ERROR = CONTINUE in the COPY INTO command
- B
Set ON_ERROR = ABORT_STATEMENT in the COPY INTO command
- C
Set VALIDATION_MODE = RETURN_ERRORS in the COPY INTO command used for the actual load
- D
Set SIZE_LIMIT in the COPY INTO command to stop processing after bad rows are found
Show answer and explanation
Correct answer: A
Explanation
The key requirement is to load valid rows while skipping bad records. In Snowflake, the COPY INTO command supports the ON_ERROR copy option for this purpose. Setting ON_ERROR = CONTINUE allows row-level load errors to be skipped so the rest of the file can still be loaded. The COPY results provide operational feedback, including rows parsed, rows loaded, and error counts, which gives the team visibility into rejected rows. By contrast, ON_ERROR = ABORT_STATEMENT stops the load on the first error, and VALIDATION_MODE is intended for validation rather than actual ingestion. This aligns with Snowflake documentation for COPY INTO
copy options and data load error handling best practices.- A. Correct.
Correct. ON_ERROR = CONTINUE tells Snowflake to continue loading the file even when it encounters row-level errors that can be skipped, such as data conversion issues in individual records. Snowflake still reports load results, including the number of rows loaded and errors seen, which helps the team monitor rejected rows while ingesting valid data.
- B. Incorrect.
Incorrect. ON_ERROR = ABORT_STATEMENT is the default behavior for many COPY operations and causes the entire load statement to fail when an error is encountered. That does not satisfy the requirement to continue loading valid rows.
- C. Incorrect.
Incorrect. VALIDATION_MODE = RETURN_ERRORS is used to validate files and return errors instead of loading data. It is useful for testing or troubleshooting, but it does not perform the actual load. Using it for the production load would prevent valid rows from being ingested.
- D. Incorrect.
Incorrect. SIZE_LIMIT controls the approximate amount of data loaded in a COPY operation, not how errors are handled. It is unrelated to continuing past malformed rows or reporting rejected-row counts.