Data Movement · Topic 1 of 17
Loading Data - Methods, Considerations and Load Features
Choose how to load data into Snowflake: PUT and COPY, Snowpipe, file sizing, warehouse parallelism, load metadata and the impact of COPY options.
- 6 min
- Pick by trigger and latency: bulk COPY for batches, Snowpipe for arriving files, Snowpipe Streaming for rows, wizard for small manual loads.
- Best file size is about 100 to 250 MB compressed; parallelism is capped by the file count.
- COPY load metadata lasts 64 days per table; Snowpipe keeps 14 days per pipe.
What it is
Loading data means moving data from files, applications or streams into Snowflake tables. The exam gives you a data set and a requirement, such as "files arrive hourly", "rows must be queryable in seconds" or "a 2 TB history load", and asks which path fits.
A Data Engineer picks the method first, then tunes file size, compute and error handling. The same four choices come up again and again: where the data waits (a stage), how it is read (a file format), what moves it (COPY, a pipe or a client), and what pays for it (a warehouse or serverless compute).
How it works
Choosing the loading method
| Situation | Best fit | Compute |
|---|---|---|
| Large one time or scheduled batch of files | Bulk COPY INTO <table> | Your virtual warehouse |
| Files arrive continuously in cloud storage | Snowpipe with auto ingest | Serverless |
| Application pushes files and calls an API | Snowpipe REST API | Serverless |
| Rows from devices, apps or Kafka, needed in seconds | Snowpipe Streaming | Serverless |
| A person has a small local file | Snowsight Load Data wizard | Warehouse |
| Data you do not want to copy at all | External table or Iceberg table over cloud storage | Query time |
For local files the classic path is PUT (SnowSQL or Snowflake CLI) to an internal stage, then COPY INTO. PUT compresses uncompressed files with gzip by default (AUTO_COMPRESS = TRUE), and the uploaded files are encrypted automatically.
Considerations for data loading
File size and count. Aim for roughly 100 to 250 MB compressed per file. Many tiny files add per file overhead, and one huge file cannot be spread across threads, so split it before loading.
Parallelism. A COPY statement loads files in parallel, but it cannot use more parallel operations than there are files. A larger warehouse helps only when there are many files; for a handful of files an X-Small warehouse is usually enough.
Organize the path. Stage files under paths such as /sales/2026/11/14/. Then COPY can target a narrow path or a PATTERN regular expression instead of scanning the whole stage.
Batch or continuous. Bulk COPY batches work and uses a warehouse you pay for while it runs. Snowpipe bills a fixed credit rate per GB loaded and Snowpipe Streaming per uncompressed GB ingested, so they suit a trickle of data without keeping a warehouse running.
When a question says "load 10,000 files as fast as possible", the answer is more files in parallel on a larger warehouse. When it says "one 50 GB file is slow", the answer is to split the file, not to resize the warehouse.
Data loading features and their impact
| Feature | What it does | Impact to remember |
|---|---|---|
| Load metadata | COPY records each file it loaded for 64 days per table | A file with the same name and unchanged content is skipped on re-run |
FORCE = TRUE | Loads files even if already loaded | Creates duplicate rows |
PURGE = TRUE | Deletes staged files after a successful load | Files are gone; plan your retention elsewhere |
ON_ERROR | Decides what bad rows do | Default is ABORT_STATEMENT for COPY and SKIP_FILE for Snowpipe |
SIZE_LIMIT | Stops starting new files after a byte total | The file in progress still finishes |
PATTERN | Regular expression filter on file names | Narrow the path as well for speed |
MATCH_BY_COLUMN_NAME | Maps file columns to table columns by name | Needs matching names; case handling is an option |
| COPY transformation | SELECT over the stage while loading | Reorder, cast, omit columns, add metadata columns |
VALIDATION_MODE | Checks files without loading | Not allowed together with a transformation |
COPY is transactional: with the default ABORT_STATEMENT, one bad row stops the statement and nothing from that statement is committed. Snowpipe combines or splits files into one or more transactions of its own, so a problem file does not roll back a whole batch.
Ingesting data of various formats
Snowflake loads delimited text (CSV, TSV), JSON, Avro, ORC, Parquet and XML. The format is described by a file format object or inline options on the stage or COPY statement.
- Structured, delimited data maps column by column into typed columns.
- Semi-structured data (JSON, Avro, ORC, Parquet, XML) lands in a
VARIANTcolumn, or is mapped to typed columns withMATCH_BY_COLUMN_NAMEor a COPY transformation such asSELECT $1:id::NUMBER, $1:name::STRING. - Unstructured data (images, PDFs, audio) is not loaded into table rows. It stays in a stage and is read through a directory table and file URLs.
COPY INTO orders (id, name, loaded_file)
FROM (SELECT $1:id::NUMBER, $1:name::STRING, METADATA$FILENAME
FROM @raw_stage/orders/)
FILE_FORMAT = (TYPE = JSON)
PATTERN = '.*[.]json'
ON_ERROR = SKIP_FILE;
The mechanics are always the same: read the staged file with the file format, apply the transformation if there is one, then write to the table. The later pages in this chapter take each piece in turn.
Common mistakes
Resizing the warehouse to speed up one file. Parallelism comes from the number of files, not the warehouse size.
Using FORCE to fix a failed load. FORCE reloads every matching file, including those that already loaded, and creates duplicates. Fix the cause and re-run; only the files that did not load will load again.
Assuming Snowpipe uses your warehouse. A pipe runs on serverless compute and has no warehouse setting.
What to remember for the exam
- Pick by trigger and latency: bulk COPY for batches, Snowpipe for arriving files, Snowpipe Streaming for rows, wizard for small manual loads.
- Best file size is about 100 to 250 MB compressed; parallelism is capped by the file count.
- COPY load metadata lasts 64 days per table; Snowpipe keeps 14 days per pipe.
- Default
ON_ERROR:ABORT_STATEMENTfor COPY,SKIP_FILEfor Snowpipe. - Semi-structured data goes to
VARIANTor is mapped by name or transformation; unstructured files stay in a stage.
Check yourself
A COPY statement loads 8 large files on an X-Small warehouse. Will a 4X-Large warehouse help?
Only a little. Parallelism is limited by the number of files, so 8 files cannot use all that extra capacity.
What happens if you re-run COPY over a stage where all files loaded 10 days ago?
Nothing loads, because load metadata marks the files as already loaded for 64 days. FORCE = TRUE would reload them and create duplicates.
Where does unstructured data such as PDFs live after ingestion?
In a stage, accessed through a directory table and file URLs, not as rows in a table.
Topic 1 of 17 in Data Movement
Test yourself on Data Movement
The full Snowflake DEA-C02 question bank has 709 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.