ARA-C01 Question 289
Single answerParameters for copying data and addressing data handling errorsA data engineering team loads daily CSV files from an internal stage into a Snowflake fact table by using COPY INTO. Recently, upstream systems started sending occasional malformed rows with extra delimiters and invalid numeric values. The architect must ensure that valid rows continue loading, invalid rows are skipped, and the team can later review which rows failed and why. Which approach best meets these requirements?
- A
Use COPY INTO with ON_ERROR = CONTINUE, then use VALIDATE on the target table and the COPY job to review rejected rows and error details.
- B
Use COPY INTO with ON_ERROR = ABORT_STATEMENT, then query COPY_HISTORY to retrieve the full contents of each rejected row and reload only those rows.
- C
Use COPY INTO with RETURN_FAILED_ONLY = TRUE so that valid rows are skipped and only invalid rows are loaded into the target table for analysis.
- D
Use COPY INTO with SIZE_LIMIT to stop processing after the first file containing errors, then query QUERY_HISTORY for row-level parsing details.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use COPY INTO with ON_ERROR = CONTINUE and then inspect rejected records with VALIDATE. In Snowflake, ON_ERROR controls how COPY handles conversion and parsing problems. When the business requirement is to load as many valid rows as possible while skipping bad records, CONTINUE is the appropriate setting. To investigate failures afterward, Snowflake provides validation capabilities for COPY loads that return row-level error information for rejected records. By contrast, ABORT_STATEMENT stops the load on the first error, which is appropriate only when strict all-or-nothing ingestion is required. SIZE_LIMIT is about controlling batch size, not handling bad data. Architects should also distinguish between COPY load parameters and unload-related options, since they are often confused in practice. This aligns with Snowflake documentation and best practices for resilient ingestion pipelines: use explicit error-handling parameters during COPY and pair them with validation/error review mechanisms to support operational troubleshooting.
- A. Correct.
Correct. ON_ERROR = CONTINUE allows Snowflake to continue loading valid rows while skipping problematic records in the files. To investigate data load issues afterward, Snowflake provides the VALIDATE function/table function support for COPY loads to return error details about rows that failed during loading. This combination directly addresses the requirement to keep good data flowing while preserving visibility into rejected records.
- B. Incorrect.
Incorrect. ON_ERROR = ABORT_STATEMENT causes the entire COPY statement to fail when an error is encountered, which does not satisfy the requirement to continue loading valid rows. COPY_HISTORY is useful for operational visibility into load activity and status, but it does not provide the full rejected row contents and detailed row-level validation output in the way VALIDATE is designed to do.
- C. Incorrect.
Incorrect. RETURN_FAILED_ONLY is not a parameter for loading invalid rows into the target table. It is associated with COPY INTO
unloading behavior/results handling, not with turning failed input rows into loaded target rows for analysis. This option reflects a common confusion between COPY load parameters and unload/result-set options. - D. Incorrect.
Incorrect. SIZE_LIMIT controls the approximate amount of data loaded by a COPY operation before stopping, and is not intended as an error-handling mechanism. QUERY_HISTORY can show statement-level information, but not the row-level parsing and rejection details needed to diagnose malformed records. This would neither guarantee continued loading of valid rows nor provide the necessary error diagnostics.