Skip to content
Tutorials Center
My learning

Topics

Lesson 14 of 38 ยท Loading and Unloading Data

Continuous Loading with Snowpipe

Learn how Snowpipe loads files automatically as they land in a stage, with auto-ingest from cloud events, the REST API, serverless billing, and Snowpipe Streaming.

COPY INTO is a batch tool: you run it when you decide. Many systems instead produce a steady trickle of files, such as logs, events or exports every few minutes, and users want the data in tables soon after it arrives. Snowpipe does that. It watches a stage, loads new files automatically and uses serverless compute, so you do not need to run a warehouse.

What a pipe is

A pipe wraps a COPY INTO statement. Once created, Snowflake runs that statement for each new file that appears in the stage.

SQL
-- Snowflake SQL (read-only)
CREATE PIPE shop.raw.orders_pipe
  AUTO_INGEST = TRUE
AS
COPY INTO shop.raw.orders
FROM @shop.raw.s3_orders
FILE_FORMAT = (FORMAT_NAME = 'shop.raw.csv_fmt');

AUTO_INGEST = TRUE means cloud storage will tell Snowflake when a file lands. You configure an event notification (S3 event to an SQS queue, Azure Event Grid, or Google Pub/Sub) pointing at the pipe's notification channel, which you find with SHOW PIPES (column notification_channel).

flowchart LR
  A["App writes file<br/>to bucket"] --> B["Cloud event<br/>notification"]
  B --> C["Snowpipe queue"]
  C --> D["Serverless load<br/>COPY INTO"]
  D --> E["Table"]

New file in, event out, rows in the table within about a minute.

Two ways to trigger a pipe

Method How it works Use when
Auto-ingest Cloud storage events notify Snowflake Files land in an external stage (S3, Azure, GCS)
REST API Your code calls insertFiles with file names Internal stages, or when you control the trigger

Latency is typically around a minute from file arrival to rows queryable; it is near real time rather than instant.

Snowpipe versus COPY INTO

COPY INTO Snowpipe
Compute Your warehouse Serverless (Snowflake-managed)
Trigger You run it New files arrive
Billing Warehouse credits Per-file overhead plus compute used
Best for Scheduled bulk loads Continuous micro-batches
Transformations Fuller options Basic, same as COPY
Latency When you schedule it About a minute

Billing nuance: Snowpipe charges a small overhead per file in addition to compute, so thousands of tiny files cost far more than a few right-sized ones. Aim for files of roughly 100 to 250 MB compressed when you can, and at most about one file per minute from each producer; if your source writes thousands of 2 KB files a minute, batch them first or consider Snowpipe Streaming.

Operating a pipe

SQL
-- Snowflake SQL (read-only)
SHOW PIPES;
SELECT SYSTEM$PIPE_STATUS('shop.raw.orders_pipe');    -- JSON: executionState, pendingFileCount

ALTER PIPE shop.raw.orders_pipe SET PIPE_EXECUTION_PAUSED = TRUE;    -- pause
ALTER PIPE shop.raw.orders_pipe SET PIPE_EXECUTION_PAUSED = FALSE;   -- resume

-- Load files that were already in the stage before the pipe existed
ALTER PIPE shop.raw.orders_pipe REFRESH;

-- What did the pipe load recently?
SELECT file_name, status, row_count, first_error_message
FROM TABLE(information_schema.copy_history(
  TABLE_NAME => 'ORDERS', START_TIME => DATEADD(hours, -1, CURRENT_TIMESTAMP())));

Pipes keep their own load history (14 days), separate from COPY INTO history, so a file loaded by a pipe may still be reloaded by a manual COPY if you are not careful. Choose one path per table.

Errors in a pipe do not stop it. Files that fail are recorded, and you handle them with COPY_HISTORY and VALIDATE_PIPE_LOAD. Set ON_ERROR deliberately because the default for pipes is SKIP_FILE, meaning a bad file is quietly skipped.

Snowpipe Streaming

For data that is not in files at all (events from Kafka, IoT devices, application code), Snowpipe Streaming writes rows straight into tables through an API with latency of seconds, skipping the file stage. The Snowflake Connector for Kafka can use it. It suits high-volume event streams, but it is more complex to set up and is usually done through a client library, so treat it as an advanced option once the file-based pipe is not fast enough.

Dynamic tables as an alternative downstream

After raw data arrives, you often transform it. Rather than scheduling a job, you can declare a dynamic table and let Snowflake keep it fresh; see the later chapter on pipelines. A common combo is Snowpipe to load raw data and a dynamic table or a stream-plus-task to maintain clean tables.

A model you can run

A pipe is "for each new file, load it and remember it". Here is a runnable simulation where files arrive over time and a "pipe" loop loads only files with an arrival time later than the last one processed (a bookmark):

SQL Stand-in: a pipe with a bookmark

Press Run (or Ctrl + Enter) to see the result here.

And a quick comparison of overhead per file: many tiny files cost more than few large ones when each file carries a fixed charge. The numbers are invented to show the shape:

SQL Stand-in: per-file overhead

Press Run (or Ctrl + Enter) to see the result here.

Both plans carry the same data, but the first pays the file overhead a thousand times more.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What compute does Snowpipe use?

    Snowpipe is serverless, so you do not need a running warehouse. You are billed for the compute used plus a per-file overhead.
  2. 2. Which setting makes a pipe load files when cloud storage sends an event?

    AUTO_INGEST relies on event notifications from the bucket.
  3. 3. Why are thousands of 2 KB files a poor fit for Snowpipe?

    File overhead dominates with tiny files. Batch them into larger files first.
  4. 4. What is Snowpipe Streaming for?

    It writes rows through an API directly into tables, skipping the file stage.

Summary

  • A pipe wraps a COPY INTO and runs it automatically for new files, using serverless compute.
  • Auto-ingest uses cloud events; the REST API suits internal stages.
  • Latency is around a minute; right-size files to control per-file overhead.
  • Monitor with SYSTEM$PIPE_STATUS, COPY_HISTORY and VALIDATE_PIPE_LOAD.
  • Snowpipe Streaming handles row-level, low-latency ingestion without files.

Next: Unloading Data with COPY INTO a Location