Skip to main content

Monitoring Snowpipe (PIPE_USAGE_HISTORY, COPY_HISTORY)

Because Snowpipe runs continuously in the background, it's crucial to proactively monitor it. If an upstream system changes a file format or a cloud security policy revokes Snowflake's access to an S3 bucket, Snowpipe will fail silently.

This module covers the views and functions used to track Snowpipe performance, cost, and errors.

Topics Covered​

  • Real-Time Pipe Status (SYSTEM$PIPE_STATUS)
  • Load History for Error Hunting (COPY_HISTORY)
  • Cost Monitoring (PIPE_USAGE_HISTORY)
  • Alerting on Stale Pipes

Real-Time Pipe Status (SYSTEM$PIPE_STATUS)​

The fastest way to check the immediate health of a single pipe is using the SYSTEM$PIPE_STATUS function.

(To run this, your active role must have the MONITOR or OWNERSHIP privilege on the pipe. To check your current user and role, execute SELECT CURRENT_USER(); and SELECT CURRENT_ROLE();)

-- Assume the pipe is named PIPELINE_DB.RAW.SENSORS_PIPE
SELECT SYSTEM$PIPE_STATUS('PIPELINE_DB.RAW.SENSORS_PIPE');

What to look for in the JSON output:

  • executionState: Should be RUNNING. If it says PAUSED, someone manually stopped it. If it says STALLED, there is a critical error (usually a broken Storage Integration).
  • pendingFileCount: How many files are waiting in the queue. If this number is consistently rising, your files might be too large, causing the ingestion queue to fall behind.
  • lastIngestedTimestamp: The exact time the last file successfully loaded.

Load History for Error Hunting (COPY_HISTORY)​

When you suspect data is missing from your target table, you should check the COPY_HISTORY. This view tells you the status of every single file Snowpipe attempted to load.

There are two versions of this view:

  1. INFORMATION_SCHEMA.COPY_HISTORY: Best for immediate, tactical debugging. Only retains data for 14 days. Fast to query.
  2. ACCOUNT_USAGE.COPY_HISTORY: Best for long-term auditing. Retains data for 365 days. Slower to query.

Querying INFORMATION_SCHEMA for Errors​

-- Find files that Snowpipe failed to load in the last 24 hours
SELECT
FILE_NAME,
LAST_LOAD_TIME,
STATUS,
ROW_COUNT,
ROW_PARSED,
FIRST_ERROR_MESSAGE,
FIRST_ERROR_LINE_NUMBER
FROM table(information_schema.copy_history(
TABLE_NAME => 'PIPELINE_DB.RAW.SENSORS_TABLE',
START_TIME => DATEADD(hours, -24, CURRENT_TIMESTAMP())
))
WHERE STATUS = 'LOAD_FAILED'
ORDER BY LAST_LOAD_TIME DESC;

If you see errors like "Number of columns in file (10) does not match that of the corresponding table (9)", an upstream data engineer altered the CSV format without telling you!

Cost Monitoring (PIPE_USAGE_HISTORY)​

Snowpipe is billed based on serverless compute time. To understand how many credits your pipes are burning, query the PIPE_USAGE_HISTORY view in the ACCOUNT_USAGE schema.

-- Find the most expensive pipes over the last 30 days
SELECT
PIPE_NAME,
SUM(CREDITS_USED) AS TOTAL_CREDITS,
SUM(BYTES_INSERTED) / (1024*1024*1024) AS TOTAL_GB_LOADED
FROM SNOWFLAKE.ACCOUNT_USAGE.PIPE_USAGE_HISTORY
WHERE START_TIME >= DATEADD(day, -30, CURRENT_DATE())
GROUP BY PIPE_NAME
ORDER BY TOTAL_CREDITS DESC;

Cost Optimization Tip: If you see a pipe with very high CREDITS_USED but extremely low BYTES_INSERTED, your source system is likely sending thousands of tiny (1KB - 100KB) files. You are paying the Snowpipe overhead penalty. Batch those files upstream into 100MB chunks before sending them to Snowflake.

Setting Up Alerts for Stale Pipes​

The biggest risk with Snowpipe is it silently stopping (e.g., someone deletes the AWS SQS queue by accident). You should proactively monitor for "stale" pipes—pipes that haven't ingested anything when they normally do.

Here is a conceptual example of how a data engineer (like Priya in your Bangalore office) would use Snowflake Alerts to get an email if a high-volume pipe stops loading.

-- Create an alert that runs every hour
CREATE OR REPLACE ALERT STALE_PIPE_ALERT
WAREHOUSE = COMPUTE_WH
SCHEDULE = '60 MINUTE'
-- The Condition: If the pipe hasn't loaded any files in the last 2 hours
IF (EXISTS (
SELECT 1
FROM table(information_schema.copy_history(
TABLE_NAME => 'PIPELINE_DB.RAW.SENSORS_TABLE',
START_TIME => DATEADD(hours, -2, CURRENT_TIMESTAMP())
))
HAVING COUNT(*) = 0
))
-- The Action: Send an email
THEN CALL SYSTEM$SEND_EMAIL(
'MY_EMAIL_INTEGRATION',
'priya.dataops@yourcompany.com',
'ALERT: Snowpipe is Stale!',
'The SENSORS_PIPE has not loaded any files in the last 2 hours. Please investigate.'
);

-- Don't forget to start the alert!
ALTER ALERT STALE_PIPE_ALERT RESUME;

Summary​

  • Use SYSTEM$PIPE_STATUS() to quickly check the queue and execution state.
  • Use INFORMATION_SCHEMA.COPY_HISTORY to read the exact error message for a failed file load.
  • Use ACCOUNT_USAGE.PIPE_USAGE_HISTORY to track serverless credit consumption.
  • Always configure Snowflake Alerts to monitor for stale pipes to avoid silent data pipeline failures.