Update existing pipes for error logging and CHECK constraints¶
If a pipe already loads a table, you can start recording every row that the pipe rejects, and you can change the CHECK constraints on the table, without loading any file twice or skipping any file. To turn on error logging for a table that no pipe loads yet, see Error logging for COPY INTO and Snowpipe.
The examples on this page use the orders table and the orders_pipe pipe. For their definitions, see
Turn on error logging and add CHECK constraints and
Load files with ON_ERROR = CONTINUE.
Record errors from an existing pipe¶
A pipe that already loads the table starts recording errors within 15 minutes after you turn on error logging, without being recreated. Rows that it rejects before then aren’t recorded. A pipe that you create after you turn on error logging records errors from its first load.
The pipe records errors according to its ON_ERROR value, which you can change only by recreating the pipe:
- With the default,
SKIP_FILE, the error table records one error for each file that the pipe skips. To record every rejected row, recreate the pipe withON_ERROR = CONTINUE. For how this changes error notifications and other reports, see Choose an ON_ERROR value for a pipe. - Error logging doesn’t support
SKIP_FILE_<num>or'SKIP_FILE_<num>%'. To record errors from a pipe that uses one of these values, recreate the pipe withSKIP_FILEorCONTINUE.
To find the pipes that use the default, SKIP_FILE, list the pipes that load from a stage and whose definition doesn’t
set ON_ERROR. The output includes only the pipes on which your role has the OWNERSHIP, MONITOR, or OPERATE
privilege, unless your role has the global MONITOR EXECUTION privilege:
If you recreate several pipes, recreate one first, and check the result in step 5 before you recreate the others. To recreate a pipe, do the following:
-
Turn on error logging for the target table, if it isn’t turned on already.
-
If you switch to
ON_ERROR = CONTINUEand you rely on error notifications orFAILEDevents, set up another way to find out about errors, such as an alert on the error table. -
Recreate the pipe without loading any file twice or skipping any file by following the steps in Recreate a pipe safely. In step 4 of those steps, set the
ON_ERRORvalue that you chose in the pipe’sCOPY INTOstatement. -
Optional: To load the rows from the files that the old pipe skipped, load the files, as described in Load each skipped file. The new pipe doesn’t load these files. Don’t load a file whose rows you already loaded.
-
After the new pipe loads a file that contains errors, check that the error table contains errors from the pipe with the
ON_ERRORvalue that you chose:Replace
MYDB.MYSCHEMAwith the database and schema that contain your pipe, because thepipe_namevalue is the fully qualified name. The new pipe has the same name as the old pipe, so the query also counts the old pipe’s errors.
Change the CHECK constraints on a table that a pipe loads¶
To change the CHECK constraints on a table that a pipe loads, drop each pipe that loads the table, change the
constraints, and then create the pipes again. You can’t add a CHECK constraint to a table that any pipe loads, and you
can’t drop a CHECK constraint or change it with ALTER TABLE ... ALTER CONSTRAINT on a table that a Snowpipe pipe
loads. Pausing the pipe doesn’t lift either restriction. If you try, the statement fails with one of the following
errors:
Cannot add CHECK constraint to table ... because it has active pipesCannot drop or alter CHECK constraint on table ... because it has active pipes
To find out which pipes block each change, see CHECK constraints. To drop and create the pipes without loading any file twice or skipping any file, follow Recreate a pipe safely, with the following changes:
- If several pipes load the table, do steps 1 to 3 for each pipe before step 4, and steps 5 to 9 for each pipe after step 4.
- Before step 4, also suspend the tasks that run
COPY INTOfor the table, and wait for their runs in progress to finish. Stop the otherCOPY INTOstatements and the applications that write to the table too. Rows that are written while a constraint is dropped aren’t checked, even after you add it again. Resume the tasks and applications after you create the pipes again. For the statements that suspend a task and find its runs in progress, see Stop loads into the table. - In step 4, drop each pipe with
DROP PIPE, change the constraints, and then create each pipe withCREATE PIPEand theON_ERRORvalue that you chose, as described in Choose an ON_ERROR value for a pipe. Don’t useCREATE OR REPLACE PIPE, because you can’t change the constraints until every pipe that loads the table is dropped. To change the condition of a constraint, drop the constraint and add it again.
To add a constraint with ALTER TABLE ... ADD CONSTRAINT, specify ENABLE NOVALIDATE, which doesn’t check the rows
that the table already contains. Without it, the statement fails. For example, the following statements drop the
orders_pipe pipe, add a constraint that rejects orders whose ID is zero or negative, and create the pipe again from
the pipe definition that you saved:
To find the existing rows that violate the constraint, query the table for rows where the condition is FALSE:
After you change the constraints, COPY INTO statements and the pipes that you create again enforce them on their next
load.