📖

Key Terms — Snowpipe

—

Snowpipe

Serverless continuous data loading service that automatically ingests files as they arrive in a stage. No virtual warehouse required.

—

Auto-Ingest

Snowpipe mode where cloud event notifications (SQS, Event Grid, Pub/Sub) automatically trigger file loading.

—

REST API

Snowpipe's programmatic interface for manually triggering file ingestion via insertFiles() and checking status via insertReport().

—

Pipe

A Snowflake object (CREATE PIPE) that defines the COPY INTO statement used by Snowpipe.

—

Snowpipe Streaming

Low-latency row-by-row ingestion via the Snowflake Ingest SDK — sub-second latency without staging files.

What is Snowpipe?

Snowpipe is a serverless continuous data ingestion service. Unlike bulk COPY INTO which you run manually or on a schedule, Snowpipe automatically loads data as files arrive in a stage — with no virtual warehouse needed.

Snowpipe Architecture

Flow diagram: 1) Files arrive in cloud storage (S3/Blob/GCS). 2) Cloud event notification triggers (SQS for AWS, Event Grid for Azure, Pub/Sub for GCP). 3) Snowflake Cloud Services layer receives notification. 4) Serverless compute loads file into target table. 5) Load metadata updated. No virtual warehouse involved — all serverless.

Flow from file arrival through cloud notification to serverless loading into Snowflake table

🎯Exam Focus: Snowpipe vs COPY INTO

Snowpipe = continuous, serverless, event-driven, per-file billing. COPY INTO = batch, warehouse-based, manual/scheduled. Snowpipe tracks loaded files for 14 days vs COPY INTO’s 64 days.

Auto-Ingest Mode

The recommended approach uses cloud event notifications to trigger Snowpipe automatically:

📋

Quick Reference

Auto-Ingest by Cloud Provider

🟠

AWS

Service

S3 Event Notification → SQS Queue → Snowpipe

Setup

Configure S3 event on ObjectCreated → SQS → Snowflake notification integration

🔵

Azure

Service

Azure Event Grid → Snowpipe

Setup

Configure Blob Storage events → Event Grid → Snowflake notification integration

🟢

GCP

Service

Google Cloud Pub/Sub → Snowpipe

Setup

Configure GCS notifications → Pub/Sub topic → Snowflake notification integration

Creating a Snowpipe with Auto-Ingest

1-- Create notification integration (AWS example)
2CREATE NOTIFICATION INTEGRATION my_s3_notification
3ENABLED = TRUE
4TYPE = QUEUE
5NOTIFICATION_PROVIDER = AWS_SQS
6DIRECTION = INBOUND
7AWS_SQS_ARN = 'arn:aws:sqs:us-east-1:123456789:my-queue';
8
9-- Create the pipe with AUTO_INGEST
10CREATE OR REPLACE PIPE sales_pipe
11AUTO_INGEST = TRUE
12AS
13COPY INTO sales.raw_orders
14FROM @sales_stage
15FILE_FORMAT = (TYPE = JSON STRIP_OUTER_ARRAY = TRUE)
16MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;

REST API Mode

For custom orchestration, use Snowpipe’s REST API:

REST API Endpoints

1-- insertFiles: submit files for loading
2-- POST https://<account>.snowflakecomputing.com/v1/data/pipes/<pipe>/insertFiles
3-- Body: { "files": [{"path": "file1.csv"}, {"path": "file2.csv"}] }
4
5-- insertReport: check load status
6-- GET https://<account>.snowflakecomputing.com/v1/data/pipes/<pipe>/insertReport
7
8-- loadHistoryScan: scan load history
9-- GET https://<account>.snowflakecomputing.com/v1/data/pipes/<pipe>/loadHistoryScan

Pipe Management

Managing Pipes

1-- Check pipe status
2SELECT SYSTEM$PIPE_STATUS('sales_pipe');
3-- Returns: executionState, pendingFileCount, lastIngestedTimestamp
4
5-- Pause a pipe
6ALTER PIPE sales_pipe SET PIPE_EXECUTION_PAUSED = TRUE;
7
8-- Resume a pipe
9ALTER PIPE sales_pipe SET PIPE_EXECUTION_PAUSED = FALSE;
10
11-- View pipe definition
12DESCRIBE PIPE sales_pipe;
13SHOW PIPES;
14
15-- View load history
16SELECT * FROM TABLE(INFORMATION_SCHEMA.COPY_HISTORY(
17TABLE_NAME => 'RAW_ORDERS',
18START_TIME => DATEADD('hour', -24, CURRENT_TIMESTAMP())
19));

Snowpipe vs COPY INTO

Snowpipe vs Bulk COPY INTO

Feature

Snowpipe

COPY INTO

Trigger

✓Event-driven (automatic) or REST API

Manual or scheduled (Task)

Compute

✓Serverless — no warehouse needed

Virtual warehouse required

Billing

Per-file serverless credits

Per-second warehouse credits

Latency

✓Near real-time (minutes)

Depends on schedule

Load metadata

14 days

✓64 days

Best for

