Troubleshooting COPY INTO and Snowpipe Load Errors

1.Start with the load history

Snowflake documents the same method for bulk loads and for Snowpipe: first, query the load activity history for the target table. COPY_HISTORY reports a STATUS for each set of files: loaded, partially loaded or failed. When a load partially loaded or failed, the FIRST_ERROR_MESSAGE column gives the reason. As the name says, that column holds only the first error found in a file. A file with ten bad rows still shows one message.

There are two versions of this history, and they differ in ways that matter during an incident. The COPY_HISTORY view in the ACCOUNT_USAGE schema, which also backs the account-level Copy History page in Snowsight, covers 365 days for every table in the account. Its latency is up to two hours. The COPY_HISTORY table function, which backs the table-level Copy History tab, covers only 14 days for one table but has very low latency. Both include bulk COPY INTO statements, pipe loads and files loaded through the web interface. In Snowsight you can filter by status (In progress, Loaded, Failed, Partially loaded, Skipped) and hover over a Failed entry to see the error details.

Permissions affect what you see. The table function needs the MONITOR privilege on the account, or USAGE on the database and schema plus any privilege on the table. Without MONITOR on the pipe, pipe details are masked as NULL. Don't mistake that for a load that didn't use a pipe.

Sources12

2.Seeing every error, not just the first

Because FIRST_ERROR_MESSAGE stops at one error, the next step is to run a COPY statement with VALIDATION_MODE set to RETURN_ALL_ERRORS. Point it at the same files you tried to load. The statement validates the data and returns the errors without loading anything, so you can run it safely against production tables.

The documented pattern goes one step further and turns the error list into a file you can fix. It captures the validation query's ID, reads the rejected records back through RESULT_SCAN, and unloads them to a stage. These statements must run one after another in the same session, because LAST_QUERY_ID returns the ID of the query that ran just before it.

For files that Snowpipe tried to load, the same validation applies: run COPY INTO <table> with RETURN_ALL_ERRORS against those files. The Snowpipe troubleshooting guide also points to the VALIDATE_PIPE_LOAD function for validating data files after a pipe load hits errors.

COPY INTO mytable
  FROM @mystage/myfile.csv.gz
  VALIDATION_MODE=RETURN_ALL_ERRORS;

SET qid=last_query_id();

COPY INTO @mystage/errors/load_errors.txt FROM (SELECT rejected_record FROM TABLE(result_scan($qid)));

COPY INTO mytable
  FROM @mystage/myfile.csv.gz
  VALIDATION_MODE= ? ;

Sources13

3.When auto-ingest loads nothing: reading the pipe status

With auto-ingest Snowpipe, the load history may be empty because no event ever reached the pipe. SYSTEM$PIPE_STATUS returns the pipe's state as JSON. Each field points to a different part of the event chain.

The path check catches many people out. The path in the pipe definition is appended to the path in the stage definition, so the effective location is the two combined. Only messages triggered by created objects are consumed. If messages are both received and forwarded, the problem is in the data, so go back to COPY_HISTORY.

Several causes are specific to one cloud:

- Amazon SNS. The first pipe that references an SNS topic subscribes a Snowflake-owned SQS queue to that topic. If an AWS administrator deletes the subscription, every pipe on that topic stops receiving S3 events. To fix it, wait 72 hours for SNS to clear the deleted subscription, then recreate the pipes with CREATE OR REPLACE PIPE on the same topic. To skip the wait, create a topic with a different name and recreate the pipes against that topic instead. - Google Cloud Storage. Only one staged file's event is read, or loads are delayed by minutes to a day or more. The usual cause is that the Snowflake service account was never granted the Monitoring Viewer role. - Azure ADLS Gen2. Some third-party clients don't call FlushWithClose, and that call is what triggers the event. Call the REST API manually. - Snowpipe REST API. The endpoints use key pair authentication with a JWT. A request without a token returns error 400. An invalid token returns 401 with "JWT token is invalid".

Instead of polling, you can have Snowpipe push error notifications to the cloud messaging service where your account is hosted (there is no cross-cloud support). These notifications only fire when ON_ERROR is SKIP_FILE, which is the default. NOTIFICATION_HISTORY shows what was sent.

Sources34

4.Files skipped, files duplicated, and broken stage links

Sometimes the pipe works but particular files are missing or loaded twice. Each case has a documented cause.

Expected files missing from COPY_HISTORY. Query an earlier time window. If the files duplicated earlier ones, the history may have recorded them when the originals were attempted. Also check the pipe's COPY statement for a PATTERN clause whose regular expression filters out every staged file. Changing PATTERN means recreating the pipe with CREATE OR REPLACE PIPE.

A subset of files never loaded. Run ALTER PIPE … REFRESH. This is the fix when the external stage was previously used for bulk COPY, when files were already in the stage before event notifications were set up, or when a notification failure stopped files from being queued.

Fixed files that still won't load. Snowpipe's load metadata belongs to the pipe, not the table. A staged file with the same name as one already loaded is ignored even if its contents changed (a different eTag). Files that failed during the pipe's COPY are still registered in that metadata, and later pipe activity, including REFRESH, skips them. Load those files manually with a COPY statement. TRUNCATE TABLE does not clear this metadata.

Duplicated rows. Run SHOW PIPES and compare the COPY statements. If two pipes reference overlapping paths such as <storage_location>/path1/ and <storage_location>/path1/path2/, files staged in the deeper path are loaded by both pipes.

Error 003139, "Integration … cannot be found". A stage links to its storage integration through a hidden ID, not the integration's name. CREATE OR REPLACE STORAGE INTEGRATION drops the integration and recreates it with a new ID, which breaks every stage linked to it. Re-link each stage with ALTER STAGE … SET STORAGE_INTEGRATION.

Load timestamps that look too early. A column defaulting to CURRENT_TIMESTAMP is evaluated when the load is compiled, not when it commits, so its values come before COPY_HISTORY's LOAD_TIME. Use METADATA$START_SCAN_TIME instead.

Sources31

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Truncating the target table lets Snowpipe reload files it already processed.Why is that wrong?

   Snowpipe's file-loading metadata belongs to the pipe, not the table, so TRUNCATE TABLE leaves it in place and those file names are still ignored.

   Covered in Files skipped, files duplicated, and broken stage links

2. 2.Setting ON_ERROR=CONTINUE on a pipe still sends you error notifications for the rows it skips.Why is that wrong?

   Error notifications only work with ON_ERROR=SKIP_FILE, the default. With CONTINUE, Snowpipe sends none.

   Covered in When auto-ingest loads nothing: reading the pipe status

3. 3.After someone deletes the SQS subscription to the SNS topic, recreating the pipes right away restores S3 events on the same topic.Why is that wrong?

   You have to wait 72 hours for SNS to clear the deleted subscription before recreating pipes on that topic. The alternative is a topic with a new name.

   Covered in When auto-ingest loads nothing: reading the pipe status