DAA-C01 Question 84
Single answerIdentify and resolve data import errorsA data analyst loads daily CSV files from an internal stage into a Snowflake table named SALES_RAW using a COPY INTO command. Recently, several files have started failing to load. The analyst needs to identify which rows are causing the failures and still load the valid rows from the same files during the investigation. Which approach should the analyst use?
- A
Run COPY INTO SALES_RAW with ON_ERROR = 'CONTINUE', then use the VALIDATE table function on the target table and load job to return the rows that produced errors.
- B
Run COPY INTO SALES_RAW with FORCE = TRUE so Snowflake reloads all files and automatically skips only the invalid columns.
- C
Run COPY INTO SALES_RAW with MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE so Snowflake can correct row-level parsing errors in the CSV files.
- D
Run COPY INTO SALES_RAW with PURGE = TRUE so failed files are removed from the stage and can be inspected in the load history.
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 the VALIDATE table function. This combination is specifically suited to a real-world troubleshooting scenario where the business wants good data loaded immediately while problematic rows are isolated for remediation. Snowflake load error handling supports behaviors such as ABORT_STATEMENT, CONTINUE, and SKIP_FILE, and ON_ERROR = 'CONTINUE' is appropriate when preserving good rows is important. The VALIDATE function is designed to return errors encountered during loading for a specified table and load job, making it useful for diagnosing row-level import failures. In contrast, FORCE only reloads files, MATCH_BY_COLUMN_NAME addresses column mapping rather than bad input records, and PURGE is a file cleanup setting rather than an error investigation tool. This aligns with Snowflake best practices for staged data loading, COPY error handling, and validation of data load errors.
- A. Correct.
Correct. Using COPY INTO with ON_ERROR = 'CONTINUE' allows Snowflake to load valid rows while skipping rows that cause parsing or conversion errors. After the load, the VALIDATE table function can be used against the target table and the relevant COPY job to identify the rejected rows and associated errors. This is the practical approach when the goal is to continue ingestion while diagnosing bad records.
- B. Incorrect.
Incorrect. FORCE = TRUE tells Snowflake to reload files even if they were previously loaded, but it does not help identify bad rows or skip invalid columns. Snowflake does not automatically ignore only specific invalid columns during a standard COPY operation.
- C. Incorrect.
Incorrect. MATCH_BY_COLUMN_NAME helps map source fields to target columns, commonly with semi-structured data or when column order differs, but it does not resolve malformed CSV row data, delimiter issues, or data type conversion errors in individual records.
- D. Incorrect.
Incorrect. PURGE = TRUE removes successfully loaded files from the stage after loading. It does not remove failed files for inspection through load history, nor does it help identify which records caused the import errors. In fact, enabling PURGE during troubleshooting can make investigation harder if successful staged files are removed too early.