Home / Data Platform / Warehouses

Data PlatformTroubleshootingIntermediate

Snowpipe not loading files: pipe status, COPY_HISTORY and safe reloads

Files land in the bucket but never reach the table. Read SYSTEM$PIPE_STATUS and COPY_HISTORY, fix the pipe, notification or file error, then reload the gap without duplicates.

Deepak Bhardwaj7 min read

Applies toSnowflake (all editions), Snowpipe with AUTO_INGEST on Amazon S3, Azure Blob/ADLS Gen2 and Google Cloud Storage

Symptom

Files are arriving in the stage location, but the target table is not growing. Nobody gets an error, because Snowpipe runs in the background and, by default, skips bad files (ON_ERROR = SKIP_FILE) instead of failing loudly.

Work through three questions in order:

  1. Is the pipe running?
  2. Is Snowflake receiving the file notifications?
  3. Did the files load, fail, or get skipped?

1. Is the pipe running?

SELECT SYSTEM$PIPE_STATUS('raw.sales.orders_pipe');

The function returns JSON. Pull out the fields you need:

SELECT PARSE_JSON(SYSTEM$PIPE_STATUS('raw.sales.orders_pipe')) AS s,
       s:executionState::string               AS state,
       s:pendingFileCount::int                AS pending_files,
       s:lastReceivedMessageTimestamp::string AS last_msg,
       s:lastForwardedMessageTimestamp::string AS last_forwarded,
       s:lastIngestedTimestamp::string        AS last_ingested,
       s:error::string                        AS error;

Fields only appear when they apply, so a missing field is normal.

executionState What it means Fix
RUNNING The pipe is healthy. It may or may not be loading at this moment Go to step 2
PAUSED Someone paused it, or ownership was transferred Resume it (below)
STOPPED_CLONED The pipe sits in a cloned database or schema Expected in clones. In the source, check the original pipe
STOPPED_STAGE_ALTERED / STOPPED_STAGE_DROPPED The stage behind the pipe changed or was dropped Recreate the stage, then recreate the pipe
STOPPED_MISSING_TABLE The target table was dropped Recreate or undrop the table, then recreate the pipe
STOPPED_FILE_FORMAT_DROPPED The named file format is gone Recreate the file format
STOPPED_NOTIFICATION_INTEGRATION_DROPPED The notification integration (Azure/GCP) is gone Recreate the integration and the pipe
STALLED_COMPILATION_ERROR The pipe's COPY statement no longer compiles, usually because a column was renamed or dropped Check invalid_reason in SHOW PIPES or DESC PIPE
STALLED_STAGE_PERMISSION_ERROR Snowflake cannot read the external location Fix the storage integration or the IAM role / RBAC grant on the bucket or container
READ_ONLY The pipe is in a secondary (replica) database Expected until the database is promoted 
SHOW PIPES LIKE 'orders_pipe' IN SCHEMA raw.sales;
DESC PIPE raw.sales.orders_pipe;   -- definition, notification_channel, invalid_reason

Resume a paused pipe

ALTER PIPE raw.sales.orders_pipe SET PIPE_EXECUTION_PAUSED = FALSE;

If the pipe changed owner while paused, or was paused for longer than the 14-day message retention period, a plain resume is not enough:

-- Ownership changed while paused
SELECT SYSTEM$PIPE_FORCE_RESUME('raw.sales.orders_pipe');

-- Paused for more than 14 days (auto-ingest pipes)
SELECT SYSTEM$PIPE_FORCE_RESUME('raw.sales.orders_pipe', 'STALENESS_CHECK_OVERRIDE');

Warning

After a stale resume, Snowflake processes notifications older than 14 days on a best-effort basis only. Assume some files were missed and backfill them (section 4).

2. Is Snowflake receiving notifications?

The state is RUNNING but nothing is loading. Compare the timestamps from step 1:

  • lastReceivedMessageTimestamp is old or missing. Event notifications are not reaching Snowflake. The problem is on the cloud side.
  • Received is recent, but lastForwardedMessageTimestamp is old. Messages arrive but do not match the pipe. The usual cause is a path that doesn't match the stage URL or prefix, or the wrong case in a path.
  • Forwarded is recent, but nothing is ingested. The files are reaching COPY. Go to step 3.

Amazon S3

The pipe's SQS queue ARN is in the notification_channel column of SHOW PIPES. Check that the bucket's event notification sends s3:ObjectCreated:* to that ARN, and that the prefix filter matches the stage path:

aws s3api get-bucket-notification-configuration --bucket my-landing-bucket

