SnowPro Associate: Platform Question 222
Single answer○ COPY INTOA data engineering team loads daily CSV files from an internal stage into the SALES_RAW table using a COPY INTO command. One morning, the load fails because one file contains a few rows with an extra delimiter, while the rest of the files are valid. The team wants to load as many valid rows as possible from all files in this run and skip only the problematic rows, without manually editing the source files first. Which COPY INTO approach best meets this requirement?
- A
Use COPY INTO with ON_ERROR = 'CONTINUE' so valid rows are loaded and only problematic rows are skipped
- B
Use COPY INTO with FORCE = TRUE so Snowflake ignores malformed rows and loads all valid rows
- C
Use COPY INTO with VALIDATION_MODE = 'RETURN_ERRORS' so valid rows are loaded and invalid rows are skipped in the same command
- D
Use COPY INTO with PURGE = TRUE so the bad file is removed from the stage after valid rows are loaded
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use COPY INTO with ON_ERROR = 'CONTINUE'. In Snowflake, COPY INTO supports error-handling behavior through the ON_ERROR parameter. For delimited files such as CSV, this allows a load to continue when certain rows are malformed, which is a common real-world requirement for ingest pipelines that prioritize availability and incremental loading. By contrast, FORCE = TRUE is about reloading files regardless of load history, not error tolerance. VALIDATION_MODE is for pre-load validation and diagnostics rather than loading data. PURGE = TRUE is for stage file cleanup after successful loads and does not address row parsing issues. This aligns with Snowflake documentation and best practices for staged data loading using COPY INTO tables.
- A. Correct.
Correct. ON_ERROR = 'CONTINUE' instructs Snowflake to continue loading after encountering row-level errors, skipping the problematic rows and loading the valid rows it can process. This is the best fit when the goal is to maximize data loaded during the current run without stopping because of a small number of bad records.
- B. Incorrect.
Incorrect. FORCE = TRUE tells Snowflake to reload files even if they were loaded previously according to load metadata. It does not control how parsing or data errors are handled and does not cause malformed rows to be ignored.
- C. Incorrect.
Incorrect. VALIDATION_MODE is used to validate data and return errors or sample rows without actually loading the data into the target table. It is useful for troubleshooting, but it does not perform a partial load in the same command.
- D. Incorrect.
Incorrect. PURGE = TRUE removes successfully loaded files from the stage after loading. It does not handle malformed rows and would not selectively solve the issue of bad records within a file. Also, relying on PURGE to manage bad files is not the intended purpose of the parameter.