ARA-C01 Question 290
Single answerParameters for copying data and addressing data handling errorsA data engineering team loads daily CSV files from an external stage into a Snowflake fact table using COPY INTO. The files occasionally contain malformed rows caused by extra delimiters or invalid numeric values. The business requirement is to load all valid rows, skip only problematic rows, and keep enough detail to troubleshoot which rows failed later. The team also wants to avoid aborting the entire load because of a few bad records. Which COPY INTO approach best meets these requirements?
- A
Use COPY INTO with ON_ERROR = 'CONTINUE' and then review the load results and validation output to identify rejected rows.
- B
Use COPY INTO with ON_ERROR = 'ABORT_STATEMENT' and SIZE_LIMIT to ensure only valid rows are committed before any errors occur.
- C
Use COPY INTO with FORCE = TRUE so that malformed rows are skipped but valid rows are still loaded.
- D
Use COPY INTO with PURGE = TRUE so that files containing bad rows are removed from the stage after partial loading, preventing repeated errors.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use COPY INTO with ON_ERROR = 'CONTINUE'. This parameter is specifically designed for scenarios where some rows may be bad but the business wants to ingest as many valid records as possible. Snowflake will continue processing and skip problematic rows rather than failing the entire statement. To investigate issues afterward, architects commonly review COPY output and use Snowflake validation features to inspect load errors. In contrast, ABORT_STATEMENT fails the load, FORCE only affects whether previously loaded files are reloaded, and PURGE only removes successfully loaded files from the stage. Snowflake documentation on COPY INTO
describes ON_ERROR behavior and related validation/error-handling best practices.- A. Correct.
Correct. ON_ERROR = 'CONTINUE' tells Snowflake to continue loading the file while skipping rows that encounter parsing or conversion errors, which directly satisfies the requirement to load valid rows without aborting the entire operation. After the load, the team can inspect COPY results and use validation capabilities such as VALIDATION_MODE in appropriate contexts to troubleshoot rejected rows. This is the standard approach when some bad records are expected and partial load success is acceptable.
- B. Incorrect.
Incorrect. ON_ERROR = 'ABORT_STATEMENT' is the default behavior for many COPY scenarios and causes the entire COPY command to fail when an error is encountered. That conflicts with the requirement to load valid rows and skip only problematic rows. SIZE_LIMIT controls the amount of data loaded in a COPY operation, not row-level error handling, so it does not solve the stated problem.
- C. Incorrect.
Incorrect. FORCE = TRUE causes Snowflake to reload files even if they were loaded previously, bypassing load history checks. It does not change how malformed rows are handled and does not instruct Snowflake to skip bad records. This option reflects a common misconception between file reprocessing behavior and row error handling behavior.
- D. Incorrect.
Incorrect. PURGE = TRUE removes successfully loaded files from the stage after loading. It does not provide row-level error handling or preserve bad rows for troubleshooting. In fact, using PURGE in a scenario with data quality issues can be risky if the team still needs staged files for investigation or reprocessing.