Snowpipe Streaming: Getting Data into Snowflake in Seconds, Not Minutes

Three Ways to Load Snowflake

It helps to be precise about Snowflake's loading options, because they're often lumped together:

The key difference with Snowpipe Streaming is that there are no intermediate files. Your client (or the Kafka connector) sends rows over a channel directly into a table.

Core Concepts

Channels. A client opens one or more channels to write into a table. A channel is an ordered stream of rows from a single writer, often mapped one-to-one to a Kafka partition or a shard of your source.

Offset tokens. Each batch of rows can carry an offset token, which you set (for example, the Kafka offset or a CDC log sequence number). After a restart, you ask the channel for its latest committed offset token and resume from there. That's how you get exactly-once delivery semantics: rows are committed with their offset, so you never have to guess what was written.

Serverless compute. Ingestion doesn't need a running virtual warehouse. That's a big part of why it's cheaper than keeping a warehouse awake to load micro-batches.

The High-Performance Architecture

Snowflake's newer high-performance architecture for Snowpipe Streaming became generally available on AWS in September 2025, and on Azure and GCP shortly after. It's worth knowing about because it changes the design:

• A server-side PIPE object is now the entry point for streaming into a table. Transformations, column mapping and schema validation are defined in the pipe using familiar COPY-style syntax, rather than in client code.
• Much higher throughput per table, with ingest-to-query latency typically in single-digit seconds.
• Throughput-based pricing: a flat rate per uncompressed GB ingested, instead of the classic model's mix of client-connection and compute charges. That makes costs far more predictable.

If you're starting fresh, start on the high-performance architecture. If you run the classic SDK, Snowflake publishes a migration guide; the main changes are the new SDK and moving transformations into the PIPE object.

Pattern 1: Kafka to Snowflake

If you already run Kafka (MSK, Confluent or self-managed), the Snowflake Kafka connector with Snowpipe Streaming is the lowest-effort path:

name=orders-to-snowflake
connector.class=com.snowflake.kafka.connector.SnowflakeSinkConnector
topics=orders.cdc
snowflake.ingestion.method=SNOWPIPE_STREAMING
snowflake.url.name=<account>.snowflakecomputing.com:443
snowflake.user.name=KAFKA_CONNECTOR
snowflake.private.key=<key>
snowflake.role.name=INGEST_ROLE
snowflake.database.name=RAW
snowflake.schema.name=KAFKA
snowflake.enable.schematization=true

The connector manages channels per partition and uses Kafka offsets as offset tokens, so restarts and rebalances don't produce duplicates or gaps. Check the connector documentation for the settings that select the high-performance architecture in your connector version.

Pattern 2: Direct SDK Writes from a Service

For event streams that don't pass through Kafka (application events, IoT, clickstream collected by your own service), write directly with the SDK. The shape of the code is:

open client (key-pair auth)
open channel(s) for the target table/pipe
for each batch:
    append rows with offset token = last source position in the batch
periodically: check committed offset token for monitoring
on restart: read committed offset token → resume source from there

Keep the number of channels modest and long-lived. Opening and closing channels per request is an anti-pattern.

Pattern 3: Postgres CDC in Near Real Time

For operational databases, combine a CDC tool (Debezium or DMS into Kafka or Kinesis) with Snowpipe Streaming. Land changes append-only into a raw table, then build current-state tables with Dynamic Tables or dbt incremental models:

CREATE OR REPLACE DYNAMIC TABLE analytics.orders_current
  TARGET_LAG = '1 minute'
  WAREHOUSE = transform_xs
AS
SELECT *
FROM raw.orders_cdc
QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY source_lsn DESC) = 1
  AND op <> 'd';

Note that the transformation still runs on a warehouse. Seconds-latency ingestion paired with a one-minute target lag is usually the sweet spot; pushing target lag lower costs warehouse credits.

When It's Worth It

Use Snowpipe Streaming when:

• The business genuinely acts on data within minutes: fraud signals, operational dashboards for fulfilment, stock levels, live campaign monitoring.
• You already have events in Kafka or Kinesis and want them in Snowflake without a file stage.
• Your micro-batch Snowpipe setup is producing lots of small files and costs are creeping up.

Stick with batch or classic Snowpipe when:

• Reporting is daily or hourly and nobody looks at the dashboard more often.
• Your sources are files from partners or exports.
• You don't have the operational maturity for streaming yet: monitoring lag, handling schema changes and replaying from offsets.

Operational Checklist

• Monitor lag between source position and committed offset token per channel, and alert on it.
• Plan for schema evolution. Decide whether new columns are auto-added (schematization) or rejected and alerted.
• Land raw, model downstream. Keep the raw table append-only so you can rebuild.
• Tag and monitor costs for ingestion and for the transformations downstream, separately.

The Takeaway

Snowpipe Streaming is the right tool when latency truly matters, and the high-performance architecture's per-GB pricing makes it much easier to budget for. But most "real-time" requirements are really "every 15 minutes is fine". Establish the real freshness requirement first, then pick the simplest loading method that meets it.