What you will be able to do
- Pick the right stage type (user, table, named internal, named external) for a set of files
- Explain what bulk loading with COPY INTO <table> requires and what it can transform during the load
- Compare bulk loading and Snowpipe on compute, transactions, load history and cost
- Say when Snowpipe Streaming or the Kafka connector fits better than file-based loading
- Anticipate the impact of warehouse use, file sizing, ON_ERROR, VALIDATION_MODE and load metadata on a data load
Key concept
Stage — A stage is the cloud storage location that holds data files before Snowflake loads them. Every file-based load, whether bulk COPY or Snowpipe, reads from a stage. The stage can be internal (storage inside your Snowflake account) or external (a bucket or container your organization manages).
1.Where files wait: internal and external stages
File-based loading always has two steps. First the files land in a stage, then a COPY statement moves their rows into a table. An external stage points at storage your organization owns: Amazon S3, Google Cloud Storage or Microsoft Azure. You can load from any of these no matter which cloud hosts your Snowflake account. Loading from a different region or cloud can add data transfer charges. One limit is easy to miss: you cannot load from archival storage classes such as S3 Glacier Flexible Retrieval, Glacier Deep Archive or Azure Archive Storage, because those files have to be restored before anyone can read them.
Internal stages live inside Snowflake, and you upload local files to them with the PUT command. There are three kinds. They differ in who manages the files and how many tables the files can feed.
| Stage type | Files managed by | Loaded 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 | Not a separate database object; no grantable privileges; requires table OWNERSHIP |
| Named internal stage | One or more users | One or more tables | Database object in a schema; access controlled by privileges; created with CREATE STAGE |
| Named external stage | Your cloud storage account | One or more tables | Stores the URL, access settings and file format options; created with CREATE STAGE |
The table stage needs special care. It is tied to its table, it has no privileges of its own, and only the table owner can stage, list, query or remove files in it. If several teams need to share staged files under controlled access, use a named stage. Because it is a database object, privileges control who can create, change, use or drop it.
Checkpoint 1 of 6· Match them up
Match each stage type to its defining constraint
Tap a term, then the definition that fits it.
User and table stages are built in and fixed. Named stages are schema objects created with CREATE STAGE. A named external stage holds the URL and credentials for storage you manage.
“designed to store files that are staged and managed by one or more users but only loaded into a single table”Source: docs.snowflake.com
Sources1
2.Bulk loading with COPY INTO <table>
Bulk loading takes a batch of files that are already in cloud storage, or that you have PUT into an internal stage, and runs a COPY command to load them. The most important point is compute. Bulk loads run on a virtual warehouse that you name in the COPY statement, and sizing that warehouse is your job. You pay for the time the warehouse is running.
COPY handles delimited files (comma by default, but any valid delimiter works), the semi-structured formats JSON, Avro, ORC, Parquet and XML, and unstructured formats. Snowflake doesn’t support loading data from tar (tape archive) files.
COPY can do light transformation while it loads. It can reorder columns, omit columns, cast values, and truncate text strings that are longer than the target column. That is often enough to skip a separate staging step for simple reshaping.
Checkpoint 2 of 6· Check yourself
A team runs a nightly COPY INTO job from an external stage. Which statement about its compute is correct?
Bulk loading runs on the virtual warehouse named in the COPY statement, and the user is responsible for sizing it. Serverless compute and per-GB billing describe Snowpipe.
“Bulk loading relies on user-provided virtual warehouses, which are specified in the COPY statement.”Source: docs.snowflake.com
3.Snowpipe: continuous micro-batches on serverless compute
Snowpipe is for small volumes of data (micro-batches) that need to become queryable soon after they arrive. You don't schedule COPY runs yourself. Instead you create a pipe, which is a named Snowflake object that wraps a COPY statement. The pipe's COPY supports the same transformation options as a bulk load. Snowpipe learns about new files in one of two ways. With auto-ingest, cloud storage event notifications go to a queue that Snowpipe polls. With the REST API, your application calls an endpoint with the pipe name and a list of filenames. Snowflake recommends turning on cloud event filtering to cut cost, event noise and latency.
| Aspect | Bulk data load (COPY) | Snowpipe |
|---|---|---|
| Authentication | Security options supported by the client session | REST endpoints require key pair authentication with JWT |
| Load history | Target table metadata for 64 days; returned as COPY output | Pipe metadata for 14 days; requested via REST endpoint, SQL table function, or ACCOUNT_USAGE view |
| Transactions | Always a single transaction | Combined or split into one or more transactions by row count and size |
| Compute | User-specified warehouse | Snowflake-supplied compute resources |
These differences matter in practice. Each pipe keeps its own load metadata so it doesn't reload files, and that metadata goes by file path and name. A file that is modified later but keeps the same name will not be loaded again. Each pipe has a single queue, but several processes pull from it, so Snowpipe does not guarantee that files load in the order they were staged. If a load must follow file order, it cannot rely on Snowpipe for that. Snowflake also recommends loading any given set of files with either bulk loading or Snowpipe, never both, so you don't get duplicate rows.
Cost works differently too. Snowpipe ingestion is billed at a fixed credit amount per GB loaded, so there is no warehouse to manage. Text files (CSV, JSON, XML) are billed on their uncompressed size, so you need to know their compression ratio to estimate cost. Binary files (Parquet, Avro, ORC) are billed on their observed size, regardless of compression.
Checkpoint 3 of 6· Check yourself
A pipeline delivers 10 GB of gzip-compressed CSV each day through Snowpipe. What volume determines the Snowpipe charge?
Snowpipe charges a fixed credit amount per GB. For text formats such as CSV, that GB figure is the uncompressed size. The per-1,000-files charge belonged to the earlier cost model.
“For text files — such as CSV, JSON, XML — you are charged based on their uncompressed size.”Source: docs.snowflake.com
Checkpoint 4 of 6· Match them up
Match each loading method or interface to the behavior it describes
Tap a term, then the definition that fits it.
Bulk COPY records load history in the target table's metadata for 64 days. Snowpipe records history in the pipe's metadata for 14 days, and its REST endpoints require key pair authentication with JWT.
“Stored in the metadata of the pipe for 14 days.”Source: docs.snowflake.com
4.Snowpipe Streaming and the Kafka connector
Bulk COPY and Snowpipe both start from files. Snowpipe Streaming does not. Its API writes rows straight into Snowflake tables, with no staged files at all. Without the stage-then-load step, latency is lower, and loading costs less at any data volume. That makes it a good fit for near real-time data streams.
The Snowflake Connector for Kafka reads from one or more Apache Kafka topics and loads the data into Snowflake tables. The connector can also use Snowpipe Streaming, which gives existing Kafka pipelines an easy way to get lower latency and lower cost. Snowpipe can also feed bigger pipelines: it continuously loads micro-batches into staging tables, and automated tasks then transform the data using the change data capture information in streams.
| Option | Unit of ingestion | Compute | Latency profile |
|---|---|---|---|
| Bulk loading (COPY) | Batches of staged files | User-provided virtual warehouse | Runs when you execute COPY |
| Snowpipe | Micro-batches of staged files | Snowflake-provided, serverless | Within minutes after files are staged and submitted |
| Snowpipe Streaming | Rows, no staged files | Snowflake-managed API path | Lower latency; near real-time streams |
Checkpoint 5 of 6· Check yourself
An application emits individual events and needs them queryable with the lowest load latency. It does not write files. Which option fits?
Snowpipe Streaming writes rows directly to tables with no staged files. All the other options start from files sitting in a stage.
“The Snowpipe Streaming API writes rows of data directly to Snowflake tables without the requirement of staging files.”Source: docs.snowflake.com
Sources1
5.Loading features and their impacts
Each loading feature has a side effect worth planning for before you load a data set.
Compute and query performance: loading large data sets can slow queries, so Snowflake recommends separate warehouses for loading and for querying. A smaller warehouse is generally enough unless you bulk load hundreds or thousands of files at once. A larger warehouse costs more credits and may not run any faster.
File size: parallel load operations cannot outnumber the data files, so aim for files of roughly 100-250 MB compressed. Aggregate small files and split large ones. For Snowpipe, staging files more often than once per minute adds queue-management overhead to the cost and cannot guarantee lower latency.
Error handling: the ON_ERROR copy option defaults to ABORT_STATEMENT for bulk loading and SKIP_FILE for Snowpipe. SKIP_FILE buffers the whole file, so it is slower than CONTINUE or ABORT_STATEMENT, and skipping a large file over a few bad rows wastes credits. To find problems before loading, run COPY in VALIDATION_MODE, which returns the errors it finds in the staged file. A pipe's COPY statement does not support VALIDATION_MODE.
Load metadata: COPY remembers which files it loaded for 64 days, and that is how it avoids duplicates. If a file's load status can no longer be determined, COPY skips it by default. LOAD_UNCERTAIN_FILES loads such files while still using the metadata that exists. FORCE ignores load metadata and can duplicate data.
Stage housekeeping: removing loaded files with the PURGE copy option or the REMOVE command prevents accidental reloads and improves performance, because COPY has fewer files to scan.
Checkpoint 6 of 6· Check yourself
A staged file is older than 64 days, and the table's load metadata for it has expired. What does COPY INTO <table> do with the file by default?
Once the load status is unknown, COPY skips the file by default to avoid duplicates. LOAD_UNCERTAIN_FILES or FORCE is needed to load it.
“to prevent accidental reload, the command skips the file by default.”Source: docs.snowflake.com
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Snowpipe and a scheduled COPY can safely load the same files, because both check load metadata.Why is that wrong?
Bulk loads keep their load metadata on the table and Snowpipe keeps its metadata on the pipe. Snowflake recommends loading a given set of files with only one of the two methods so you don't duplicate data.
Covered in Snowpipe: continuous micro-batches on serverless compute
2.If you overwrite a staged file with corrected data under the same name, Snowpipe will load the new version.Why is that wrong?
Snowpipe tracks files by path and name. It will not reload a file with the same name, even if the file changed and has a new eTag.
Covered in Snowpipe: continuous micro-batches on serverless compute
3.Setting the FORCE copy option is a safe way to load files that COPY skipped, because load metadata still prevents duplicates.Why is that wrong?
FORCE ignores load metadata, so it reloads files and can duplicate data. LOAD_UNCERTAIN_FILES is the option that still consults the metadata that is available.
Covered in Loading features and their impacts
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“You cannot access data held in archival cloud storage classes that requires restoration before it can be retrieved.”
↩︎ Where files wait: internal and external stages“To stage files to a table stage, list the files, query them on the stage, or drop them, you must be the table owner”
↩︎ Where files wait: internal and external stages“Upload files to any of the internal stage types from your local file system using the PUT command.”
↩︎ Where files wait: internal and external stages“Users are required to size the warehouse appropriately to accommodate expected loads.”
↩︎ Bulk loading with COPY INTO <table>“This architecture results in lower load latencies with corresponding lower costs for loading any volume of data”
↩︎ Snowpipe Streaming and the Kafka connector“enables users to connect to an Apache Kafka server, read data from one or more topics, and load that data into Snowflake tables”
↩︎ Snowpipe Streaming and the Kafka connector“Snowflake refers to the location of data files in cloud storage as a stage.”
↩︎ Key concept“designed to store files that are staged and managed by one or more users but only loaded into a single table”
↩︎ Checkpoint“There is no requirement for your data files to have the same number and ordering of columns as your target table.”
↩︎ Prediction“Bulk loading relies on user-provided virtual warehouses, which are specified in the COPY statement.”
↩︎ Checkpoint“The Snowpipe Streaming API writes rows of data directly to Snowflake tables without the requirement of staging files.”
↩︎ Checkpoint - 2.
“Snowflake doesn’t support loading data from tar (tape archive) files.”
↩︎ Bulk loading with COPY INTO <table> - 3.
“A pipe is a named, first-class Snowflake object that contains a COPY statement used by Snowpipe.”
↩︎ Snowpipe: continuous micro-batches on serverless compute“Automated data loads leverage event notifications for cloud storage to inform Snowpipe of the arrival of new data files to load.”
↩︎ Snowpipe: continuous micro-batches on serverless compute“there is no guarantee that files are loaded in the same order they are staged.”
↩︎ Snowpipe: continuous micro-batches on serverless compute“we recommend loading data from a specific set of files using either bulk data loading or Snowpipe but not both.”
↩︎ Exam trap 1“prevents loading files with the same name even if they were later modified (i.e. have a different eTag).”
↩︎ Exam trap 2“Stored in the metadata of the pipe for 14 days.”
↩︎ Checkpoint - 4.
“Snowpipe ingestion is billed based on a fixed credit amount per GB.”
↩︎ Snowpipe: continuous micro-batches on serverless compute“For text files — such as CSV, JSON, XML — you are charged based on their uncompressed size.”
↩︎ Checkpoint - 5.
“Loading large data sets can affect query performance.”
↩︎ Loading features and their impacts - 6.
“aiming to produce data files roughly 100-250 MB (or larger) in size compressed”
↩︎ Loading features and their impacts - 7.
“SKIP_FILE is slower than either CONTINUE or ABORT_STATEMENT.”
↩︎ Loading features and their impacts - 8.
“To validate data in an uploaded file, execute COPY INTO <table> in validation mode using the VALIDATION_MODE parameter.”
↩︎ Loading features and their impacts - 9.
“to prevent accidental reload, the command skips the file by default.”
↩︎ Loading features and their impacts“Note that this option reloads files, potentially duplicating data in a table.”
↩︎ Exam trap 3