Skip to main content

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 INTO Loading
  • 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:

FeatureCOPY INTO (Bulk Load)Snowpipe
Execution TriggerManual or Scheduled TaskAutomated via Cloud Event Notifications
Compute EngineVirtual WarehouseSnowflake Serverless Compute
LatencyBatch (e.g., Hourly or Daily)Near Real-Time (typically ~1 minute)
Data SizeMassive files (GBs) loaded at onceThousands 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:

  1. A mobile app user in Tamil Nadu clicks a button. The app backend drops a JSON file into the S3 bucket.
  2. 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.json has arrived!"
  3. Snowpipe reads this SQS message, adds log_123.json to its internal ingestion queue, and provisions serverless compute to execute the COPY INTO statement 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.