Snowpipe (Continuous Data Loading)
Snowflake · Views, Pipelines & Automation
Theory
Snowpipe loads files continuously as they arrive in a stage. A pipe is an object that wraps one COPY INTO statement. With AUTO_INGEST = TRUE, your cloud storage sends an event notification when a new file lands (S3 events through SQS, Azure Event Grid, or Google Pub/Sub), Snowpipe queues the file and loads it, usually within minutes. Pipes can also be triggered by calling the Snowpipe REST API.
Snowpipe runs on Snowflake-managed serverless compute, so no warehouse has to stay running. Like COPY, it keeps load history so the same file is not loaded twice, and its default ON_ERROR is SKIP_FILE.
Why we use it
Running COPY on a schedule means data waits until the next run and a warehouse must wake up each time. Snowpipe loads each file shortly after it arrives, with no warehouse to manage, and is billed a fixed number of credits per GB loaded.
When to use it
Use Snowpipe for files arriving continuously in small batches throughout the day. Use scheduled COPY for large daily or hourly batches. For row-level streaming from apps or Kafka, look at Snowpipe Streaming instead of files.
Event-Driven Loading
Instead of running COPY INTO manually or on a schedule, Snowpipe listens for a cloud storage event notification the moment a new file lands in a stage.
Serverless Compute
Loads run on Snowflake-managed serverless compute within seconds to minutes, so no warehouse has to stay running. Snowpipe is billed as a fixed number of credits per GB loaded, not by warehouse time or the number of files.
Where It Fits in a Pipeline
The standard building block for near-real-time ingestion — pair it with Streams and Tasks downstream to transform data as soon as it lands.
How it works
- New file lands in the bucket
- Cloud storage sends an event notification
- Snowpipe queues the file for its pipe
- Serverless compute runs the pipe's COPY INTO
- Did the file contain errors? Yes: Skip the file (default) and record the error. No: Load the rows into the table.
- Check SYSTEM$PIPE_STATUS and COPY_HISTORY
Example
CREATE OR REPLACE TABLE streaming_orders (
order_data VARIANT, file_name VARCHAR(500) DEFAULT METADATA$FILENAME);
CREATE OR REPLACE PIPE orders_pipe AUTO_INGEST = TRUE AS
COPY INTO streaming_orders (order_data)
FROM @my_s3_stage/orders/ FILE_FORMAT = (TYPE = 'JSON');
SHOW PIPES LIKE 'orders_pipe'; -- use notification_channel for S3 events
SELECT SYSTEM$PIPE_STATUS('orders_pipe');
ALTER PIPE orders_pipe REFRESH;Key points
- Triggered by cloud storage event notifications or the REST API
- Serverless; no user warehouse needs to stay running
- Billed as fixed credits per GB loaded, not per file or warehouse time
- Load history prevents loading the same file twice
- Default ON_ERROR for Snowpipe is SKIP_FILE
- Load order across files is not guaranteed