COF-C03 Question 224
Single answerError handling optionsA data engineering team loads daily CSV files from an internal stage into a Snowflake table using the 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 to review rejected rows after the load completes. 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
- D
Set FORCE = TRUE in the COPY INTO command
Show answer and explanation
Correct answer: A
Explanation
For COPY INTO, Snowflake provides several error-handling behaviors through the ON_ERROR option. When the goal is to ingest as many valid rows as possible from imperfect source files, ON_ERROR = CONTINUE is typically the best choice because it skips problematic rows and continues processing the rest of the file. By contrast, ON_ERROR = ABORT_STATEMENT stops the load on the first encountered error, and VALIDATION_MODE is intended for pre-checking or troubleshooting rather than loading data. FORCE is unrelated to row parsing or conversion failures. This aligns with Snowflake documentation and operational best practices for staged file ingestion, where teams often combine ON_ERROR settings with load history review and validation queries to investigate rejected records.
- A. Correct.
Correct. ON_ERROR = CONTINUE tells Snowflake to skip rows that cause parsing or conversion errors and continue loading the remaining valid rows. Rejected rows and related load errors can then be reviewed through load history and validation techniques after the load. This is the standard choice when partial loading is acceptable and bad records need to be investigated later.
- 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 data instead of performing the actual load. It helps identify errors, but it does not load valid rows while skipping invalid ones in the same operation. A candidate might choose this because it supports error review, but it does not meet the requirement to continue ingesting good data.
- D. Incorrect.
Incorrect. FORCE = TRUE reloads files even if Snowflake believes they were already loaded before. It affects file load tracking behavior, not row-level error handling. This is a plausible distractor because it is a common COPY option, but it does not address malformed rows.