SnowPro Associate: Platform Question 223
Single answer○ COPY INTOA data engineering team receives daily CSV files in an internal stage. Occasionally, a few rows contain bad data such as invalid dates or extra delimiters, but the team still wants to load all valid rows into a target table and review the rejected rows later. They run a COPY INTO command from the stage into the table. Which COPY option should they use to meet this requirement?
- A
ON_ERROR = CONTINUE
- B
FORCE = TRUE
- C
VALIDATION_MODE = RETURN_ERRORS
- D
PURGE = TRUE
Show answer and explanation
Correct answer: A
Explanation
The requirement is to load valid records while skipping problematic rows, which is exactly what COPY INTO with ON_ERROR = CONTINUE is designed to do. In Snowflake, COPY INTO supports error-handling behavior through the ON_ERROR parameter. By contrast, VALIDATION_MODE is for validating files without performing the actual load, FORCE is for reloading previously loaded files, and PURGE is for removing files after successful loading. In practice, teams often combine ON_ERROR = CONTINUE with review of COPY results and load history to identify rejected rows and fix source data issues. This aligns with Snowflake documentation for COPY INTO
, especially the ON_ERROR and VALIDATION_MODE parameters.- A. Correct.
Correct. ON_ERROR = CONTINUE tells COPY INTO to continue loading rows even when some rows fail parsing or conversion. Valid rows are loaded, and invalid rows are skipped and reported in the load results. This is the appropriate choice when the goal is to maximize successful ingestion while handling bad records separately.
- B. Incorrect.
Incorrect. FORCE = TRUE reloads files that Snowflake would otherwise consider already loaded. It does not control row-level error handling. Someone might choose this option if they confuse file reprocessing behavior with bad-record handling.
- C. Incorrect.
Incorrect. VALIDATION_MODE = RETURN_ERRORS validates the data and returns errors instead of loading data into the target table. This is useful for testing a load or investigating issues, but it does not satisfy the requirement to load valid rows now.
- D. Incorrect.
Incorrect. PURGE = TRUE removes staged files after they are loaded successfully, depending on stage type and permissions. It does not address malformed rows or partial load behavior. A user might pick this if they are thinking about post-load cleanup rather than error handling.