What Is Snowpipe?
Snowpipe is Snowflake's serverless, event-driven service for loading data continuously as files arrive in a cloud storage stage, rather than waiting for a scheduled batch job to run. Under the hood a pipe wraps a COPY INTO statement, but instead of you executing it manually or on a cron schedule, Snowflake triggers it automatically when new files land in S3, Azure Blob Storage, or Google Cloud Storage. Because Snowpipe runs on Snowflake-managed serverless compute rather than a customer virtual warehouse, you don't need to size or manage a warehouse for ingestion, and billing is metered per second of actual compute used plus a per-file overhead.
Cricket analogy: Like a boundary fielder relaying the ball back the instant it crosses the rope instead of waiting for the over to end, Snowpipe reacts to each file landing in storage rather than waiting for a scheduled batch window like a team waiting for the drinks break.
How Auto-Ingest Snowpipe Works
Auto-ingest Snowpipe is set up by creating a pipe object with CREATE PIPE ... AUTO_INGEST = TRUE, and pointing your cloud provider's event notification service (S3 Event Notifications through SQS, Azure Event Grid, or GCS Pub/Sub) at that stage. When a new file lands, the cloud provider pushes a notification, Snowflake's internal queue picks it up, and the pipe's COPY INTO runs against just that file using serverless compute resources sized automatically to the workload. Load latency is typically measured in seconds to a couple of minutes, which is a major improvement over polling-based or manually scheduled batch loads, and it removes the operational burden of managing a dedicated warehouse purely for ingestion.
Cricket analogy: Like a DRS (Decision Review System) that's wired directly into the stump microphone and automatically flags an edge the instant the ball nicks the bat, rather than an umpire manually reviewing footage on request.
-- Create a stage pointing at cloud storage
CREATE OR REPLACE STAGE raw_events_stage
URL = 's3://my-bucket/raw/events/'
STORAGE_INTEGRATION = my_s3_integration
FILE_FORMAT = (TYPE = 'JSON');
-- Create an auto-ingest pipe
CREATE OR REPLACE PIPE raw_events_pipe
AUTO_INGEST = TRUE
AS
COPY INTO raw_events
FROM @raw_events_stage
FILE_FORMAT = (TYPE = 'JSON')
ON_ERROR = 'CONTINUE';
-- Grab the notification channel ARN to wire up S3 event notifications
SHOW PIPES LIKE 'raw_events_pipe';
-- Check recent ingestion history
SELECT *
FROM TABLE(INFORMATION_SCHEMA.COPY_HISTORY(
TABLE_NAME => 'RAW_EVENTS',
START_TIME => DATEADD('hour', -1, CURRENT_TIMESTAMP())
));File Sizing and the REST API
Snowpipe also exposes a REST API (insertFiles and insertReport endpoints) for cases where you want your own application or orchestration tool to notify Snowflake that files are ready, rather than relying on cloud-native event notifications. Regardless of trigger mechanism, Snowpipe's per-file overhead means ingesting many tiny files is inefficient: Snowflake's own guidance recommends staging compressed files in the 100-250 MB range to get a good balance between load latency and per-file fixed costs. Loading thousands of 1 KB files instead of a handful of well-sized ones can make the fixed overhead dominate the actual data transfer cost.
Cricket analogy: Like sending one well-packed kit bag with all your gear to an away match instead of mailing your bat, pads, and gloves separately in a dozen small parcels, each incurring its own shipping fee.
Snowflake retains Snowpipe load history metadata for 14 days, which is what backs the COPY_HISTORY function and the pipe's load status. If you need longer audit history, query and archive it into a permanent table before that window closes.
Snowpipe Streaming
Snowpipe Streaming is a separate, lower-latency ingestion path that writes rows directly into Snowflake tables via an API (commonly used through the Kafka connector's streaming mode) without first staging files in cloud storage at all. This avoids the file-staging and notification round trip entirely, cutting latency from the seconds-to-minutes range down toward sub-second to single-digit-second latency, which matters for near-real-time dashboards and operational alerting use cases. It's billed differently from file-based Snowpipe, based on the volume of data ingested through the streaming SDK rather than per-file and per-second compute charges.
Cricket analogy: Like a stadium's ball-tracking radar (Hawk-Eye) streaming trajectory data straight to broadcast graphics in real time, instead of first recording footage to disk and processing it afterward.
Snowpipe auto-ingest can silently miss files if the event notification configuration is misconfigured or the pipe is paused for too long, since Snowflake's notification queue has retry limits. Always monitor pipe status with SYSTEM$PIPE_STATUS and set up alerting on load failures rather than assuming ingestion is always healthy.
- Snowpipe loads data continuously and automatically as files arrive in a cloud storage stage, wrapping a COPY INTO statement.
- Auto-ingest uses cloud-native event notifications (S3 SQS, Azure Event Grid, GCS Pub/Sub) to trigger loads within seconds to minutes.
- Snowpipe runs on serverless compute billed per second of use plus per-file overhead, not on a customer-managed warehouse.
- File sizing matters: aim for 100-250 MB compressed files to avoid per-file overhead dominating ingestion cost and latency.
- The REST API (insertFiles/insertReport) lets custom applications trigger loads instead of relying on cloud event notifications.
- Snowpipe Streaming writes rows directly to tables via an API, bypassing file staging for sub-second to single-digit-second latency.
- Monitor pipe health with SYSTEM$PIPE_STATUS and COPY_HISTORY, since load history metadata is only retained for 14 days.
Practice what you learned
1. What triggers an auto-ingest Snowpipe to load a new file?
2. What compute does file-based Snowpipe use to execute its COPY INTO?
3. What is the recommended compressed file size range for efficient Snowpipe ingestion?
4. How does Snowpipe Streaming differ from file-based auto-ingest Snowpipe?
5. How long does Snowflake retain Snowpipe load history metadata used by COPY_HISTORY?
Was this page helpful?
You May Also Like
Tasks and Streams
Understand how Snowflake Streams capture change data and how Tasks schedule and chain SQL automation, including the common stream-and-task CDC pattern.
Cost Management and Credits
Explains how Snowflake's credit-based consumption pricing works across compute, storage, and cloud services, and the tools available to monitor and control spend.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics
ProgrammingPowerShell Study Notes
Programming · 30 topics