COF-C03 Question 222
Single answerCOPY INTO commandA data engineering team loads daily CSV files from an external stage into a Snowflake table named SALES_RAW using COPY INTO. They discover that some rows in the source files occasionally contain malformed numeric values in the AMOUNT column. The business requirement is to load all valid rows, skip only the problematic rows, and continue processing the rest of each file. Which COPY INTO approach best meets this requirement?
- A
Use COPY INTO SALES_RAW ... ON_ERROR = 'CONTINUE'
- B
Use COPY INTO SALES_RAW ... FORCE = TRUE
- C
Use COPY INTO SALES_RAW ... VALIDATION_MODE = 'RETURN_ERRORS'
- D
Use COPY INTO SALES_RAW ... PURGE = TRUE
Show answer and explanation
Correct answer: A
Explanation
The requirement is a classic partial-load scenario for COPY INTO: keep good records, skip bad ones, and continue loading. In Snowflake, the COPY INTO command supports error-handling behavior through the ON_ERROR copy option. ON_ERROR = 'CONTINUE' is the appropriate choice when the goal is to load valid rows and ignore rows that cause errors. By contrast, VALIDATION_MODE is for pre-load validation and does not load data, FORCE addresses reloading previously loaded files, and PURGE manages staged files after successful load. This aligns with Snowflake documentation and common data-loading best practices for resilient ingestion pipelines.
- A. Correct.
Correct. Setting ON_ERROR = 'CONTINUE' tells Snowflake to continue loading data even when it encounters row-level errors, such as malformed values that cannot be converted to the target column type. Snowflake loads the valid rows and skips the invalid ones, which matches the stated requirement.
- B. Incorrect.
Incorrect. FORCE = TRUE tells Snowflake to reload files even if they were loaded previously and are recorded in load metadata. It does not control row-level error handling or allow valid rows to load while skipping invalid rows.
- C. Incorrect.
Incorrect. VALIDATION_MODE = 'RETURN_ERRORS' is used to test or validate data files and return encountered errors without actually loading the data into the table. This is useful for troubleshooting, but it does not satisfy the requirement to load valid rows while skipping bad ones.
- D. Incorrect.
Incorrect. PURGE = TRUE removes files from the stage after they are loaded successfully. It is related to file lifecycle management after loading, not to handling malformed rows during COPY INTO execution.