Snowpipe vs Snowpipe Streaming: which ingestion path to choose

Both names contain "Snowpipe" and both load data continuously, so they get confused in exams and in design reviews. The real distinction is what arrives: files or rows.

At a glance

	Snowpipe	Snowpipe Streaming	Scheduled COPY INTO
Input	Files in a stage (S3, GCS, Azure, internal)	Rows sent by a client	Files in a stage
Trigger	Cloud event notifications (AUTO_INGEST) or REST API	Your application or the Kafka connector	A task, an orchestrator, or you
Typical latency	Around a minute after the file lands	Seconds	Your schedule
Compute	Serverless (Snowflake-managed)	Serverless	Your virtual warehouse
Best for	Files already landing in object storage	Event streams, Kafka, app telemetry	Large periodic batches

Snowpipe (file-based)

A pipe wraps a COPY INTO statement. When a new file lands in the stage, a cloud notification tells Snowflake and the pipe loads it with Snowflake-managed compute.

CREATE PIPE raw.events_pipe
  AUTO_INGEST = TRUE
AS
COPY INTO raw.events
FROM @raw.events_stage
FILE_FORMAT = (TYPE = 'JSON');

Worth remembering:

• File size matters. Snowflake recommends roughly 100–250 MB (compressed) per file. Thousands of tiny files add per-file overhead and cost.
• Load history is kept per pipe for 14 days; that's how Snowpipe avoids loading the same file twice. (Bulk COPY INTO keeps load metadata for 64 days.)
• Edited files aren't reloaded. Re-staging a modified file under the same name looks like a file that was already loaded.
• Monitoring: SYSTEM$PIPE_STATUS('raw.events_pipe') shows the pipe's state; COPY_HISTORY shows per-file results and errors.

Snowpipe Streaming (row-based)

Snowpipe Streaming skips files entirely. A client — your own code using the Snowflake Ingest SDK, or the Snowflake Connector for Kafka configured with snowflake.ingestion.method=SNOWPIPE_STREAMING — opens a channel to a table and writes rows directly. Rows are queryable within seconds.

Why it matters:

• Latency: no waiting for a file to be written, closed, uploaded and noticed.
• No small-file problem: high-frequency events don't turn into millions of tiny staged files.
• Resumable ingestion: channels track an offset token, so a client such as the Kafka connector can resume from its last committed position after a restart.

Billing works differently from file-based Snowpipe, so check the current pricing documentation when you estimate cost rather than assuming the two are interchangeable.

Choosing between them

Use Snowpipe when files already land in object storage — vendor drops, application exports, CDC tools that write files — and about a minute of latency is fine.

Use Snowpipe Streaming when data originates as a stream — Kafka topics, clickstream, IoT telemetry — and users need it in seconds.

Use scheduled COPY INTO when data arrives in big batches a few times a day. A task running COPY INTO on a right-sized warehouse is simple, predictable and easy to reason about.

After the data lands

Ingestion is only half the pipeline. The Snowflake-native options for the transform step:

• Streams + tasks: a stream captures inserts, updates and deletes on the landing table; a task — optionally gated with WHEN SYSTEM$STREAM_HAS_DATA('...') — processes them incrementally.
• Dynamic tables: declare the transformation as a query plus a TARGET_LAG, and Snowflake keeps the result fresh without hand-written orchestration.

A classic scenario: files arrive every few hours and must be ingested and transformed incrementally as quickly as possible. The usual best answer is Snowpipe for ingestion plus streams and tasks (or a dynamic table) for the transformation.

Common mistakes

1. Forcing file-based Snowpipe into "near real-time" by writing tiny files every second. Latency barely improves and cost goes up.
2. Forgetting that a newly created task is suspended until you run ALTER TASK ... RESUME.
3. Expecting Snowpipe to pick up an edited file that kept the same name.

Practice these trade-offs with 10 free SnowPro Advanced: Data Engineer questions.