Data Loading, Unloading, and Connectivity · Topic 4 of 7

Snowpipe and Snowpipe Streaming

Compare bulk COPY, Snowpipe auto ingest and Snowpipe Streaming: event notifications, pipes, serverless billing, load metadata and when to pick each one.

  • 5 min
  • Includes a diagram
In a nutshell
  • Snowpipe = files, event driven or REST driven, serverless, about a minute of latency.
  • Snowpipe Streaming = rows from SDK, REST API or the Kafka connector, serverless, seconds of latency, no files.
  • A pipe wraps a single COPY INTO statement; AUTO_INGEST = TRUE uses cloud event notifications.

What it is

Automated ingestion loads data continuously without anyone running COPY by hand. Snowflake offers two serverless services for it.

Snowpipe loads files soon after they land in a stage, usually within about a minute. Snowpipe Streaming writes rows straight into tables from an application or from Kafka, with no files and no stage, for latency measured in seconds.

New filecloud bucketevent notificationPipeCOPY INTO definitionserverless loadTableApplicationor Kafkarows over SDKSnowpipe StreamingchannelscommitTable

How it works

Bulk COPY vs Snowpipe vs Snowpipe Streaming

Three load paths into a table: bulk COPY loads staged files on your warehouse when you or a task run it; Snowpipe loads staged files serverless about a minute after a cloud event or REST call; Snowpipe Streaming appends rows from SDK, REST or Kafka clients through channels in seconds.

Bulk COPYSnowpipeSnowpipe Streaming
Unit of dataFiles in a stageFiles in a stageRows from a client
TriggerYou or a task run COPYCloud event notification or REST API callThe client calls the SDK or REST API
ComputeYour virtual warehouseServerless, managed by SnowflakeServerless, managed by Snowflake
Typical latencyBatch scheduleAbout a minute after the file arrives, not guaranteedSeconds
Load metadata64 days, per table14 days, per pipeOffset tokens per channel
Default ON_ERRORABORT_STATEMENTSKIP_FILENot applicable

Snowpipe: the pipe object

A pipe is a named object that wraps one COPY INTO statement.

CREATE PIPE sales_pipe
  AUTO_INGEST = TRUE
AS
  COPY INTO sales
  FROM @s3_stage/sales/
  FILE_FORMAT = (TYPE = PARQUET)
  MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;

Snowpipe learns about new files in one of two ways.

  • Auto ingest (AUTO_INGEST = TRUE): cloud event notifications announce new files. On Amazon S3 they arrive through Amazon SQS, SNS or EventBridge; on Google Cloud Storage through Pub/Sub with a notification integration; on Azure through Event Grid with a notification integration. For S3, SHOW PIPES lists the notification_channel SQS queue ARN that you add to the bucket's event notification.
  • REST API: the client calls the insertFiles endpoint with a list of staged files, authenticated with key pair authentication. insertReport and loadHistoryScan return load status. This is the classic way to use Snowpipe with internal stages; auto ingest for internal stages is a preview feature on AWS only.
File landsS3 bucketS3 eventSQS queueSnowflake managednotifiesPipeserverlessCOPYTableClient appinsertFiles callREST endpointqueues filesPipeserverlessCOPYTable

Snowpipe behavior to know

  • No warehouse. Snowpipe uses Snowflake managed compute, billed by Snowflake per GB loaded. You never size a warehouse for it.
  • Deduplication. Each pipe keeps load metadata for 14 days and skips files it already loaded.
  • Order is not guaranteed. Snowpipe generally loads older files first, but files can load out of order.
  • Errors. The default ON_ERROR is SKIP_FILE, and ABORT_STATEMENT is not supported in a pipe.
  • Backfill. ALTER PIPE ... REFRESH queues files staged in the last 7 days that the pipe has not loaded, useful after creating a pipe over existing files.
  • Status. SYSTEM$PIPE_STATUS('sales_pipe') shows whether the pipe is running and how many files are pending. Load history is in the COPY_HISTORY table function and view; credit use is in the PIPE_USAGE_HISTORY view.
  • Pause. ALTER PIPE ... SET PIPE_EXECUTION_PAUSED = TRUE pauses it.
Watch out

You cannot change the COPY statement of an existing pipe with ALTER PIPE. Recreate the pipe, and remember that CREATE OR REPLACE PIPE starts with empty load history, so files could load again. Pause the pipe and plan the switch.

File sizing still matters. Aim for 100 to 250 MB compressed, and if data arrives slowly, create a new file about once per minute rather than many tiny files.

Snowpipe Streaming

Snowpipe Streaming skips files entirely. A client SDK or the REST API opens channels to a table and appends rows. Snowflake buffers and commits them, and rows become queryable within seconds.

ConceptMeaning
ChannelA logical, ordered connection from a client to one target table
Offset tokenA marker the client stores with each batch, so after a restart it knows the last committed row and resumes without gaps or duplicates
Pipe objectIn the high-performance architecture, rows flow through a PIPE that can reorder, cast and transform columns before commit
SDKsJava, Python and Node.js SDKs, plus a REST API for lightweight clients

The Snowflake Connector for Kafka can ingest with Snowpipe Streaming, which gives lower latency than its file based Snowpipe mode. Snowpipe Streaming can also write to Snowflake managed Apache Iceberg tables.

Kafka topicpartitionsKafka connectorChannelsone per partitionappend rowsSnowpipe StreamingcommitTableseconds later
Exam tip

Look at the source. "Files land in S3 and must load automatically" is Snowpipe with auto ingest. "IoT devices or Kafka send rows and need data in seconds" is Snowpipe Streaming. "Load last night's export at 2 a.m." is bulk COPY, often in a task.

Common mistakes

Assigning a warehouse to Snowpipe. Pipes have no warehouse parameter; compute is serverless.

Expecting files in order. Snowpipe does not guarantee load order. If order matters, sort with a timestamp column after loading.

Expecting ALTER PIPE REFRESH to load old files. It only looks at files staged within the last 7 days. Use a bulk COPY for older history.

Staging files for streaming. Snowpipe Streaming needs no stage and no files; rows go straight from the client to the table.

What to remember for the exam

  • Snowpipe = files, event driven or REST driven, serverless, about a minute of latency.
  • Snowpipe Streaming = rows from SDK, REST API or the Kafka connector, serverless, seconds of latency, no files.
  • A pipe wraps a single COPY INTO statement; AUTO_INGEST = TRUE uses cloud event notifications.
  • Pipe load metadata lasts 14 days (bulk COPY: 64 days). Default ON_ERROR is SKIP_FILE.
  • ALTER PIPE ... REFRESH loads files staged in the last 7 days. SYSTEM$PIPE_STATUS shows pipe health.
  • Channels and offset tokens let streaming clients resume without duplicates.

Check yourself

  • What tells an auto ingest pipe on S3 that a new file has arrived?

    An S3 event notification delivered to the SQS queue that Snowflake provides for the pipe.

  • What is the default ON_ERROR setting for Snowpipe?

    SKIP_FILE, which skips any file with errors and loads the others.

  • Which service fits sensors that send rows that must be queryable within seconds?

    Snowpipe Streaming, which appends rows through channels without staging files.

Topic 4 of 7 in Data Loading, Unloading, and Connectivity

Test yourself on Data Loading, Unloading, and Connectivity

The full Snowflake SnowPro Core COF-C03 question bank has 845 questions, with timed practice exams and an explanation for every answer.

This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.