Data Engineering · Topic 3 of 16

Data Ingestion - Bulk COPY, Snowpipe and Snowpipe Streaming

Compare bulk file upload with COPY, Snowpipe auto ingest and REST, and Snowpipe Streaming: latency, compute, billing, load history and when each wins.

  • 5 min
In a nutshell
  • Bulk COPY: your warehouse, files, transactional, ON_ERROR default ABORT_STATEMENT.
  • Snowpipe: serverless, files, auto ingest or REST API, 14 day load history, SKIP_FILE default.
  • Snowpipe Streaming: rows, channels, offset tokens, seconds of latency, no staged files.

What it is

Snowflake has three native ingestion engines, and the exam expects you to match each to a scenario. Bulk loading uses COPY INTO <table> on a virtual warehouse. Snowpipe loads files continuously on serverless compute. Snowpipe Streaming writes rows straight into tables without files.

The differences that decide questions are the unit of input (file or row), the latency, who provides compute, and how duplicates are prevented.

Files in batchstageCOPY INTOWarehouseyou size itwritesTableFiles arrivingstagepipeServerlessSnowpipewritesTableRowsapp or KafkachannelServerlessSnowpipe StreamingwritesTable

How it works

Bulk file upload

For local files, the classic route is PUT (from SnowSQL, Snowflake CLI or a driver) to an internal stage, then COPY INTO. For cloud storage, create an external stage that uses a storage integration, then COPY. The Snowsight Load Data wizard does the same for small manual loads.

COPY runs on the warehouse you choose and loads files in parallel, but it cannot use more parallel threads than there are files. Aim for files of roughly 100 to 250 MB compressed, and split very large files first. COPY is transactional: with the default ON_ERROR = ABORT_STATEMENT, one bad row means nothing from that statement is committed.

Bulk loading suits scheduled batches, history loads and loads that need a transformation in the statement (reorder columns, cast, add metadata such as METADATA$FILENAME).

Snowpipe

A pipe is a named object that holds a COPY statement. Snowpipe has two ways to learn about new files.

TriggerHow it worksTypical use
Auto ingestCloud event notifications (S3 to SQS, Azure Event Grid, Google Pub/Sub) tell the pipeFiles land in an external stage
REST APIYour application calls insertFiles with a list of staged file namesInternal stages, or custom control

Compute is serverless and managed by Snowflake, so there is no warehouse setting on a pipe. Snowpipe is billed as serverless usage at a fixed number of credits per GB loaded, not as warehouse time. Many tiny files add processing overhead without lowering latency, so batch events into files of about 100 to 250 MB where you can, but do not wait so long that latency suffers; files about once a minute are a good balance.

Snowpipe remembers which files it loaded in the pipe load history for 14 days, which is how it avoids loading a file twice. Its default ON_ERROR is SKIP_FILE, not ABORT_STATEMENT, so a bad file is skipped and reported while other files continue.

CREATE PIPE raw.orders_pipe
  AUTO_INGEST = TRUE
AS
  COPY INTO raw.orders
  FROM @raw.orders_stage
  FILE_FORMAT = (TYPE = JSON);

After creating a pipe, SHOW PIPES returns a notification_channel value that you wire to the bucket's event notification. Use SYSTEM$PIPE_STATUS to see whether the pipe is running and how many files are pending.

Exam tip

To load files that were already in the stage before the pipe existed, run ALTER PIPE ... REFRESH. It looks at files staged in the last 7 days. Older files need a bulk COPY.

Snowpipe Streaming

Snowpipe Streaming ingests rows directly, with no staged files. A client uses the Snowpipe Streaming SDK (or the Kafka connector configured for it) to open a channel to a table and send rows. Rows are queryable with seconds of latency. Snowflake offers two architectures: the original SDK that writes directly to tables, and a newer high performance architecture that ingests through a pipe object, with billing based on the volume of data ingested.

Each channel has an offset token that your client sets. After a failure, the client asks Snowflake for the last committed offset token and resumes from there, which gives exactly once behavior without duplicates. Rows in one channel are ordered; separate channels have no ordering relation.

AspectBulk COPYSnowpipeSnowpipe Streaming
InputFilesFilesRows
LatencyPer runAbout a minuteSeconds
ComputeYour warehouseServerlessServerless
Duplicate protectionLoad metadata, 64 daysLoad history, 14 daysOffset tokens
Default error behaviorABORT_STATEMENTSKIP_FILEHandled by the client or ingest settings
TransformationIn the COPY statementIn the pipe COPY statementLight, at ingest

Data ingested through streaming often lands in a raw table and is then refined by streams and tasks or dynamic tables.

Common mistakes

Putting a warehouse on a pipe. Pipes use serverless compute. There is no warehouse parameter.

Sending many tiny files to Snowpipe. Processing overhead grows, and staging smaller files more often does not guarantee lower latency. Aggregate small events before writing files.

Assuming Snowpipe load history lasts as long as COPY's. Pipe history is 14 days; COPY metadata is 64 days. Re-sending an older file to a pipe can load it again.

Recreating a pipe and expecting history to stay. CREATE OR REPLACE PIPE resets the load history, so already loaded files can load again.

What to remember for the exam

  • Bulk COPY: your warehouse, files, transactional, ON_ERROR default ABORT_STATEMENT.
  • Snowpipe: serverless, files, auto ingest or REST API, 14 day load history, SKIP_FILE default.
  • Snowpipe Streaming: rows, channels, offset tokens, seconds of latency, no staged files.
  • Use ALTER PIPE ... REFRESH for files staged before the pipe (last 7 days).
  • Aim for files of about 100 to 250 MB compressed across all file based paths.

Check yourself

  • An application writes events and needs them queryable in seconds, with no duplicates after a restart. What fits?

    Snowpipe Streaming, using a channel and its offset token to resume from the last committed row.

  • Where does a Snowpipe load run?

    On serverless compute managed by Snowflake, so a pipe has no warehouse setting.

  • Which loading method uses the default ON_ERROR = SKIP_FILE?

    Snowpipe. Bulk COPY defaults to ABORT_STATEMENT.

Topic 3 of 16 in Data Engineering

Test yourself on Data Engineering

The full Snowflake ARA-C01 question bank has 687 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.