COF-C03 Question 223
Single answerCOPY INTO commandA data engineering team loads daily CSV files from an internal stage into a Snowflake table named SALES_RAW using the COPY INTO command. They discover that some rows contain invalid numeric values in the AMOUNT column, but the business wants all valid rows loaded while invalid rows are skipped and reported for later review. Which COPY INTO statement option best meets this requirement?
- A
Use COPY INTO SALES_RAW ... ON_ERROR = 'CONTINUE'
- B
Use COPY INTO SALES_RAW ... VALIDATION_MODE = 'RETURN_ERRORS'
- C
Use COPY INTO SALES_RAW ... FORCE = TRUE
- D
Use COPY INTO SALES_RAW ... PURGE = TRUE
Show answer and explanation
Correct answer: A
Explanation
The best answer is ON_ERROR = 'CONTINUE' because the requirement is to load as many valid rows as possible while skipping rows that fail conversion or parsing. In Snowflake, COPY INTO supports error-handling behavior through the ON_ERROR copy option. By contrast, VALIDATION_MODE is for pre-checking files without loading them, FORCE is for reloading previously loaded files, and PURGE is for cleaning up staged files after successful loads. This reflects a common real-world ingestion pattern where imperfect source files must still be processed on schedule while problematic records are reviewed separately. See Snowflake documentation for COPY INTO
options, especially ON_ERROR and VALIDATION_MODE behavior.- A. Correct.
Correct. ON_ERROR = 'CONTINUE' tells Snowflake to continue loading the file even when it encounters row-level errors, skipping problematic rows and loading valid rows. This is the appropriate choice when the goal is partial loading with bad records excluded. The rejected rows can then be investigated using Snowflake load history and error details.
- B. Incorrect.
Incorrect. VALIDATION_MODE = 'RETURN_ERRORS' is used to validate data and return encountered errors without actually loading the data into the target table. This is useful for testing or troubleshooting a load, but it does not satisfy the requirement to load valid rows while skipping invalid ones.
- C. Incorrect.
Incorrect. FORCE = TRUE instructs Snowflake to reload files even if they were loaded previously and are recorded in load metadata. It does not control how row-level data errors are handled during the load.
- D. Incorrect.
Incorrect. PURGE = TRUE removes files from the stage after they are loaded successfully. This affects stage file lifecycle management, not error-handling behavior for invalid rows during COPY INTO.