What you will be able to do
- Match a data source (files at rest, rows in motion, CDC feeds, API clients) to bulk COPY, Snowpipe or Snowpipe Streaming
- Pick the right stage type and explain who supplies and pays for the compute in each loading option
- Choose between Elastic Channels and Named Channels for streaming and CDC workloads
- Tell when to query files in place through an external table or an externally managed Iceberg table, and when to use a Snowflake-managed Iceberg table instead
Key concept
Files at rest vs rows in motion — The first decision in any Snowflake ingestion design is the shape of the arriving data. Files that land in cloud storage go through a stage and a COPY statement (bulk or Snowpipe). Rows emitted continuously by applications, devices or CDC sources go straight into tables through Snowpipe Streaming.
1.Data at rest: stages, file formats and bulk COPY
Every loading decision starts with two questions: how much data arrives, and how often. Snowflake's loading overview frames the choice in those terms: the best solution "may depend upon the volume of data to load and the frequency of loading." Data at rest means files that already exist, such as nightly exports from an OLTP/RDBMS system, partner drops or historical archives. That data enters Snowflake through a stage, which is Snowflake's name for a location of data files in cloud storage.
An external stage points at cloud storage your organisation manages. Amazon S3, Google Cloud Storage and Microsoft Azure are all supported no matter which cloud hosts your Snowflake account, although loading from a different region or cloud can add data transfer charges. Internal stages live inside your Snowflake account. For bulk file upload from a local machine, you use the PUT command to push files into an internal stage. Files bound for an external stage are uploaded with the cloud provider's own tools. Archival storage classes that need restoring first, such as S3 Glacier Deep Archive or Azure Archive Storage, can't be read at all.
| Stage type | Who manages the files | Loads into | Notes |
|---|---|---|---|
| User stage | A single user | Multiple tables | Allocated to each user; cannot be altered or dropped |
| Table stage | One or more users | A single table | Implicit, not a separate database object; requires table OWNERSHIP |
| Named internal stage | One or more users | One or more tables | Database object created with CREATE STAGE; access controlled by privileges |
COPY INTO <table> then reads the staged files. External sources and formats are described by FILE_FORMAT, whose TYPE can be CSV, JSON, AVRO, ORC, PARQUET or XML. Bulk loading runs on a virtual warehouse that you name in the COPY statement, so you are responsible for sizing it for the expected load. COPY can also do light transformation on the way in: reordering columns, omitting columns, casting, and truncating text that is too long for the target column. Your files don't need to have the same number or order of columns as the target table.
Checkpoint 1 of 5· Check yourself
Several engineers stage files that must be loaded into three different tables, and the security team wants to control who can use the stage through grants. Which stage fits?
Only a named stage is a database object whose use can be governed by access control privileges, and it can feed one or more tables. A table stage loads a single table and has no grantable privileges of its own.
“Because named stages are database objects, the ability to create, modify, use, or drop them can be controlled using security access control privileges.”Source: docs.snowflake.com
Sources1
2.Snowpipe: continuous micro-batches from staged files
When files arrive throughout the day rather than in one nightly batch, Snowpipe turns the same COPY logic into continuous ingestion. It is built to "load small volumes of data (i.e. micro-batches) and incrementally make them available for analysis", usually within minutes of a file being staged. A pipe is a named object that wraps one COPY statement, and that COPY supports the same transformation options as bulk loading.
Snowpipe can learn about new files in two ways. With auto-ingest, cloud storage event notifications tell Snowpipe that files have arrived, and Snowpipe polls those notifications from a queue. Snowflake recommends cloud event filtering to cut cost, noise and latency. With the REST endpoints, a client application or other API source calls a public endpoint with the pipe name and a list of file names. These calls require key pair authentication with JSON Web Token (JWT).
Snowpipe also uses a different operating model from bulk loading:
| Aspect | Bulk data load | Snowpipe |
|---|---|---|
| Compute | User-specified warehouse | Snowflake-supplied, serverless |
| Billing | Time each warehouse is active | Compute used while loading the files |
| Load history | Target table metadata, 64 days | Pipe metadata, 14 days |
| Transactions | Always a single transaction | Combined or split by number and size of rows |
| Load order | n/a | Generally older first, but not guaranteed |
Snowpipe is also the usual front end of a file-based CDC pipeline. A CDC tool replicating an OLTP database writes change files to a stage, and Snowpipe loads them into a staging table. Downstream work then runs "using automated tasks and the change data capture (CDC) information in streams." Don't point Snowpipe and bulk COPY at the same set of files. Each keeps its own load history, so mixing them risks loading the same files twice.
Checkpoint 2 of 5· Exam question
A manufacturer's IoT sensors emit thousands of small readings per second, and the operations team needs each reading queryable in Snowflake within about five seconds without writing the readings to files first. Which ingestion approach best satisfies this latency and volume profile?
Correct answer: A — Use Snowpipe Streaming so the sensor application writes rows directly into the target table without any intermediate staged files.
- A. Correct - Snowpipe Streaming ingests rows directly from an application into a table, bypassing file staging entirely, and is built for the low-latency, high-throughput profile this sensor workload describes.
- B. Incorrect - Snowpipe still requires files to be staged first and typically delivers data within minutes rather than seconds, which does not meet the near-real-time latency target.
- C. Incorrect - a per-minute scheduled COPY INTO job still depends on files being staged beforehand and adds scheduling latency on top, missing the five-second requirement.
- D. Incorrect - querying an external table over appended raw files adds file-listing and read overhead per query and does not give the sub-ten-second freshness a streaming write path provides.
Checkpoint 3 of 5· Check yourself
An auditor asks for load history on files ingested by a pipe 30 days ago. What should the architect expect?
Snowpipe load history lives in the pipe's metadata for 14 days and must be requested from Snowflake. The 64-day window belongs to bulk loads.
“Stored in the metadata of the pipe for 14 days.”Source: docs.snowflake.com
3.Snowpipe Streaming: rows in motion, CDC and API sources
Some data never starts out as files. Device telemetry, application events and CDC feeds produce rows continuously. For data in motion like this, staging files only to load them again adds latency and moving parts. Snowpipe Streaming "writes rows of data directly to Snowflake tables without the requirement of staging files," into standard tables or Snowflake-managed Iceberg tables. Snowflake quotes figures of up to 20 GB/s per table and ingest-to-queryable latency as low as 5 seconds, though real results depend on the workload. Billing is based on throughput: credits per uncompressed GB ingested.
This is an architecture change as well as a speed-up. The direct path "can remove staging files, intermediate object storage, message buses, and connector services" that a workload doesn't otherwise need. Teams already running Kafka can keep it: the Snowflake Connector for Kafka offers Snowpipe Streaming as "an easy upgrade path to take advantage of the lower latency and lower cost loads."
The choice that matters most is the channel type:
- Elastic Channels are the recommended starting point. Snowflake manages the channels and scales ingestion for you, with at-least-once delivery and no ordering guarantee. - Named Channels use offset tokens to give ordered, exactly-once ingestion within each channel. Use them for Kafka partitions or Change Data Capture from OLTP/RDBMS sources, where order matters.
API sources can use the Java (11+), Python (3.9+) or Node.js (20+) SDKs, which buffer and batch rows automatically. They can also send newline-delimited JSON to the REST API, which suits lightweight workloads, IoT devices and edge deployments. Snowflake recommends the SDK wherever one can run.
Checkpoint 4 of 5· Match them up
Match each Snowpipe Streaming option to the workload it fits
Tap a term, then the definition that fits it.
Elastic Channels trade ordering for simplicity. Named Channels use offset tokens to keep order, which CDC needs. The REST API and the Kafka connector are integration paths for specific environments.
“Use Named Channels when reading from a source that requires strict ordering semantics, such as Kafka partitions or Change Data Capture (CDC).”Source: docs.snowflake.com
4.Querying in place: external tables and Iceberg tables
Not all data has to be loaded. An external table lets you "query data without first loading it into Snowflake". You define it over a path in an external stage with a file format, and the files stay where they are. The example below is for S3-compatible storage. In that setup the metadata can't refresh automatically, so you register added or removed files with ALTER EXTERNAL TABLE … REFRESH.
CREATE EXTERNAL TABLE et
LOCATION=@my_s3compat_stage/path1/
AUTO_REFRESH = FALSE
REFRESH_ON_CREATE = TRUE
FILE_FORMAT = (TYPE = PARQUET);Apache Iceberg tables keep data in an open table format, and they come in two kinds. An externally managed (unmanaged) Iceberg table is registered in an outside catalog such as Snowflake Open Catalog. You reach it through a catalog integration and an external volume that points at the storage holding the table's data and metadata, and Snowflake gets read-only access. A Snowflake-managed Iceberg table is one Snowflake can write to. Snowpipe Streaming can stream into it (Iceberg v2 and v3), and it supports DML error logging via CREATE/ALTER ICEBERG TABLE, which the docs mark as "Snowflake-managed only". If Snowflake has to ingest into the Iceberg table, it must be Snowflake-managed.
Checkpoint 5 of 5· Check yourself
A data lake team keeps Iceberg tables in Snowflake Open Catalog and wants a Snowflake pipeline to stream new rows into them. What is the problem with an externally managed Iceberg table here?
Querying through a catalog integration gives read-only access. Snowpipe Streaming writes to Snowflake-managed Iceberg tables.
“The table represents the Iceberg table in Snowflake Open Catalog and provides read-only access.”Source: docs.snowflake.com
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Snowpipe load history is kept for 64 days, just like bulk COPY.Why is that wrong?
Bulk load history lives in the target table's metadata for 64 days. Snowpipe history lives in the pipe's metadata for only 14 days.
Covered in Snowpipe: continuous micro-batches from staged files
2.Elastic Channels are the recommended default, so they are the right choice for CDC streams too.Why is that wrong?
Elastic Channels give at-least-once delivery with no ordering guarantee. CDC needs Named Channels, which provide ordered, exactly-once ingestion through offset tokens.
Covered in Snowpipe Streaming: rows in motion, CDC and API sources
3.Any Iceberg table visible in Snowflake can be a target for loads and DML.Why is that wrong?
An externally managed Iceberg table queried through a catalog integration is read-only. Snowflake writes only to Snowflake-managed Iceberg tables.
Covered in Querying in place: external tables and Iceberg tables
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“The best solution may depend upon the volume of data to load and the frequency of loading.”
↩︎ Data at rest: stages, file formats and bulk COPY“Upload files to any of the internal stage types from your local file system using the PUT command.”
↩︎ Data at rest: stages, file formats and bulk COPY“Bulk loading relies on user-provided virtual warehouses, which are specified in the COPY statement.”
↩︎ Data at rest: stages, file formats and bulk COPY“There is no requirement for your data files to have the same number and ordering of columns as your target table.”
↩︎ Data at rest: stages, file formats and bulk COPY“using automated tasks and the change data capture (CDC) information in streams”
↩︎ Snowpipe: continuous micro-batches from staged files“The Snowpipe Streaming API writes rows of data directly to Snowflake tables without the requirement of staging files.”
↩︎ Snowpipe Streaming: rows in motion, CDC and API sources“Because named stages are database objects, the ability to create, modify, use, or drop them can be controlled using security access control privileges.”
↩︎ Checkpoint - 2.
“Your client application calls a public REST endpoint with the name of a pipe object and a list of data filenames.”
↩︎ Snowpipe: continuous micro-batches from staged files“we recommend loading data from a specific set of files using either bulk data loading or Snowpipe but not both.”
↩︎ Snowpipe: continuous micro-batches from staged files“Stored in the metadata of the pipe for 14 days.”
↩︎ Exam trap 1“prevents loading files with the same name even if they were later modified (i.e. have a different eTag).”
↩︎ Prediction“Stored in the metadata of the pipe for 14 days.”
↩︎ Checkpoint - 3.https://docs.snowflake.com/en/user-guide/snowpipe-streaming/data-load-snowpipe-streaming-overviewOfficial docs
“This direct path can remove staging files, intermediate object storage, message buses, and connector services”
↩︎ Snowpipe Streaming: rows in motion, CDC and API sources“Where possible, use the Snowpipe Streaming SDK instead of the REST API to benefit from automatic batching and simpler integration.”
↩︎ Snowpipe Streaming: rows in motion, CDC and API sources“Stream data into Snowflake-managed Iceberg tables, including both Iceberg v2 and Iceberg v3 tables.”
↩︎ Querying in place: external tables and Iceberg tables“Use Snowpipe when your pipeline already produces files in cloud storage and batch-oriented, higher-latency loading is acceptable.”
↩︎ Key concept“Elastic Channels provide at-least-once delivery without an ordering guarantee.”
↩︎ Exam trap 2“Use Named Channels when reading from a source that requires strict ordering semantics, such as Kafka partitions or Change Data Capture (CDC).”
↩︎ Checkpoint - 4.
“You can use external tables with S3-compatible storage to query data without first loading it into Snowflake.”
↩︎ Querying in place: external tables and Iceberg tables
Also cited
“The table represents the Iceberg table in Snowflake Open Catalog and provides read-only access.”
↩︎ Exam trap 3“The table represents the Iceberg table in Snowflake Open Catalog and provides read-only access.”
↩︎ Checkpoint