Snowpipe Architecture
Snowpipe is Snowflake's continuous data ingestion service. It is designed to load data within minutes after files are added to an external stage (like AWS S3, Google Cloud Storage, or Azure Blob) and submit them for ingestion.
This module explores the underlying architecture of Snowpipe and how it provides a near real-time ingestion layer for your data pipelines.
Topics Covered
- Snowpipe vs Bulk
COPY INTOLoading - The Serverless Execution Model
- The Ingestion Queue (Event Notifications)
- Latency and Micro-Batching
- Snowpipe Credit Model
Snowpipe vs Bulk COPY INTO
There are two primary ways to load flat files (CSV, JSON, Parquet) into Snowflake:
| Feature | COPY INTO (Bulk Load) | Snowpipe |
|---|---|---|
| Execution Trigger | Manual or Scheduled Task | Automated via Cloud Event Notifications |
| Compute Engine | Virtual Warehouse | Snowflake Serverless Compute |
| Latency | Batch (e.g., Hourly or Daily) | Near Real-Time (typically ~1 minute) |
| Data Size | Massive files (GBs) loaded at once | Thousands of small files (100MB-250MB) loaded continuously |
The Serverless Execution Model
The most critical architectural difference is that Snowpipe does not use a Virtual Warehouse.
If you use a scheduled task to run COPY INTO, your Virtual Warehouse must wake up, run the command, and stay awake until it auto-suspends. If you are doing this every 5 minutes, your warehouse will never suspend, costing you significant compute credits.
Snowpipe is Serverless. Snowflake manages a pool of compute resources in the background. When files arrive, Snowpipe borrows just enough compute from this shared pool to load the file, and then immediately releases it. You only pay for the exact per-second compute required to load the files.
Hands-On Demonstration: Simulating the Architecture
To understand the architecture, we will set up the target table and build a Snowpipe object, simulating how a data engineering team in Chennai would configure a continuous pipeline for mobile app logs.
1. Database & Schema Setup
Copy and paste the following code into your Snowflake worksheet.
USE ROLE SYSADMIN;
CREATE DATABASE IF NOT EXISTS BEEMS_INGEST_DB;
USE DATABASE BEEMS_INGEST_DB;
CREATE SCHEMA IF NOT EXISTS SNOWPIPE_LAB;
USE SCHEMA SNOWPIPE_LAB;
2. The Target Table and Stage
First, we need a table to receive the data and an external stage where the files land.
-- Target table for Mobile App Logs
CREATE OR REPLACE TABLE SNOWPIPE_LAB.APP_LOGS (
LOG_TIMESTAMP TIMESTAMP,
USER_ID STRING,
CITY STRING,
ACTION STRING
);
-- (Conceptual) External Stage pointing to an AWS S3 bucket
-- CREATE OR REPLACE STAGE SNOWPIPE_LAB.APP_STAGE
-- URL='s3://beems-chennai-bucket/mobile_logs/'
-- STORAGE_INTEGRATION = S3_INT;
3. Creating the Snowpipe Object
A pipe is essentially a wrapper around a COPY INTO statement. It tells Snowflake what file format to expect and where to put the data.
-- Create the Pipe
CREATE OR REPLACE PIPE SNOWPIPE_LAB.LOG_INGESTION_PIPE
AUTO_INGEST = TRUE
AS
COPY INTO SNOWPIPE_LAB.APP_LOGS
FROM @SNOWPIPE_LAB.APP_STAGE
FILE_FORMAT = (TYPE = 'JSON');
(To check who created this pipe and therefore who owns it, run SELECT CURRENT_USER(); and SELECT CURRENT_ROLE();)
4. The Ingestion Queue (Event Notifications)
This is how the architecture actually flows in production:
- A mobile app user in Tamil Nadu clicks a button. The app backend drops a JSON file into the S3 bucket.
- The AWS S3 bucket is configured to trigger an Event Notification (e.g., an SQS message or SNS topic) telling Snowflake "A new file named
log_123.jsonhas arrived!" - Snowpipe reads this SQS message, adds
log_123.jsonto its internal ingestion queue, and provisions serverless compute to execute theCOPY INTOstatement defined in the pipe.
5. Simulating the Ingestion
Let's insert mock data that simulates the result of Snowpipe running successfully over a 5-minute window.
INSERT INTO SNOWPIPE_LAB.APP_LOGS VALUES
(CURRENT_TIMESTAMP(), 'U-001', 'Chennai', 'Login'),
(CURRENT_TIMESTAMP(), 'U-002', 'Madurai', 'View Product'),
(CURRENT_TIMESTAMP(), 'U-003', 'Coimbatore', 'Add to Cart');
SELECT * FROM SNOWPIPE_LAB.APP_LOGS;
Latency and Micro-Batching
Snowpipe does not load files instantly. It performs Micro-Batching. When a file notification arrives, Snowpipe usually waits a short period (around 60 seconds) to see if more files arrive. It groups these files into a small batch and loads them together to optimize compute efficiency and file compaction.
Expect a typical latency of 1 to 2 minutes between a file landing in S3 and it being queryable in Snowflake.
Snowpipe Credit Model
Because Snowpipe is serverless, billing works differently:
- You are billed based on the compute time required to load the files, measured in seconds.
- You are billed a small overhead charge (0.06 credits per 1000 files queued) to maintain the SQS/Event Grid connection.
- Cost Optimization Tip: Do not use Snowpipe to load millions of tiny 1KB files. The overhead charge will skyrocket. The optimal file size for Snowpipe is roughly 100MB to 250MB compressed.
Summary
- Snowpipe is an automated, continuous ingestion service.
- It is Serverless; it uses Snowflake-managed compute, not your Virtual Warehouses.
- It relies on Cloud Event Notifications (SQS, Event Grid, Pub/Sub) to know when to load a file.
- It provides Micro-Batching with typical latencies of 1-2 minutes.