SOL-C01 · Question #95
A data engineering team is loading data from a CSV file containing customer information into a Snowflake table. The CSV file occasionally contains malformed data, such as missing fields or fields with
The correct answer is E. Use 'ON ERROR = 'CONTINUE'` to allow the COPY statement to proceed even when errors are. Option E is correct because ON_ERROR = 'CONTINUE' is the only setting that satisfies both requirements: the COPY statement keeps processing the file past malformed rows, and Snowflake automatically captures rejected records in a queryable load history (accessible via VALIDATE() o
Question
A data engineering team is loading data from a CSV file containing customer information into a Snowflake table. The CSV file occasionally contains malformed data, such as missing fields or fields with incorrect data types (e.g., a string in a numeric field). The team wants to ensure that the loading process continues even when such errors occur, and that the problematic records are tracked for later analysis. Which of the following COPY INTO options should be used to achieve this, and how should the error be handled?
Options
- AUse 'ON_ERROR = 'SKIP_FILE" to skip the entire file if any errors are encountered, and use
- BUse 'ON_ERROR = 'SKIP_FILE" to skip the entire file if any errors are encountered, without
- CUse 'ON ERROR = 'CONTINUE'` to allow the COPY statement to proceed even when errors are
- DUse 'ON_ERROR = 'SKIP_FILE" along with the 'SIZE_LIMIT parameter to limit the size of the file
- EUse 'ON ERROR = 'CONTINUE'` to allow the COPY statement to proceed even when errors are
How the community answered
(37 responses)- A3% (1)
- B14% (5)
- C22% (8)
- D5% (2)
- E57% (21)
Explanation
Option E is correct because ON_ERROR = 'CONTINUE' is the only setting that satisfies both requirements: the COPY statement keeps processing the file past malformed rows, and Snowflake automatically captures rejected records in a queryable load history (accessible via VALIDATE() or the COPY_HISTORY view), enabling the team to audit problematic records afterward.
Options A, B, and D all use ON_ERROR = 'SKIP_FILE', which abandons the entire file the moment any error is encountered - the opposite of what the team needs; valid rows in the same file are never loaded. Option D compounds the problem by adding SIZE_LIMIT, which is a file-size filter unrelated to error handling. Option C also specifies ON_ERROR = 'CONTINUE' but omits the error-tracking mechanism, so while loading would proceed, there would be no systematic way to identify and analyze the rejected records later - failing the second requirement.
Memory tip: Think of it as a road trip. SKIP_FILE turns the car around at the first pothole; CONTINUE drives through all the potholes but logs every one of them in the trip report so you can fix the road later.
Topics
Community Discussion
No community discussion yet for this question.