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
In a nutshell
  • 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).

Sourcefiles or rowsland inStageinternal or externalread withFile formatCSV, JSON, Parquetmoved byCOPY or pipewarehouse or serverlesswritesTable

How it works

Choosing the loading method

SituationBest fitCompute
Large one time or scheduled batch of filesBulk COPY INTO <table>Your virtual warehouse
Files arrive continuously in cloud storageSnowpipe with auto ingestServerless
Application pushes files and calls an APISnowpipe REST APIServerless
Rows from devices, apps or Kafka, needed in secondsSnowpipe StreamingServerless
A person has a small local fileSnowsight Load Data wizardWarehouse
Data you do not want to copy at allExternal table or Iceberg table over cloud storageQuery 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.

Exam tip

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

FeatureWhat it doesImpact to remember
Load metadataCOPY records each file it loaded for 64 days per tableA file with the same name and unchanged content is skipped on re-run
FORCE = TRUELoads files even if already loadedCreates duplicate rows
PURGE = TRUEDeletes staged files after a successful loadFiles are gone; plan your retention elsewhere
ON_ERRORDecides what bad rows doDefault is ABORT_STATEMENT for COPY and SKIP_FILE for Snowpipe
SIZE_LIMITStops starting new files after a byte totalThe file in progress still finishes
PATTERNRegular expression filter on file namesNarrow the path as well for speed
MATCH_BY_COLUMN_NAMEMaps file columns to table columns by nameNeeds matching names; case handling is an option
COPY transformationSELECT over the stage while loadingReorder, cast, omit columns, add metadata columns
VALIDATION_MODEChecks files without loadingNot 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.

COPY INTOstatementchecksLoad metadatafile seen in 64 days?new files onlyParse and castfile format rulesgood rowsTableParse and castbad rowsON_ERRORabort, skip or continue

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 VARIANT column, or is mapped to typed columns with MATCH_BY_COLUMN_NAME or a COPY transformation such as SELECT $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_STATEMENT for COPY, SKIP_FILE for Snowpipe.
  • Semi-structured data goes to VARIANT or 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.