Snowpipe Streaming API
The Snowpipe Streaming API represents a paradigm shift in how data is ingested into Snowflake. While classic Snowpipe relies on cloud storage (S3, Azure Blob, GCS) and loads data via files, the Snowpipe Streaming API writes rows directly to Snowflake tables.
This eliminates the need to stage files, resulting in significantly lower latency and reduced architecture complexity.
Topics Covered
- Snowpipe Streaming vs File-Based Snowpipe
- Streaming API Architecture
- Channels, Offsets, and Exactly-Once Semantics
Snowpipe Streaming vs File-Based Snowpipe
| Feature | Classic Snowpipe | Snowpipe Streaming API |
|---|---|---|
| Ingestion Mechanism | Asynchronous file-based loading from external stages | Direct row-by-row API inserts into Snowflake |
| Latency | Usually 1-minute to a few minutes | Sub-second to a few seconds |
| Cloud Storage | Required (S3, GCS, Azure Blob) | Not Required (Direct API) |
| Cost Structure | Compute per file processed + Cloud Storage costs | Compute per second of active client ingestion |
Streaming API Architecture & Exactly-Once Semantics
The Streaming API operates using Channels. A channel represents a logical, ordered stream of rows into a target table.
- You can have multiple channels writing to the same table concurrently.
- Each channel tracks an Offset Token.
- If a client disconnects and reconnects, it asks Snowflake for the latest offset token committed. By resuming from that offset, it guarantees Exactly-Once delivery semantics.
Hands-On Demonstration: Setting up a Streaming Target
While the actual streaming ingestion happens via a Java/Python SDK application, we must prepare the destination in Snowflake.
1. Database & Schema Setup
Copy and paste the following code into your Snowflake worksheet.
USE ROLE SYSADMIN;
CREATE DATABASE IF NOT EXISTS BEEMS_STREAMING_DB;
USE DATABASE BEEMS_STREAMING_DB;
CREATE SCHEMA IF NOT EXISTS API_INGESTION;
USE SCHEMA API_INGESTION;
2. Creating the Target Table
Let's create a table to capture real-time IoT temperature readings.
CREATE OR REPLACE TABLE API_INGESTION.IOT_TEMPERATURE_READINGS (
DEVICE_ID STRING,
LOCATION STRING,
TEMPERATURE_CELSIUS NUMBER(5,2),
RECORDED_AT TIMESTAMP_NTZ
);
3. Simulating the Streaming Data (Java SDK Equivalent)
In a real scenario, your custom Java application would use the Snowpipe Streaming SDK to push rows into this table. For this lab, let's simulate the data that would arrive using Indian locations and device names.
-- This simulates what the Snowpipe Streaming API does behind the scenes, row-by-row.
INSERT INTO API_INGESTION.IOT_TEMPERATURE_READINGS VALUES
('DEV_001_MUM', 'Mumbai, Maharashtra', 31.5, CURRENT_TIMESTAMP()),
('DEV_002_DEL', 'New Delhi, Delhi', 38.2, CURRENT_TIMESTAMP()),
('DEV_003_BLR', 'Bangalore, Karnataka', 26.4, CURRENT_TIMESTAMP()),
('DEV_004_KOC', 'Kochi, Kerala', 29.8, CURRENT_TIMESTAMP()),
('DEV_005_CHD', 'Chandigarh, Punjab', 35.1, CURRENT_TIMESTAMP());
4. Querying and Monitoring the Stream
You can query the table immediately. The rows inserted by the Streaming API are available for query within seconds.
SELECT * FROM API_INGESTION.IOT_TEMPERATURE_READINGS
ORDER BY RECORDED_AT DESC;
To monitor the health and performance of the Streaming API, you use the Account Usage views.
(Note: Ensure you are using a role with sufficient privileges to view account usage data. To verify your current user, you can run SELECT CURRENT_USER();)
USE ROLE ACCOUNTADMIN;
-- Monitor Snowpipe Streaming Client History
SELECT
CLIENT_NAME,
TABLE_NAME,
CHANNEL_NAME,
START_TIME,
END_TIME,
OFFSET_TOKEN
FROM SNOWFLAKE.ACCOUNT_USAGE.SNOWPIPE_STREAMING_CLIENT_HISTORY
WHERE TABLE_NAME = 'IOT_TEMPERATURE_READINGS'
ORDER BY START_TIME DESC;
Latency and Performance Characteristics
- Insert Latency: Data is typically queryable within 1-2 seconds of the API call.
- Micro-Partition Optimization: Snowflake buffers the streamed rows in memory and periodically flushes them to cloud storage as micro-partitions. You don't need to manually trigger compaction; Snowflake handles it automatically via the automated clustering service.
Summary
- Snowpipe Streaming writes rows directly to tables, bypassing cloud storage buckets.
- It provides Sub-second latency.
- It uses Channels and Offset Tokens to ensure Exactly-Once semantics.