Skip to content
Tutorials Center
My learning

Topics

Lesson 12 of 38 ยท Loading and Unloading Data

COPY INTO: Loading a Table

Load staged files into a Snowflake table with COPY INTO. Learn load history, FILES and PATTERN, column matching, transformations during load, PURGE and FORCE.

COPY INTO <table> is Snowflake's workhorse for bulk loading. It reads files from a stage, parses them with a file format and writes rows into a table, in parallel across the warehouse. Once you understand its options, loading a million-row CSV or a thousand Parquet files is routine.

The basic statement

SQL
-- Snowflake SQL (read-only)
CREATE OR REPLACE TABLE shop.raw.orders (
  id NUMBER, customer VARCHAR, total NUMBER(10,2), ordered_on DATE
);

COPY INTO shop.raw.orders
FROM @shop.raw.landing/orders/
FILE_FORMAT = (FORMAT_NAME = 'shop.raw.csv_fmt')
PATTERN = '.*orders_2025_.*[.]csv[.]gz';

Snowflake lists matching files, loads each one and returns one row per file with its status, rows_parsed, rows_loaded, errors_seen and the first error if any.

The statement needs a running warehouse: loading is compute work. A common choice is a dedicated loading warehouse such as load_wh.

Load metadata: files are loaded only once

Snowflake remembers which files a table has already loaded, for 64 days. If you run the same COPY INTO again, files already loaded are skipped, so you can safely rerun a load after adding new files without duplicating data. The result says LOAD_SKIPPED for them.

To intentionally reload (for example after truncating), add FORCE = TRUE:

SQL
-- Snowflake SQL (read-only)
TRUNCATE TABLE shop.raw.orders;
COPY INTO shop.raw.orders FROM @shop.raw.landing/orders/
  FILE_FORMAT = (FORMAT_NAME = 'shop.raw.csv_fmt')
  FORCE = TRUE;   -- ignore load history

Use FORCE carefully: reloading a file you did not truncate duplicates its rows.

The idea of "remember which files I have loaded" is simple enough to model. The stand-in keeps a load-history table and inserts only files not yet recorded:

SQL Stand-in: load history skips files already loaded

Press Run (or Ctrl + Enter) to see the result here.

Each file was loaded exactly once, even though the load ran twice.

Choosing files

Three ways to select files:

  • the path in the stage (@stage/orders/2025/),
  • FILES = ('a.csv.gz', 'b.csv.gz') for an explicit list (up to 1000),
  • PATTERN = 'regex' to match names.

Matching columns

For CSV, columns are matched by position: field 1 goes to column 1. For JSON, Parquet, Avro and ORC you can match by name:

SQL
-- Snowflake SQL (read-only)
COPY INTO shop.raw.orders
FROM @shop.raw.landing/orders_parquet/
FILE_FORMAT = (TYPE = PARQUET)
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;

Column order in the file no longer matters. Missing columns become NULL, extra file columns are ignored.

Transforming while loading

You can load from a SELECT over the stage with positional references. This lets you pick columns, change order, cast types and add values:

SQL
-- Snowflake SQL (read-only)
COPY INTO shop.raw.orders (id, customer, total, ordered_on)
FROM (
  SELECT $1::NUMBER, UPPER($3), $2::NUMBER(10,2), TO_DATE($4, 'DD/MM/YYYY')
  FROM @shop.raw.landing/orders/
)
FILE_FORMAT = (FORMAT_NAME = 'shop.raw.csv_fmt');

You can also record where each row came from, which is gold when debugging:

SQL
-- Snowflake SQL (read-only)
COPY INTO shop.raw.orders_audit (id, total, source_file, source_row)
FROM (SELECT $1, $2, METADATA$FILENAME, METADATA$FILE_ROW_NUMBER
      FROM @shop.raw.landing/orders/)
FILE_FORMAT = (FORMAT_NAME = 'shop.raw.csv_fmt');

Transformations during COPY are limited (no joins, aggregates, WHERE or LIMIT in the select; simple casts and functions are fine). For heavier logic, load raw and transform afterward with SQL, an approach called ELT.

Try that "load then transform" pattern: raw text goes into a staging table, then a typed table is built with cleaning (trim, upper, cast):

SQL Stand-in: load raw text, transform in SQL (ELT)

Press Run (or Ctrl + Enter) to see the result here.

Useful options

Option Effect
ON_ERROR What to do when a row is bad: ABORT_STATEMENT (default for COPY), CONTINUE, SKIP_FILE, SKIP_FILE_<n> (next lesson)
PURGE = TRUE Delete staged files after a successful load
FORCE = TRUE Load again even if the file is in load history
TRUNCATECOLUMNS = TRUE Cut oversize strings instead of failing
SIZE_LIMIT Stop after about this many bytes
RETURN_FAILED_ONLY Show only failed files in the result
ENFORCE_LENGTH Fail if text exceeds a column length (default behavior)

Performance tips

  • Load many files in parallel: each warehouse node processes files separately, so 100 files of 100 MB load much faster than one 10 GB file. Aim for 100 to 250 MB compressed files.
  • A bigger warehouse helps only when there are enough files to keep it busy. One file loads on one thread regardless of size, so an XL warehouse on a single file is wasted.
  • Keep file format and column types consistent; a date parse failure in a million-row file wastes time.
  • For other sources (CSV from the browser), the Snowsight "Load data into table" wizard builds a COPY for you.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. You run the same COPY INTO twice, with no new files. What happens the second time?

    Snowflake remembers loaded files for 64 days and skips them unless FORCE = TRUE.
  2. 2. How are CSV columns matched to table columns by default?

    CSV fields map by position. MATCH_BY_COLUMN_NAME is for semi-structured and columnar formats.
  3. 3. Which is NOT allowed in the SELECT of a transforming COPY?

    COPY transformations support simple expressions only; joins, aggregates and filters are not supported.
  4. 4. Why split a huge dataset into many files of 100-250 MB?

    Parallelism comes from files, so many medium files use the warehouse well.

Summary

  • COPY INTO table FROM @stage bulk-loads files using a file format and a warehouse.
  • Load history prevents duplicate loads for 64 days; FORCE = TRUE overrides it.
  • Select files by path, FILES or PATTERN; map columns by position or by name.
  • Simple transformations and metadata columns can be applied during load; do heavy work after loading.
  • Many mid-sized files give the best parallelism.

Next: Validating Loads and Handling Errors