Skip to main content

Troubleshooting Snowpipe Failures

When Snowpipe stops loading data, identifying the root cause can be challenging because there is no immediate user-facing error message (unlike running a manual query).

This module covers the most common Snowpipe failure scenarios and how to resolve them.

Topics Covered​

  • Files Not Loading & COPY_HISTORY Errors
  • The "Stale Pipe" Problem
  • Manual Pipe Refresh for Missed Files
  • SQS Queue Bottlenecks
  • Load Deduplication (The 14-Day Rule)

Files Not Loading & Schema Mismatches​

If files are arriving in your S3/GCS bucket but aren't showing up in the Snowflake table, the first step is to check if Snowflake attempted to load them and failed.

-- Check the load history for errors
SELECT *
FROM table(information_schema.copy_history(
TABLE_NAME => 'BEEMS_INGEST_DB.SNOWPIPE_LAB.APP_LOGS',
START_TIME => DATEADD(hours, -24, CURRENT_TIMESTAMP())
))
WHERE STATUS = 'LOAD_FAILED';

Common Causes for LOAD_FAILED:

  1. Data Type Mismatch: A CSV file contains the string "N/A" for a column that is defined as an INT.
  2. Schema Evolution: The upstream software team added a new column to the CSV, but didn't tell the data engineering team to ALTER the Snowflake table.
    • Fix: Use the ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE format option if you want to ignore new columns temporarily, or better yet, load data into a VARIANT column to handle schema drift dynamically.

The "Stale Pipe" Problem​

If SYSTEM$PIPE_STATUS('MY_PIPE') returns STALLED or PAUSED, or if COPY_HISTORY shows absolutely nothing despite files landing in the bucket, your pipe is likely stale.

Causes of a Stale Pipe:

  1. Someone manually ran ALTER PIPE MY_PIPE set PIPE_EXECUTION_PAUSED = true;.
  2. The Cloud Provider (e.g., AWS) revoked Snowflake's IAM Role access to the S3 bucket or SQS queue.
  3. The table the pipe points to was dropped and recreated, breaking the internal link.

Hands-On Demonstration: Manual Pipe Refresh​

Let's assume our pipeline was paused for 2 days due to a misconfiguration, and we just fixed the IAM role. Snowpipe will not automatically go back and load the files that arrived while it was broken. It only listens for new event notifications.

To force Snowpipe to scan the bucket and load the missed files, we must use the REFRESH command.

(To execute this, you must have the OPERATE privilege on the pipe. Check your current user with SELECT CURRENT_USER();)

USE ROLE SYSADMIN;

-- Step 1: Ensure the pipe is resumed
ALTER PIPE BEEMS_INGEST_DB.SNOWPIPE_LAB.LOG_INGESTION_PIPE SET PIPE_EXECUTION_PAUSED = false;

-- Step 2: Force Snowpipe to scan the bucket for missed files.
-- We use MODIFIED_AFTER to prevent it from scanning the entire historical bucket.
ALTER PIPE BEEMS_INGEST_DB.SNOWPIPE_LAB.LOG_INGESTION_PIPE REFRESH
MODIFIED_AFTER = '2023-10-01T12:00:00-00:00';

Snowpipe will now queue all files that arrived after that timestamp and load them.

Duplicate Load Prevention (The 14-Day Rule)​

When you run ALTER PIPE ... REFRESH, you might worry: "Will I accidentally load data twice and create duplicates?"

Snowflake maintains a hidden Load History metadata table for every target table. For 14 days, Snowflake remembers the exact name and checksum of every file it has successfully loaded.

If you run REFRESH, Snowflake will see the files, check the metadata, and ignore any file it has already loaded within the last 14 days. Note: If the file was loaded 15 days ago and is still in the bucket, running REFRESH without a MODIFIED_AFTER clause will result in duplicates!

Diagnosing SQS Queue Bottlenecks​

If SYSTEM$PIPE_STATUS shows a rapidly growing pendingFileCount, your pipe is falling behind.

Causes of Throughput Bottlenecks:

  1. Tiny Files: If your upstream system in Delhi is sending 10,000 files per minute, and each file is only 1KB, Snowpipe's event queue will choke. You must batch files to at least 10MB-100MB upstream.
  2. Complex Transformations: If your pipe's COPY INTO statement includes complex SQL transformations (like heavy regex parsing), the serverless compute takes longer to process each file. Move complex logic downstream (ELT) rather than doing it inside the pipe.

Summary​

  • Use COPY_HISTORY to diagnose data type and schema mismatch errors.
  • If a pipe breaks, you must use ALTER PIPE ... REFRESH MODIFIED_AFTER to catch up on missed files.
  • Snowflake prevents duplicate file loads automatically, but only for a 14-day window.
  • A growing pendingFileCount usually means files are too small or the COPY statement is too complex.