Error logging for COPY INTO and Snowpipe¶
Turn on error logging for a table to keep the rows that COPY INTO and Snowpipe reject, so that you can see why they
were rejected, correct them, and load them again with SQL. A row is rejected when COPY INTO or Snowpipe can’t load
it from a staged file. For example, a row is rejected if it can’t be parsed, if a value can’t be converted to the data
type of its column, or if a value violates a NOT NULL or CHECK constraint. Snowflake records rejected rows in the
table’s error table, along with the error, the source data, and, when Snowflake can determine it, the location of the
row in the file. The ON_ERROR copy option determines which rejected rows are recorded.
Error logging is a property of the target table, which is also called the base table. You can turn it on for Snowflake-managed Apache Iceberg™ tables and for standard tables, including transient tables. It records errors from CSV, JSON, and Parquet files. A table that has error logging turned on has one error table, which also records errors from other sources, such as DML statements. For the error table columns and other details that apply to every source of errors, see Error tables. For error logging in Snowpipe Streaming with high-performance architecture, see Error logging in Snowpipe Streaming with high-performance architecture.
Get started¶
The examples on this page and on the related pages use the orders table that’s created in
Turn on error logging and add CHECK constraints, and they load CSV and JSON files from the csv/ and json/ paths of a stage
named orders_stage.
Before you start, make sure that your role has the following privileges:
- To turn on error logging:
OWNERSHIPon the target table. - To query the error table:
OWNERSHIPon the target table, or bothSELECTandSELECT ERROR TABLEon it. If a masking policy, a row access policy, or a projection policy is set on the table or on its columns, see Access control requirements before you continue.
The error table stores the source data of each rejected row, including values in columns that have a masking policy, so
grant access only to roles that are allowed to see that data. Roles that have ALL privileges on the table can also
query it, so check which roles have these privileges before you turn on error logging, as described in
Access control requirements.
To start recording the rows that are rejected when you load files into a table, do the following:
-
Turn on error logging for the target table:
Error logging also applies to
INSERT,UPDATE, andMERGEstatements on the table. When one of these statements encounters a supported error, such as a CHECK constraint violation, it writes the other rows and records the error instead of failing. If a job detects bad data by checking whether these statements fail, see Detect errors in a scheduled job first. -
Optional: To reject the rows that don’t meet a rule, such as a list of allowed values, add a CHECK constraint. Add it before you create the pipes that load the table, because you can’t add one to a table that a pipe loads. For an example, see Turn on error logging and add CHECK constraints. If a pipe already loads the table, see Change the CHECK constraints on a table that a pipe loads.
-
The default for
COPY INTO,ABORT_STATEMENT, records no errors, so load the files withON_ERROR = CONTINUE, which loads the valid rows and records every rejected row:With
CONTINUE, the statement doesn’t fail when it rejects rows. A scheduled job that checks whether the statement fails no longer detects bad data, so check the error table after each load instead, as described in Detect errors in a scheduled job, or create an alert.For a pipe, the default,
SKIP_FILE, records one error for each file that the pipe skips. To record every rejected row, setON_ERROR = CONTINUEin the pipe’sCOPY INTOstatement. This setting changes the error notifications and events for files that contain errors, so see Choose an ON_ERROR value for a pipe first. For a pipe that already loads the table, see Record errors from an existing pipe. -
After a load rejects rows, query the error table. The following query returns one row for each error, with the file, the number of the record in the file, the reason that the record was rejected, and the record as it appears in the file:
The
servicefilter returns only the errors fromCOPY INTOand Snowpipe. Snowflake omits the fields that it can’t determine for an error, so some values can beNULL. For a description of each field, see Error table fields for file loads.
To monitor loads for new errors, see Query load errors. If the error table doesn’t contain the errors that you expect, see Troubleshoot missing errors. To correct the rejected rows and load them, see Correct and reload rejected rows.
When to use error tables¶
Use error tables when you want to keep the rows that COPY INTO and Snowpipe reject, so that you can analyze them with
SQL and load them again. The following table compares error tables with the other tools that report errors in the files
that COPY INTO and Snowpipe load:
| Tool | What it reports |
|---|---|
| Error tables | Rejected rows from CSV, JSON, and Parquet files, including CHECK constraint violations and rows from loads that transform data. Each error includes the source data and, when Snowflake can determine it, the location of the row in the file. Which rows are recorded depends on the ON_ERROR value. |
COPY INTO output and COPY_HISTORY | The load status of each file and the first error in the file. |
VALIDATION_MODE parameter of COPY INTO <table> | Errors in staged files before you load them, except CHECK constraint violations. Doesn’t support loads that transform data or loads into Iceberg tables. |
| VALIDATE | The errors from an earlier COPY INTO statement, except CHECK constraint violations. Returns no results for loads that use ON_ERROR = ABORT_STATEMENT, and doesn’t support loads that transform data. |
| VALIDATE_PIPE_LOAD | Errors in the files that a pipe processed in the last 14 days, except CHECK constraint violations. Doesn’t support pipes that transform semi-structured data, such as JSON or Parquet data, or that use MATCH_BY_COLUMN_NAME. |
| Snowpipe error notifications | A message to your cloud messaging service that lists the files that a pipe failed to load, with the first error in each file. Files that load partially don’t trigger a message. |
| Snowpipe events | Pipe errors and file errors, recorded in an event table. |
Which errors are recorded¶
Error logging records errors in individual rows. It doesn’t record problems that prevent a file from being read, or
problems with a pipe, as described in Limitations. Error logging also doesn’t change
which rows or files are loaded. The ON_ERROR copy option still controls how a load handles errors, and it also
determines which errors are recorded in the error table:
ON_ERROR value | Rows loaded from a file that contains errors | Errors recorded in the error table |
|---|---|---|
CONTINUE | Every row that doesn’t contain an error. | Every rejected row. |
SKIP_FILE (default for pipes) | None. | One error for the file. |
ABORT_STATEMENT (default for COPY INTO) | None. The statement fails. | None. |
SKIP_FILE_<num> or 'SKIP_FILE_<num>%' | Depends on the value. For more information, see ON_ERROR. | Not supported. The error table might contain no errors or only some errors from these loads. |
For a COPY INTO statement, use ON_ERROR = SKIP_FILE instead of CONTINUE if the target table must never contain
part of a file. To load the rows of a skipped file, see
Load files skipped with ON_ERROR = SKIP_FILE.
Choose an ON_ ERROR value for a pipe¶
For a pipe, ON_ERROR = CONTINUE also changes how files that contain errors are reported, because the pipe loads the
valid rows from each file. The following table compares the two values for a file that contains some rows with errors:
| Behavior | ON_ERROR = SKIP_FILE (default for pipes) | ON_ERROR = CONTINUE |
|---|---|---|
| Error notifications | Sent for the file. | Sent only if no rows from the file load. |
| Status in the copy history | Load failed. | Partially loaded, or Load failed if no rows from the file load. |
| Snowpipe events | A FAILED event. | A FAILED event only if no rows from the file load. |
| How you load the rejected rows | Load the file again. | Correct the rows and insert them from the error table. |
Choose the value as follows:
- Keep
ON_ERROR = SKIP_FILEif you rely on error notifications, theLoad failedstatus, orFAILEDevents to report every file that contains an error, or if the target table must never contain part of a file. - Use
ON_ERROR = CONTINUEif you want to load the valid rows from each file and keep every rejected row. Then monitor the error table instead, for example with an alert.
To change the ON_ERROR value of an existing pipe,
recreate the pipe.
Turn on error logging and add CHECK constraints¶
To turn on error logging, set the ERROR_LOGGING property of the target table to TRUE. For example, the following
statement creates an orders table with error logging turned on. The CHECK constraint on the order_status column
accepts only three values, so a load rejects any row with a different value:
For an existing table, run ALTER TABLE orders SET ERROR_LOGGING = TRUE. For more information, see
Introduction to error logging.
Keep the following points in mind:
- A CHECK constraint accepts a row when its condition evaluates to
NULL, so a row with noorder_statusvalue is loaded unless the column is alsoNOT NULL. - On a Snowflake-managed Iceberg table, a CHECK constraint makes writes from external engines fail. For more information, see Overview of constraints.
- Error logging records errors only from loads that run after you turn it on. To find the files from which earlier loads
rejected rows, check the copy history for files with the
Load failedorPartially loadedstatus.
Load files with ON_ ERROR = CONTINUE¶
You don’t need to add an error logging option to a COPY INTO statement or a pipe definition. To record every rejected
row, however, the load must use ON_ERROR = CONTINUE.
If your files have a header line, set the SKIP_HEADER file format option, such as
FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1). Otherwise, the load handles the header line of each file as a row of
data, and it either inserts the line or records it in the error table.
If a load rejects every row in a file, the file gets the Load failed status, so the next COPY INTO statement that
loads from the same path loads the file again and records its errors again. If you run the statement on a schedule,
move these files out of its path, or specify the files to load with the FILES or PATTERN parameter. If the
statement uses PURGE = TRUE, it also removes the files that it loads partially, so the error table might contain the
only copy of the rejected rows.
The following pipe loads JSON files from the json/ path of the same stage into the orders table when new files
arrive:
The pipe example assumes that orders_stage is an external stage and that event notifications are set up for its
storage location, as described in Automate Snowpipe with cloud event notifications. If a pipe already loads the table, don’t
replace it with a statement like this one, because the new pipe can skip files or load them twice. Follow
Record errors from an existing pipe instead.
Don’t load the same path with both a COPY INTO statement and a pipe. Snowflake separately tracks the files that each
one loads, so both load the same files, and the rows are loaded twice. For more information, see
load metadata for Snowpipe.
Access control requirements¶
The following table lists the privileges that a role needs to work with the errors from COPY INTO and Snowpipe:
| Task | Required privileges |
|---|---|
| Turn on or turn off error logging | OWNERSHIP on the target table. |
| Record errors in the error table | None. The role that runs COPY INTO or owns the pipe doesn’t need additional privileges. |
| Query the error table |
|
| Query the error table from an alert, a task, or a view | The role that owns the alert, the task, or the view needs the privileges to query the error table. |
| Pause and resume the pipes that load the table | OPERATE or OWNERSHIP on each pipe. To pause or resume all of the pipes in a schema with ALTER SCHEMA, MODIFY on the schema. |
| Suspend and resume the tasks that load the table | OPERATE or OWNERSHIP on each task. For more information, see ALTER TASK. |
List the files that a pipe skipped with the COPY_HISTORY table function | The privileges that COPY_HISTORY requires, and MONITOR or OWNERSHIP on the pipe. Without either privilege, the pipe_name column is NULL. |
List the files that a pipe skipped with the COPY_HISTORY view | Access to the ACCOUNT_USAGE schema. For more information, see Enabling other roles to use schemas in the SNOWFLAKE database. |
| Load skipped files again | The privileges to run COPY INTO for the target table and the stage. |
| Insert corrected rows from the error table | The privileges to query the error table, and INSERT on the target table. |
| Clear the error table | OWNERSHIP or TRUNCATE on the target table. The SELECT ERROR TABLE privilege isn’t enough. The TRUNCATE privilege also allows a role to run TRUNCATE TABLE on the target table, which deletes its rows and its load metadata. |
For example, if the load_monitor role has the USAGE privilege on the database, the schema, and a warehouse, the
following statements let it query the error table of the orders table:
Granting ALL privileges on a table also grants the SELECT ERROR TABLE privilege, including when you grant the
privileges on all tables or on future tables in a schema or database. For the privileges that apply to every source of
errors, see Access control requirements for error tables.
The error table stores the complete source record for each rejected row, including values for columns that have a
masking policy on the target table and fields that the load doesn’t insert into the table. Error messages can also
contain values from the record. Grant the
SELECT ERROR TABLE privilege only to roles that are allowed to see the source data. A view on the error table uses
the privileges of the view owner, so any role that can query the view can see the errors.
Cost considerations¶
Snowflake doesn’t remove errors from the error table automatically. Snowflake charges for data stored in the error table at the standard storage rate, the same as any other table. To see how much storage an error table uses, query the TABLE_STORAGE_METRICS view. To remove errors, clear the error table, as described in Clear the error table and resume loads.
Snowpipe bills for the bytes that it scans in each source file, including the bytes in rows that it rejects. Recording errors in the error table doesn’t add Snowpipe charges. For more information, see Snowpipe costs.
Usage notes¶
- If the OPT_OUT_ERROR_LOGGING parameter is set to
TRUEfor a session,COPY INTOstatements in the session don’t record errors in the error table. Pipes stop recording errors only if you set the parameter for the account, which stops error logging for every Snowpipe pipe in the account. Unlike turning off error logging, the parameter doesn’t drop the error table. For more information, see Prevent or limit error logging. - If you turn off error logging for a table, drop the table, or replace the table with
CREATE OR REPLACE TABLE, Snowflake drops the error table and the errors in it. To keep the errors, copy them to another table first. To keep recording errors afterCREATE OR REPLACE TABLE, includeERROR_LOGGING = TRUEin the statement. If you restore a dropped table with UNDROP TABLE, its errors are restored too. Errors that you remove withTRUNCATE, or that are dropped when you turn off error logging, can’t be recovered. For more information, see Error table lifecycle. - When you clone a table that has error logging turned on, the errors aren’t cloned. The clone has an empty error table.
- To find every table that has error logging turned on, see Find and monitor tables that have error logging turned on.
- When you replicate a database, its error tables and the errors in them are replicated with it, so the source records in the errors are copied to every account that the database is replicated to. If the source records must not leave the account, clear the error tables before each refresh, or don’t turn on error logging for those tables. Before you clear an error table, stop the loads and statements that write to its target table, as described in Stop loads into the table. For more information, see Replicated database objects.
Limitations¶
- Error tables record errors only in individual rows. They don’t record problems that prevent a file from being read, such as a stage or file that Snowflake can’t access. They also don’t record problems with a pipe, such as configuration or event notification issues. To find these problems, use COPY_HISTORY, Snowpipe error notifications, or Snowpipe events.
- Error logging for
COPY INTOand Snowpipe supports only CSV, JSON, and Parquet files, and only theCONTINUEandSKIP_FILEvalues ofON_ERROR. For more information, see Which errors are recorded. - Error tables record only the rows that a load rejects. Copy options that load a row instead of rejecting it, such as
ERROR_ON_COLUMN_COUNT_MISMATCH = FALSEandTRUNCATECOLUMNS = TRUE, change which rows are recorded. - In some cases, such as loads that use
SELECT DISTINCT, loads into clustered tables, and CSV loads into a table that has a stream, theON_ERRORcopy option might not work as expected, so the error table might not record every rejected row. For the full list, see the usage notes for COPY INTO. - Large Parquet records might be shortened. For more information, see Error data.
- Loads into Iceberg tables that use
LOAD_MODE = ADD_FILES_COPYdon’t record errors in the error table. To record errors, use the default,LOAD_MODE = FULL_INGEST. - Hybrid tables don’t support error logging. For a hybrid table,
COPY INTOsupports onlyON_ERROR = ABORT_STATEMENT, so a row that violates a CHECK constraint causes the load to fail. - You can’t add CHECK constraints to a table that any pipe loads, or drop or change them on a table that a Snowpipe pipe loads, even if the pipe is paused. For the steps to change them, see Change the CHECK constraints on a table that a pipe loads.