Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

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 CONTINUE without checking error logs, which can hide data issues.
  • Setting SKIP_FILE_NUM without 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 ValueBehavior
ABORT_STATEMENTStops loading on first error (default)
CONTINUEIgnores errors and continues loading rows
SKIP_FILESkips entire file if any error occurs
SKIP_FILE_NUM nSkips 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.