Error logging for DML statements¶
By default, when a DML statement encounters an error in one row, the statement fails, and Snowflake rolls back the changes that the statement made. When you turn on error logging for the target table, a statement that encounters a supported error writes the other rows instead of failing, and records the error in an error table. The table for which error logging is turned on is called the base table.
Error logging records errors from the following statements:
INSERT, including multi-table INSERT: The statement inserts the other rows. In a multi-tableINSERT, an error in a row for a target table that doesn’t have error logging turned on fails the whole statement.UPDATE: The statement updates the other rows and leaves the row that caused the error unchanged.MERGE: The statement applies the other changes and doesn’t apply the update or the insert for the row that caused the error.CREATE TABLE ... AS SELECT(CTAS) that setsERROR_LOGGING = TRUE: The statement creates the table with the other rows. For an example, see Record errors when creating a table from a query.
The error table also records errors from other sources, such as COPY INTO and Snowpipe. For those errors, see
Error logging for COPY INTO and Snowpipe. To see only the errors from DML and CTAS statements, filter on
error_metadata:service IS NULL.
For the columns of the error table, see Error table columns. For the privileges that you need to query the error table, see Access control requirements for error tables.
Use cases for DML error logging¶
You might use DML error logging to avoid failures on errors for the following use cases:
- Migration of workloads that rely on DML error logging in another database, such as Oracle.
- Enforcement of some table constraints, such as NOT NULL and CHECK constraints, during data ingestion.
Supported errors¶
Error logging records only errors that are directly related to the base table:
- NOT NULL table constraint violations.
- CHECK constraint violations.
- Type conversion errors, which occur when a value can’t be converted to the data type of the base table column.
- Incompatible precision and scale values.
- Incompatible length for string and binary types.
- Some expression evaluation failures, such as division by zero or
PARSE_JSONfunction failures.
Other errors fail the statement, the same as when error logging is turned off. For example, a division by zero in the
ON condition or in a WHEN MATCHED AND condition of a MERGE statement fails the statement. A statement that has a
compilation error fails, and no errors are recorded. Snowflake doesn’t enforce PRIMARY KEY, UNIQUE, or FOREIGN KEY
constraints on standard tables, so rows that violate these constraints are written, not recorded as errors. For the
errors that are recorded when you load files with COPY INTO or Snowpipe, see
Which errors are recorded.
Turn on error logging for the target table¶
Before you turn on error logging, check whether an application or a pipeline relies on a failed statement to detect bad data. If one does, check the error table instead, as described in Detect errors in a scheduled job, or opt out of error logging for its sessions, as described in Prevent or limit error logging.
To turn on error logging for an existing table, use a role that owns the table, and run ALTER TABLE:
Use ALTER TABLE, not CREATE OR ALTER TABLE, which doesn’t change the ERROR_LOGGING property of a table that
already exists. To create a table with error logging turned on, set ERROR_LOGGING = TRUE in the CREATE TABLE
statement. For more information, see Turn on error logging.
Find the errors from a statement¶
To see the errors from a statement that you just ran in the same session, filter on its query ID:
LAST_QUERY_ID() returns the ID of the most recent statement in the session, so run the query right after the
statement. In a Snowflake Scripting block, save the query ID of the
statement from the SQLID variable instead. For an example, see
Detect errors in a scheduled job.
If you don’t have the query ID, find it in the error table. All of the errors from one statement have the same
query_id and timestamp values, and the timestamp value is the time when the statement started:
Detect errors in a scheduled job¶
When error logging is turned on, a statement that encounters a supported error doesn’t fail, so a task or a job that
relies on the statement failing doesn’t detect the error. To detect errors, check the error table for errors from the
statement. For example, the following Snowflake Scripting block runs a MERGE statement, and then raises an exception
if the statement recorded any errors. The block uses an orders table that has error logging turned on, and an
orders_staging table that has the same order_id and order_status columns. In a task, the exception makes the run
fail, so the run appears as failed in the task history and triggers error notifications if you set them up for the task:
To use the block as the body of a task or a stored procedure, use the part from DECLARE to END;, with the
delimiters that your client requires, as described in Using Snowflake Scripting in Snowflake CLI and Python Connector.
The exception doesn’t roll back the MERGE statement, which already wrote the other rows. To correct the rows that
the statement couldn’t write, see Correct and write rows again.
Examples¶
The following examples turn on DML error logging for tables and show how errors are recorded in error tables.
Record errors when inserting rows directly¶
The following example records errors from an INSERT statement that inserts values directly into a table:
-
Create a table and turn on error logging for it:
-
Run an
INSERTstatement that tries to insert several rows, including both valid and invalid values: -
Query the table to confirm that one valid row was inserted:
-
Query the error table for the
test_dml_error_loggingbase table to view the errors that were recorded:The error table contains one error for each row that the statement didn’t insert. In the
error_datavalue, each invalid value is in an array, and each valid value is shown directly. The second row contains two invalid values,NULLfor the NOT NULL columnNand'toolong'for theVARCHAR(5)columnT. Theerror_codeanderror_metadatavalues describe only the first error, but theerror_datavalue shows both invalid values. -
Turn off error logging for the
test_dml_error_loggingtable. Turning off error logging drops the error table and the errors in it: -
Run the same
INSERTstatement again. The statement fails, and no errors are recorded:
Record errors when inserting rows from one table into another table¶
The following example records errors from an INSERT statement that selects rows from one table and inserts them into
another table:
-
Create a source table and insert values:
-
Create a target table with the same definition as the source table:
-
Turn on error logging for the
dml_error_logging_targettable: -
Insert values into the target table by querying the source table so that one of the inserts results in a division-by-zero error:
-
Query the table to confirm that two valid rows were inserted:
-
Query the error table for the
dml_error_logging_targetbase table to view the errors that were recorded:
Record errors when creating a table from a query¶
A CREATE TABLE ... AS SELECT statement that sets ERROR_LOGGING = TRUE creates the table with the rows that don’t
cause errors, and records the errors in the error table of the new table. For example, the following statements create
an orders_raw table and then create an orders_clean table from it. The row whose amount value can’t be
converted to a number isn’t written to the new table, and its error is recorded in the error table:
To see the errors, query the error table of the new table:
Error logging stays turned on for the new table, so later INSERT, UPDATE, and MERGE statements on the table also
record errors instead of failing. To turn it off, use ALTER TABLE orders_clean SET ERROR_LOGGING = FALSE, which drops
the error table.
A CREATE OR REPLACE TABLE ... AS SELECT statement that sets ERROR_LOGGING = TRUE works the same way. It drops the
existing table and its error table, so the new error table contains only the errors from the new statement.
A CREATE TABLE ... AS SELECT statement that sets ERROR_LOGGING = TRUE doesn’t fail when a row has a supported
error. If a job relies on the statement failing, check the error table of the new table after the statement, as
described in Detect errors in a scheduled job. To keep the statement failing on these errors, omit
ERROR_LOGGING = TRUE from it, and then turn on error logging for the new table with
ALTER TABLE ... SET ERROR_LOGGING = TRUE.
Correct and write rows again¶
A statement that records errors already wrote its other rows, so don’t run the whole statement again. Otherwise, it writes those rows a second time. If the statement runs again before you correct the rows, for example on a schedule, it records the same errors again, so correct the source data or filter the rows that cause errors out of the statement.
To correct and write the rows, do the following:
-
Query the errors from the statement by filtering on its query ID. The following query gets the values of the
nandtcolumns of a table like the one in Record errors when inserting rows directly:Keep the following points in mind:
- In the
error_datavalue, each invalid value is in an array, and each valid value is shown directly. An expression such asCOALESCE(error_data:T[0], error_data:T)gets the column value either way. - The keys in the
error_datavalue are the column names, which are uppercase unless the columns were created with quoted, lowercase names. - For an expression evaluation error, such as division by zero, the array can contain more than one value. For
example, in Record errors when inserting rows from one table into another table, the array
[1, 0]contains both inputs of1/col1, so[0]returns1, not the source value0. For a column that the statement computes with an expression, get the source values from the statement’s source data instead, such as by joining on the key values in theerror_datavalue.
- In the
-
Correct the values, either in the source data or in a statement that converts them.
-
Write only the corrected rows. For an
INSERTstatement, insert the corrected rows. For anUPDATEor aMERGEstatement, run the statement again with a condition that selects only the rows that failed, such as a condition on the key values of the rows in the error table.Error logging is still turned on, so a corrected row that still has an error is recorded in the error table again instead of causing the statement to fail. Right after the statement, run the following query in the same session:
If the query returns rows, the statement didn’t write the rows that the query returns, but it wrote the other corrected rows. Don’t run the statement again for all of the corrected rows, because it writes the other rows a second time. Instead, repeat this procedure from step 1 for these rows, with the
query_idvalue that the query returns. -
Remove the errors from the error table, so that you don’t correct and write the same rows again later.
TRUNCATEremoves every error, including errors from loads and from statements that run while you work, and you can’t delete individual errors.If pipes or
COPY INTOstatements also load the table, follow Stop loads into the table and Clear the error table and resume loads instead. Otherwise, do the following:-
Suspend the tasks and stop the applications that run
INSERT,UPDATE, orMERGEstatements on the table. -
Copy the errors that you want to keep to another table, and then truncate the error table. The copy is a regular table, so grant access to it only to roles that are allowed to see the source data:
The next time that you archive errors, add them to the same table with
INSERT INTO my_table_errors_archive SELECT CURRENT_TIMESTAMP(), * FROM ERROR_TABLE(my_table). -
Resume the tasks and applications that you stopped.
For more information, see Query and truncate an error table.
-
Comparison with Oracle LOG ERRORS INTO¶
If you’re migrating from Oracle, DML error logging is similar to the LOG ERRORS INTO clause, with the following
differences:
- You turn on error logging as a property of the table, not in each statement. Every
INSERT,UPDATE, andMERGEstatement on the table records supported errors instead of failing. - Snowflake creates the error table automatically, and each base table has one error table. To query it, use the
ERROR_TABLEtable function. There’s no tag for each statement. To find the errors from one statement, filter on thequery_idcolumn. - There’s no reject limit. A statement records every supported error and writes the other rows. To fail a job when a statement records errors, check the error table after the statement, as described in Detect errors in a scheduled job.
- Errors that a statement records are rolled back if the transaction that contains the statement is rolled back. Oracle writes errors in an autonomous transaction, so they’re kept even if the transaction is rolled back.
- Snowflake doesn’t enforce PRIMARY KEY, UNIQUE, or FOREIGN KEY constraints on standard tables, so rows that violate these constraints are written, not recorded as errors. For the types of errors that are recorded, see Supported errors.
The following table lists the Snowflake equivalents of the Oracle elements:
| Oracle | Snowflake |
|---|---|
LOG ERRORS INTO <error_table> in each statement | ALTER TABLE <table_name> SET ERROR_LOGGING = TRUE, once for the table. |
DBMS_ERRLOG.CREATE_ERROR_LOG | None. Snowflake creates the error table when you turn on error logging. |
The tag in LOG ERRORS INTO <error_table> ('<tag>') and the ORA_ERR_TAG$ column | The query_id column. |
The ORA_ERR_NUMBER$ and ORA_ERR_MESG$ columns | The error_code column and the error_metadata:error_message field. |
| The columns for the values of the row | The error_data column. |
REJECT LIMIT | None. Check the error table after the statement. |
Usage notes¶
The following usage notes apply when error logging is turned on for a table:
- There’s no limit on the number of errors that a statement records. To fail a job when a statement records errors, check the error table after the statement, as described in Detect errors in a scheduled job.
- Errors that a statement records are rolled back if the transaction that contains the statement is rolled back.
- If you replace the table with
CREATE OR REPLACE TABLE, for example to rebuild it, Snowflake drops its error table and the errors in it. Copy the errors that you want to keep first, and setERROR_LOGGING = TRUEin the statement if the new table must record errors. For more information, see Error table lifecycle. - If the OPT_OUT_ERROR_LOGGING parameter is set to
TRUEfor a session, statements in the session don’t record errors. A statement that encounters a supported error fails, as if error logging were turned off. - The following are considerations related to DML error logging and performance:
- When error logging is turned on for a base table, and there are no errors in a DML statement that is run on the base table, there’s little or no difference in performance.
- When error logging is turned on for a base table, and there are errors in a DML statement that is run on the base table, additional time is required to complete the DML statement because the error information is inserted into the error table.