A common cause is a second tool, such as Terraform or another team's Lambda trigger, overwriting the bucket's notification configuration. S3 keeps one notification configuration per bucket, and each put-bucket-notification-configuration call replaces it completely.

Azure Blob / ADLS Gen2

Check that the Event Grid subscription still exists, filters on Microsoft.Storage.BlobCreated, and delivers to the storage queue named in the notification integration:

az eventgrid event-subscription list \
  --source-resource-id $(az storage account show -n mylandingacct -g rg-data --query id -o tsv) \
  -o table

DESC NOTIFICATION INTEGRATION azure_snowpipe_int;

If the Snowflake service principal lost its role on the queue (Storage Queue Data Contributor) or on the container, notifications stop or files can't be read.

3. Did the files load, fail or get skipped?

SELECT file_name, status, row_count, row_parsed, error_count,
       first_error_message, first_error_line_number, first_error_column_name,
       pipe_received_time, last_load_time
FROM TABLE(information_schema.copy_history(
       TABLE_NAME => 'RAW.SALES.ORDERS',
       START_TIME => DATEADD('hour', -24, CURRENT_TIMESTAMP())))
ORDER BY pipe_received_time DESC;

This table function covers the last 14 days. For longer history, query SNOWFLAKE.ACCOUNT_USAGE.COPY_HISTORY, which can lag by up to a couple of hours.

To see the errors Snowpipe hit for a pipe:

SELECT *
FROM TABLE(information_schema.validate_pipe_load(
       PIPE_NAME  => 'raw.sales.orders_pipe',
       START_TIME => DATEADD('hour', -24, CURRENT_TIMESTAMP())));
What you see Cause Fix
status = 'Load failed', a parsing error File format mismatch: delimiter, header, quoting or compression Fix the file format, then reload (section 4)
Partially loaded ON_ERROR = CONTINUE let bad rows through Decide whether partial data is acceptable, then fix the source
Error on a column count or a type The upstream schema changed Fix the target table or the COPY transform, then recreate the pipe
The file is missing from COPY_HISTORY entirely No notification, a path mismatch, or the file was already loaded under the same name Step 2. To reload a file that was already loaded, rename it in storage
Loaded, but the table is still empty The COPY loads into a different table, or downstream tasks/streams aren't running Check the pipe definition with DESC PIPE

Confirm what is actually in the stage:

LIST @raw.sales.orders_stage/2026/10/26/;

4. Reload the gap safely

Once the cause is fixed, queue the missed files:

ALTER PIPE raw.sales.orders_pipe REFRESH
  PREFIX = '2026/10/26/'
  MODIFIED_AFTER = '2026-10-26T00:00:00Z';

REFRESH only sees files staged within the last 7 days. It checks the load history of both the pipe and the table, so files that are already loaded are skipped. For files older than 7 days, run a one-off COPY INTO with PATTERN or FILES. COPY also skips files recorded in the table's load metadata unless you set FORCE = TRUE.

COPY INTO raw.sales.orders
FROM @raw.sales.orders_stage/2026/10/15/
FILE_FORMAT = (FORMAT_NAME = raw.sales.csv_fmt)
ON_ERROR = ABORT_STATEMENT;

Caution

CREATE OR REPLACE PIPE drops the pipe's load history. A broad REFRESH straight afterwards can queue files the old pipe already loaded. Check COPY_HISTORY first, keep PREFIX and MODIFIED_AFTER tight, and never use FORCE = TRUE on a range that was partly loaded. If you do, you will load duplicates.

To change a pipe's COPY statement, pause the pipe, wait for pendingFileCount to reach 0, recreate the pipe, then resume it and refresh only the window where files may have been missed.

Prevent it next time

  • Alert on freshness, not just failures. Schedule a task or an external check that compares lastIngestedTimestamp, or MAX(last_load_time) from COPY_HISTORY, against the expected arrival interval.
  • Alert on Load failed rows in ACCOUNT_USAGE.COPY_HISTORY every hour.
  • Own the bucket notification in one place. Manage S3 event notifications or Event Grid subscriptions in the same IaC module as the pipe, so no other tool can overwrite them.
  • Treat schema changes as pipe changes. Renaming a table column can stall the pipe. Include SYSTEM$PIPE_STATUS checks in the deployment's smoke test.
  • Never reuse file names for corrected files. Write them with a new name so Snowpipe picks them up.
  • Track Snowpipe cost and volume in SNOWFLAKE.ACCOUNT_USAGE.PIPE_USAGE_HISTORY. A sudden drop in files or bytes is an early warning that ingestion has stopped.

Related fixes