What is Snowpipe? Automated Data Ingestion in Snowflake

Snowpipe is a serverless data ingestion service that continuously and automatically loads files arriving in cloud storage into Snowflake tables. When a new file lands in S3, Azure Blob Storage, or GCS, an event notification triggers Snowpipe and the data is reflected in the target table within minutes. It is a frequently tested topic in the Data Loading / Data Transformation domain of the SnowPro Core exam.

Snowpipe Architecture

Snowpipe runs on serverless compute in the Cloud Services Layer. It does not consume your Virtual Warehouse and is billed in Serverless Credits based on the volume of data loaded. As a result, you do not need to keep a Warehouse running at all times.

Cloud Storage (S3 / Blob / GCS)
  │  File arrives
  ▼
Event Notification (SQS / EventGrid / Pub/Sub)
  │  Notification message
  ▼
Snowpipe (Cloud Services Layer - Serverless)
  │  COPY INTO executes
  ▼
Target Table (Storage Layer)

Auto-ingest: Automatic Ingestion via SQS Notifications

With auto-ingest on AWS, S3 event notifications (SQS) are wired directly into Snowpipe. Every time a file arrives in the S3 bucket, an SQS message is sent to Snowpipe's queue and the load kicks off automatically. The same pattern is available on Azure (EventGrid) and GCP (Pub/Sub).

-- Create an auto-ingest enabled pipe
CREATE OR REPLACE PIPE sales_pipe
  AUTO_INGEST = TRUE
AS
COPY INTO analytics.raw_sales
FROM @sales_stage/daily/
FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1 FIELD_OPTIONALLY_ENCLOSED_BY = '"')
ON_ERROR = 'SKIP_FILE';

Specifying AUTO_INGEST = TRUE tells Snowflake to internally generate an SQS queue ARN. Binding that ARN to the S3 bucket's event notification configuration completes the file-arrival-triggered pipeline.

Triggering via the REST API

Instead of using auto-ingest, you can also trigger loads by calling the Snowpipe REST API from an external orchestration tool such as Airflow or AWS Lambda. POST a list of file paths to the insertFiles endpoint to load only the files you specify.

-- Pipe for REST API use (AUTO_INGEST = FALSE)
CREATE OR REPLACE PIPE batch_pipe
AS
COPY INTO analytics.raw_events
FROM @event_stage/
FILE_FORMAT = (TYPE = PARQUET);

-- Check pipe status
SELECT SYSTEM$PIPE_STATUS('batch_pipe');

REST API triggering is useful when you want to integrate Snowpipe with an existing workflow management tool, or when you need precise external control over when loads happen.

Monitoring Loads with COPY_HISTORY

Check Snowpipe load results with COPY_HISTORY. The INFORMATION_SCHEMA version is near real-time and retains the last 14 days; the ACCOUNT_USAGE version retains up to 1 year of history.

-- Check the last 24 hours of load history
SELECT
  file_name,
  stage_location,
  status,
  row_count,
  error_count,
  first_error_message,
  last_load_time
FROM TABLE(INFORMATION_SCHEMA.COPY_HISTORY(
  TABLE_NAME => 'ANALYTICS.RAW_SALES',
  START_TIME => DATEADD(HOURS, -24, CURRENT_TIMESTAMP())
))
ORDER BY last_load_time DESC;

Snowpipe vs. Snowpipe Streaming

Snowpipe Streaming, which became GA in 2023, is a row-level real-time ingestion feature. Because it bypasses files entirely, latency drops to sub-second levels.

When to Use COPY INTO vs. Snowpipe

COPY INTO is a bulk load command that runs on a Warehouse. The fundamental differences from Snowpipe lie in the execution platform and what triggers the load.

Operational Best Practices

• Optimize file size: Snowpipe works with small files, but compressed files of roughly 100-250 MB are considered the most cost-efficient
• ON_ERROR option: In production, setting SKIP_FILE quarantines failing files so you can deal with them individually later — a more stable design
• Monitor cost via PIPE_USAGE_HISTORY: Use the ACCOUNT_USAGE.PIPE_USAGE_HISTORY view to track credit consumption per pipe
• Ensure idempotency: Include a timestamp or UUID in every file name so duplicates are reliably eliminated within the 14-day metadata retention window

Check Your Understanding

Frequently Asked Questions