How to Use ON_ERROR in COPY INTO Command in Snowflake
In Snowflake, use the
ON_ERROR option in the COPY INTO command to control how errors are handled during data loading. You can set it to CONTINUE, SKIP_FILE, SKIP_FILE_NUM n, or ABORT_STATEMENT to decide whether to ignore errors, skip files, or stop the load.Syntax
The ON_ERROR option in the COPY INTO command specifies how Snowflake handles errors during data loading. It accepts several values:
ABORT_STATEMENT: Stops loading on the first error (default).CONTINUE: Ignores errors and continues loading remaining data.SKIP_FILE: Skips the entire file if an error occurs.SKIP_FILE_NUM n: Skips a specified number of files with errors.
sql
COPY INTO target_table FROM @my_stage/file_path FILE_FORMAT = (TYPE = 'CSV') ON_ERROR = 'CONTINUE';
Example
This example loads data from a CSV file into a table and uses ON_ERROR = 'CONTINUE' to skip rows with errors and continue loading the rest.
sql
CREATE OR REPLACE TABLE employees ( id INT, name STRING, age INT ); COPY INTO employees FROM @my_stage/employees.csv FILE_FORMAT = (TYPE = 'CSV' FIELD_OPTIONALLY_ENCLOSED_BY='"') ON_ERROR = 'CONTINUE';
Output
COPY executed successfully. Rows loaded: X (excluding rows with errors).
Common Pitfalls
Common mistakes when using ON_ERROR include:
- Using
CONTINUEwithout checking error logs, which can hide data issues. - Setting
SKIP_FILE_NUMwithout specifying the number of files to skip. - Expecting partial row loads when errors occur; Snowflake skips entire rows with errors.
sql
/* Wrong: Missing number for SKIP_FILE_NUM */ COPY INTO employees FROM @my_stage/employees.csv FILE_FORMAT = (TYPE = 'CSV') ON_ERROR = 'SKIP_FILE_NUM'; /* Right: Specify number of files to skip */ COPY INTO employees FROM @my_stage/employees.csv FILE_FORMAT = (TYPE = 'CSV') ON_ERROR = 'SKIP_FILE_NUM 2';
Quick Reference
| ON_ERROR Value | Behavior |
|---|---|
| ABORT_STATEMENT | Stops loading on first error (default) |
| CONTINUE | Ignores errors and continues loading rows |
| SKIP_FILE | Skips entire file if any error occurs |
| SKIP_FILE_NUM n | Skips up to n files with errors |
Key Takeaways
Use ON_ERROR in COPY INTO to control error handling during data load.
CONTINUE lets loading proceed despite row errors, but check logs to avoid silent data issues.
SKIP_FILE and SKIP_FILE_NUM help skip problematic files instead of aborting.
ABORT_STATEMENT stops loading immediately on the first error, ensuring strict data quality.
Always specify the number when using SKIP_FILE_NUM to avoid syntax errors.
