Snowflake Snowpipe Continuous Loading

Many businesses cannot wait for a nightly batch. Orders, clicks, and sensor readings arrive all day, and dashboards need fresh numbers within minutes. Snowpipe loads files into tables automatically as soon as they land in a stage. No warehouse needs to run, and no one needs to press a button.

The Idea in Simple Terms

A batch load works like a bus that leaves once a night. Every passenger waits for the departure. Snowpipe works like a taxi stand. A taxi leaves the moment a passenger arrives. The files are the passengers, and the table is the destination.

BATCH LOAD VERSUS SNOWPIPE
==========================

BATCH (COPY INTO on a schedule)
  Files collect all day ......... 11:00 PM: load everything at once

SNOWPIPE (continuous)
  File 1 arrives -> loaded within about a minute
  File 2 arrives -> loaded within about a minute
  File 3 arrives -> loaded within about a minute

How Auto-Ingest Works

AUTO-INGEST FLOW
================

 +------------+    +--------------+    +------------------+
 | App writes |    | Cloud bucket |    | Event message    |
 | file to    |--->| (S3, Azure,  |--->| sent to Snowflake|
 | bucket     |    |  GCS)        |    | queue            |
 +------------+    +--------------+    +--------+---------+
                                                |
                                                v
 +------------+    +--------------+    +-----------------+
 | TABLE      |<---| COPY INTO    |<---| PIPE picks up   |
 | gets rows  |    | runs         |    | the file name   |
 +------------+    +--------------+    +-----------------+

The cloud storage service sends a notification each time a file arrives. Snowflake receives the message and runs the pipe's COPY statement for that file. Snowflake supplies the compute through a serverless pool and bills only for the work done.

Creating a Pipe

-- Target table
CREATE TABLE raw_orders (
  order_id   NUMBER,
  customer   STRING,
  amount     NUMBER(10,2),
  order_ts   TIMESTAMP_NTZ
);

-- External stage pointing to the bucket
CREATE STAGE orders_stage
  URL = 's3://my-company-data/orders/'
  STORAGE_INTEGRATION = s3_int
  FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1);

-- The pipe
CREATE PIPE orders_pipe
  AUTO_INGEST = TRUE
  AS
  COPY INTO raw_orders
  FROM @orders_stage;

-- Find the notification channel to configure in the cloud account
SHOW PIPES LIKE 'ORDERS_PIPE';

The SHOW PIPES output includes a notification channel value. Copy it into the bucket's event notification settings so file arrival messages reach Snowflake. Amazon S3 uses an SQS queue, Azure uses Event Grid, and Google Cloud uses Pub/Sub.

Monitoring and Troubleshooting

-- Current health of the pipe
SELECT SYSTEM$PIPE_STATUS('orders_pipe');

-- Load results from the last day
SELECT file_name, status, row_count, first_error_message
FROM TABLE(INFORMATION_SCHEMA.COPY_HISTORY(
  TABLE_NAME => 'RAW_ORDERS',
  START_TIME => DATEADD('hour', -24, CURRENT_TIMESTAMP())));

-- Load files that arrived before the pipe existed
ALTER PIPE orders_pipe REFRESH;

-- Pause and resume
ALTER PIPE orders_pipe SET PIPE_EXECUTION_PAUSED = TRUE;
ALTER PIPE orders_pipe SET PIPE_EXECUTION_PAUSED = FALSE;

File Size Matters

Snowpipe charges a small overhead per file. Thousands of tiny files raise costs and slow loading. Aim for compressed files between 100 MB and 250 MB. Applications that produce small files benefit from buffering for a minute before writing.

Snowpipe Streaming

Snowpipe loads files. Snowpipe Streaming skips files and writes rows directly into tables through a client library, which reduces delay to seconds. Event platforms such as Kafka use it through the Snowflake connector. Choose file-based Snowpipe for steady file drops and Snowpipe Streaming for row-by-row events.

CHOOSING A LOADING METHOD
=========================

Nightly files, large volume ........ COPY INTO with a warehouse
Files arriving all day ............. Snowpipe auto-ingest
Row events, second-level delay ..... Snowpipe Streaming

A Practical Scenario

An online store writes a small order file to cloud storage every minute. The marketing team wants a live view of sales during a flash sale. An engineer creates a stage, a target table, and a pipe with auto-ingest. The cloud bucket sends a message for each new file, and Snowflake loads each file within moments. The sales dashboard refreshes against fresh rows throughout the event, with no manual loading and no warehouse running for the loads.

Frequently Asked Questions

Does Snowpipe load a file twice?

A pipe tracks file names and load history for fourteen days, so the same file does not load again during that period.

How do I handle bad files?

Check COPY_HISTORY for the error message, fix the source file or table definition, and refresh the pipe to retry.

Cost Behavior in Plain Terms

Snowpipe charges for the compute it uses plus a small fee for each file it processes. A pipe that receives ten large files costs far less than a pipe that receives ten thousand tiny files. Teams watch the pipe usage history to confirm costs match expectations. Good file sizing, clean file formats, and a narrow COPY statement keep each load quick and cheap.

Ordering and Duplicates

Snowpipe loads files roughly in arrival order, though no strict guarantee applies. Design tables so row order does not matter, and carry a timestamp or sequence number inside each record. Downstream queries sort by that value and ignore arrival order.

Key Points

  • Snowpipe loads files automatically soon after they land in a stage.
  • Cloud event notifications trigger each load in auto-ingest mode.
  • Serverless compute handles the work, with no warehouse to manage.
  • SYSTEM$PIPE_STATUS and COPY_HISTORY reveal pipe health and load results.
  • Compressed files of 100 MB to 250 MB give the best cost and speed.
  • Snowpipe Streaming handles row-level, low-latency ingestion.
Previous lesson
Back to course
Next lesson