DAA-C01 Question 83
Select 3Identify and resolve data import errorsA data analyst loads daily CSV files from an internal stage into a Snowflake table using a COPY INTO command. The target table has 12 columns, including a TIMESTAMP_NTZ column and several NUMBER columns. Recently, the load started completing with fewer rows than expected. The analyst suspects some rows are being rejected because a new source system began sending malformed timestamps and occasional extra delimiters in a free-text field. The analyst needs to identify the specific row-level import errors quickly and then adjust the load process so that valid rows continue loading while bad rows can be reviewed separately. Which TWO actions should the analyst take?
- A
Use the VALIDATION_MODE option with COPY INTO, such as RETURN_ERRORS or RETURN_ALL_ERRORS, to inspect rejected rows without loading data.
- B
Set ON_ERROR = CONTINUE in the COPY INTO command so valid rows load while invalid rows are skipped and reported in the load output.
- C
Replace COPY INTO with INSERT ... SELECT from the staged files, because INSERT statements automatically capture malformed CSV records in a system error table.
- D
Query the COPY_HISTORY view only, because it returns the full text of each rejected row and the exact offending column value.
- E
Define or revise the file format options, such as FIELD_OPTIONALLY_ENCLOSED_BY and timestamp format settings, to correctly parse embedded delimiters and source timestamp patterns.
Show answer and explanation
Correct answers: A, B, E
Explanation
The best approach combines diagnosis and controlled ingestion. First, use COPY INTO with VALIDATION_MODE to surface row-level import issues before or instead of loading. This is especially helpful for errors such as invalid timestamp values, unexpected column counts, or parsing failures introduced by source changes. Second, use ON_ERROR = CONTINUE when the goal is to keep loading valid rows while isolating problematic records for follow-up. Third, review and correct the file format definition to reflect the actual structure of the incoming CSV files, particularly when embedded delimiters or changed timestamp patterns are involved. In Snowflake documentation, COPY INTO supports error-handling behavior through ON_ERROR and validation through VALIDATION_MODE, while file format options such as FIELD_OPTIONALLY_ENCLOSED_BY, ESCAPE, SKIP_HEADER, and TIMESTAMP_FORMAT are key tools for resolving import errors caused by file structure or data-type parsing issues.
- A. Correct.
Correct. VALIDATION_MODE on COPY INTO is specifically designed to validate staged data and return error information without performing the load. Options such as RETURN_ERRORS or RETURN_ALL_ERRORS help identify row-level parsing and conversion issues, including malformed timestamps or column-count mismatches caused by bad delimiters. This is a standard troubleshooting approach when investigating data load failures or partial loads.
- B. Correct.
Correct. ON_ERROR = CONTINUE allows Snowflake to continue loading valid rows while skipping rows that fail parsing or conversion. This is appropriate when the business requirement is to keep ingesting good data and separately review bad records. The COPY results include error counts and related load information, making this a practical operational setting for mixed-quality source files.
- C. Incorrect.
Incorrect. INSERT ... SELECT is not the standard mechanism for directly handling malformed staged CSV files in the way described here, and Snowflake does not provide a special automatic system error table for malformed CSV rows generated by INSERT statements. COPY INTO is the correct bulk-loading command for staged file ingestion and supports dedicated error-handling and validation features.
- D. Incorrect.
Incorrect. COPY_HISTORY is useful for reviewing load activity, status, file names, and error counts, but it does not by itself provide the complete rejected row text and exact offending field values for every bad record in the way this option suggests. Analysts often use COPY_HISTORY alongside validation and COPY error-handling features, but it is not sufficient alone for detailed row-level troubleshooting.
- E. Correct.
Correct. Since the source changed, revising the file format is often necessary. If a free-text field contains delimiters, settings like FIELD_OPTIONALLY_ENCLOSED_BY can prevent false column splits. Likewise, specifying an appropriate TIMESTAMP_FORMAT or using AUTO when suitable can help parse the incoming timestamp pattern. Adjusting file format settings addresses the root cause instead of only treating the symptoms.