Continuous small-file ingestion

Large batch loads

🔑14 Days vs 64 Days

Snowpipe tracks loaded files for 14 days. COPY INTO tracks for 64 days. Re-loading the same file within this window is skipped by default (unless FORCE = TRUE for COPY INTO).

Snowpipe Streaming

Snowpipe Streaming Architecture

Unlike regular Snowpipe which requires files in a stage, Snowpipe Streaming uses the Snowflake Ingest SDK to write rows directly to Snowflake tables with sub-second latency. No intermediate files needed. Flow: Application → Snowflake Ingest SDK (Java/Python) → Snowflake table directly.

Direct row ingestion from application through SDK to Snowflake without staging files

ℹ️Snowpipe Streaming vs Regular Snowpipe

Regular Snowpipe: files → stage → load (minutes latency). Snowpipe Streaming: rows → SDK → table directly (sub-second latency). Streaming uses the Snowflake Ingest SDK for Java or Python. Popular for IoT, clickstream, and real-time event data.

StepByStep: Setting Up Snowpipe

Setting Up Snowpipe End-to-End

Create a stage

Create an external stage pointing to your cloud storage bucket.

SQL

CREATE STAGE sales_stage
  URL = 's3://my-bucket/sales/'
  STORAGE_INTEGRATION = my_s3_int
  FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1);

Create the target table

Create the table that Snowpipe will load data into.

SQL

CREATE TABLE sales.raw_orders (
  order_id INT, customer_id INT,
  order_date DATE, amount DECIMAL(12,2)
);

Create the pipe

Define the COPY INTO statement as a pipe with AUTO_INGEST = TRUE.

SQL

CREATE PIPE sales_pipe AUTO_INGEST = TRUE
AS COPY INTO sales.raw_orders
FROM @sales_stage;

💡The COPY INTO in the pipe definition is fixed — you cannot parameterise it.

Configure cloud notifications

Set up S3/Blob/GCS event notifications to send to Snowflake's SQS queue. Get the notification channel ARN from DESCRIBE PIPE.

SQL

DESCRIBE PIPE sales_pipe;
-- Note the notification_channel value for AWS SQS configuration

Verify

Upload a test file and check the status.

SQL

SELECT SYSTEM$PIPE_STATUS('sales_pipe');
SELECT COUNT(*) FROM sales.raw_orders;

Cheat Sheet

📋

Quick Reference

Snowpipe Quick Reference

🚰

Key Facts

Compute

Serverless — no warehouse needed

Latency

Minutes (regular) or sub-second (Streaming)

Metadata

Tracks loaded files for 14 days— vs 64 for COPY INTO

Status

SYSTEM$PIPE_STATUS('pipe_name')

History

PIPE_USAGE_HISTORY in ACCOUNT_USAGE

⌨️

Commands

Create

CREATE PIPE p AUTO_INGEST=TRUE AS COPY INTO ...

Pause

ALTER PIPE p SET PIPE_EXECUTION_PAUSED = TRUE

Resume

ALTER PIPE p SET PIPE_EXECUTION_PAUSED = FALSE

Describe

DESCRIBE PIPE p — shows notification_channel

Practice Quiz

Data Loading

How long does Snowpipe track previously loaded files to prevent duplicate loading?

Data Loading

Which compute resource does Snowpipe use to load data?

Data Loading

What is the key difference between Snowpipe and Snowpipe Streaming?

Flashcards

Data Loading

QUESTION

What are the two modes of triggering Snowpipe?

Click to reveal answer

ANSWER

1) Auto-ingest: cloud event notifications (SQS/Event Grid/Pub-Sub) automatically trigger loading when files arrive. 2) REST API: programmatically call insertFiles() to submit specific files for loading.

Click to see question

Data Loading

QUESTION

How is Snowpipe billed compared to COPY INTO?

Click to reveal answer

ANSWER

Snowpipe: per-file serverless credits — no warehouse needed. COPY INTO: per-second warehouse credits based on warehouse size. Snowpipe is cost-efficient for frequent small files; COPY INTO is better for large batch loads.

Click to see question

Data Loading

QUESTION

What does SYSTEM$PIPE_STATUS return?

Click to reveal answer

ANSWER

JSON object with: executionState (RUNNING/PAUSED), pendingFileCount (files waiting to load), lastIngestedTimestamp, and other status information about the pipe.

Click to see question

Data Loading

QUESTION

How long does Snowpipe track loaded files vs COPY INTO?

Click to reveal answer

ANSWER

Snowpipe: 14 days. COPY INTO: 64 days. Within these windows, re-submitting the same file is skipped to prevent duplicates.

Click to see question

Data Loading

QUESTION

What is the typical latency for Snowpipe vs Snowpipe Streaming?

Click to reveal answer

ANSWER

Regular Snowpipe: minutes (near real-time). Snowpipe Streaming: sub-second latency. Streaming uses the Snowflake Ingest SDK to bypass file staging entirely.

Click to see question

Resources

Next Steps

Reinforce what you just read

Study the All flashcards with spaced repetition to lock it in.

Study flashcards →