Skip to main content

Snowpipe vs Snowpipe Streaming

Historically, "Snowpipe" referred to one specific service: file-based micro-batching. Recently, Snowflake introduced the Snowpipe Streaming API, fundamentally changing how real-time data ingestion works.

It is critical for a Data Architect to understand the difference between these two paradigms and when to deploy each.

Topics Covered​

  • File-Based vs Row-Based Ingestion
  • Latency and Use Case Comparison
  • Cost Model Differences
  • SDK Support Overview

File-Based (Classic) vs Row-Based (Streaming)​

Classic Snowpipe (File-Based)​

  1. An upstream application (e.g., a web server in Mumbai) writes events to a JSON file.
  2. The file is uploaded to an AWS S3 bucket.
  3. An SQS event notification tells Snowflake the file is ready.
  4. Snowpipe borrows serverless compute to execute a COPY INTO command, loading the file into a table. Because it requires a staging bucket and file manipulation, there is inherent latency.

Snowpipe Streaming API (Row-Based)​

  1. An upstream application (e.g., a fraud detection engine in Pune) uses the Snowflake Java or Python SDK.
  2. It sends rows of data over an encrypted HTTPS connection directly into Snowflake's active table memory.
  3. Snowflake bypasses the S3 staging bucket entirely. Because there are no intermediate files, latency is nearly instantaneous.

Comparison Matrix: When to Choose Which​

FeatureClassic SnowpipeSnowpipe Streaming API
Ingestion UnitFiles (CSV, JSON, Parquet)Individual Rows (via SDK)
ArchitectureRequires External Stage (S3, GCS, Azure)No External Stage required
Typical Latency1 to 2 MinutesLess than 1 second
Common Use CasesLog file drops, Daily vendor extractsReal-time IoT telemetry, Fraud detection CDC
Kafka IntegrationSnowflake Kafka Connector (Legacy Mode)Snowflake Kafka Connector (Streaming Mode)

Hands-On Demonstration: Simulating the Architectures​

Let's look at how the target data behaves in Snowflake, contrasting a file-based batch load against a simulated continuous streaming feed.

1. Database Setup​

Copy and paste the following code into your Snowflake worksheet.

USE ROLE SYSADMIN;

CREATE DATABASE IF NOT EXISTS BEEMS_INGEST_DB;
USE SCHEMA BEEMS_INGEST_DB.PUBLIC;

CREATE OR REPLACE TABLE CLASSIC_SNOWPIPE_TARGET ( EVENT_TIMESTAMP TIMESTAMP, LOCATION STRING, METRIC NUMBER );
CREATE OR REPLACE TABLE STREAMING_API_TARGET ( EVENT_TIMESTAMP TIMESTAMP, LOCATION STRING, METRIC NUMBER );

2. Simulating Classic Snowpipe Latency​

If we simulate Snowpipe, we insert data in "chunks" (simulating a file load). The BI dashboard will not see this data until the entire chunk is processed, meaning the dashboard is always at least a minute behind reality.

-- Simulated Snowpipe File Load (Runs every 1-2 minutes)
INSERT INTO CLASSIC_SNOWPIPE_TARGET VALUES
(CURRENT_TIMESTAMP(), 'Delhi', 105.2),
(CURRENT_TIMESTAMP(), 'Delhi', 106.1),
(CURRENT_TIMESTAMP(), 'Delhi', 104.9);

SELECT MAX(EVENT_TIMESTAMP) FROM CLASSIC_SNOWPIPE_TARGET;

3. Simulating Snowpipe Streaming Latency​

If we use the Streaming API, the custom Java/Python application pushes rows the absolute millisecond they occur. The BI dashboard updates in real-time.

-- Simulated Streaming API (Rows arrive continuously via SDK)
INSERT INTO STREAMING_API_TARGET VALUES (CURRENT_TIMESTAMP(), 'Chennai', 98.4);
-- 100ms later...
INSERT INTO STREAMING_API_TARGET VALUES (CURRENT_TIMESTAMP(), 'Chennai', 99.1);
-- 100ms later...
INSERT INTO STREAMING_API_TARGET VALUES (CURRENT_TIMESTAMP(), 'Chennai', 98.7);

SELECT MAX(EVENT_TIMESTAMP) FROM STREAMING_API_TARGET;

(To see who is executing these simulated queries, run SELECT CURRENT_USER();)

SDK Support for Snowpipe Streaming​

Because Snowpipe Streaming bypasses file storage, you cannot simply configure an AWS S3 Event to trigger it. Your upstream application must actively push the data.

Snowflake provides dedicated SDKs for this:

  • Java SDK: The most mature. Used heavily when integrating with Apache Kafka (the Snowflake Kafka Connector uses this SDK internally), Apache Flink, or custom Java microservices.
  • Python SDK: Ideal for data science teams, custom AWS Lambda functions, or lightweight Python backend servers.

Cost Model Differences​

Both tools are Serverless, but they bill differently:

  • Classic Snowpipe: You pay for the compute time required to load the file + a 0.06 credit charge per 1000 files queued.
  • Snowpipe Streaming: You pay a per-second charge based on how long the client SDK maintains an active connection to Snowflake, plus a small charge for the background process that eventually compacts the rows into micro-partitions.

Cost Rule of Thumb: If your data naturally arrives as large files from a vendor, use Classic Snowpipe. If your data arrives as millions of tiny, high-frequency events (like website clicks or IoT sensors), use Snowpipe Streaming.

Summary​

  • Classic Snowpipe requires cloud storage buckets and loads files (Minute latency).
  • Snowpipe Streaming uses SDKs to insert rows directly into tables (Sub-second latency).
  • Choose Streaming for IoT, CDC, and Kafka integrations.
  • Choose Classic for vendor file drops and traditional batch ingestion.