Course contents

SnowPro Core COF-C03 Study GuidePart 4: Data loading and storageSnowpipe

Snowpipe

Continuous Loading & External Data

Snowpipe

In shortSnowpipe loads each file shortly after it lands in a stage, by running a pipe's COPY statement on compute that Snowflake manages for you.

Ingestion path · Snowpipe
Source
Files in a stage
Trigger
A cloud event notification (auto-ingest) or a REST API call per batch of files
Processor
Snowflake serverless compute; no warehouse of yours
Destination
The table named in the pipe's COPY
Compute and billing
A flat amount of credits per GB ingested; no per-file charge

The problem

Files arrive all day. Scheduling a bulk COPY every five minutes wakes a warehouse for empty runs, pays its 60-second minimum each time, and still adds delay. Snowpipe removes the schedule: the arrival of a file is the trigger.

How it works

A pipe is a named schema object wrapping one COPY statement. Snowpipe learns about new files in one of two ways. With auto-ingest, the cloud storage service sends an event notification when a file is created (S3 events through SQS or SNS, Azure Event Grid, Google Pub/Sub), and the pipe receives it. Auto-ingest is the standard path for external stages; for internal stages it's a preview feature on AWS-hosted accounts only. With the REST API, an application calls the pipe's insertFiles endpoint with a list of file names. That works for external stages, named internal stages and table stages, but not user stages. Either way, Snowpipe queues the files and loads them with serverless compute.

Snowpipe auto-ingest sequence. A file lands in the bucket, the cloud sends an event notification, the pipe queues the file and serverless compute runs the pipe's COPY statement, committing rows and recording the file in the pipe's 14-day load history.S3 bucketEvent queuePipe ORDERS_PIPETable ORDERS3 · file queued6 · file recorded (14 days)1 · object created → event2 · pipe receives notification4 · serverless COPY reads the file5 · rows committed

S3 bucket

Event queue

Pipe ORDERS_PIPE

Table ORDERS

  1. 1 · object created → eventS3 bucketEvent queue
  2. 2 · pipe receives notificationEvent queuePipe ORDERS_PIPE
  3. 3 · file queuedPipe ORDERS_PIPE
  4. 4 · serverless COPY reads the fileS3 bucketPipe ORDERS_PIPE
  5. 5 · rows committedPipe ORDERS_PIPETable ORDERS
  6. 6 · file recorded (14 days)Pipe ORDERS_PIPE
Figure 4.10 Snowpipe auto-ingest, read top to bottom. The event carries a notification, not data; the file's bytes are read once, by Snowpipe's serverless COPY. Loads usually complete within a minute or two of arrival, but Snowflake doesn't guarantee a latency or a load order.

Example: Thomas automates the orders feed

CREATE PIPE sales_db.raw.orders_pipe
  AUTO_INGEST = TRUE
  AS
  COPY INTO sales_db.core.orders
  FROM @sales_db.raw.s3_landing/orders/
  FILE_FORMAT = (FORMAT_NAME = 'sales_db.raw.csv_std');

SHOW PIPES;   -- notification_channel: the queue ARN to configure in the S3 event settings
SELECT SYSTEM$PIPE_STATUS('sales_db.raw.orders_pipe');
ALTER PIPE sales_db.raw.orders_pipe SET PIPE_EXECUTION_PAUSED = TRUE;    -- pause
ALTER PIPE sales_db.raw.orders_pipe SET PIPE_EXECUTION_PAUSED = FALSE;   -- resume
ALTER PIPE sales_db.raw.orders_pipe REFRESH;  -- queue files staged in the last 7 days that it missed

Expected resultSHOW PIPES gives the notification channel that Thomas pastes into the bucket's event configuration. SYSTEM$PIPE_STATUS returns a JSON object whose execution state is RUNNING and whose pending file count drops as files load. New files then appear in ORDERS without any warehouse running.

How Snowpipe differs from bulk COPY

TopicBulk COPYSnowpipe
Default ON_ERRORABORT_STATEMENTSKIP_FILE (ABORT_STATEMENT isn't allowed in a pipe)
Load historyTarget table's metadata, 64 daysPipe's metadata, 14 days (both visible in COPY_HISTORY). The two histories are separate: a bulk COPY doesn't skip files a pipe loaded. Use one method for each set of files
COPY optionsAllNot allowed: FILES, FORCE, PURGE, SIZE_LIMIT, LOAD_UNCERTAIN_FILES, RETURN_FAILED_ONLY, VALIDATION_MODE
Changing the COPYEdit the statementCREATE OR REPLACE PIPE, which starts with an empty load history, so an ALTER PIPE ... REFRESH afterward can load old files a second time. ALTER PIPE only pauses, refreshes and sets a few properties
Cost controlResource monitors and budgetsBudgets only; resource monitors don't see serverless credits

Billing detail: text files are measured uncompressed and binary formats such as Parquet at their stored size. This per-GB model replaced compute-time billing plus a per-file fee in 2025, so older material shows the old one. ALTER PIPE … REFRESH only reaches files staged in the last 7 days and is meant for troubleshooting, not as a schedule.

Exam takeaway
  • Snowpipe = a pipe's COPY run by serverless compute when files are announced.
  • Triggers: cloud event notifications (auto-ingest) or the REST API (insertFiles).
  • Billed per GB ingested; default ON_ERROR = SKIP_FILE; pipe load history = 14 days; no latency guarantee.