Snowflake COPY INTO Practical Design Guide: Load, Unload, and FILE FORMAT

COPY INTO is Snowflake's core command for data loading and unloading. A single command handles bulk loads from a stage into a table and exports from a table back out to a stage, and its rich options — FILE FORMAT, ON_ERROR, and VALIDATION_MODE — give you the flexibility to build production-grade data pipelines.

On the SnowPro Core exam, COPY INTO falls under Domain 4 (Data Loading & Unloading, 10-15% of the exam). The Data Engineer exam goes even deeper into its options. This article organizes COPY INTO end-to-end from a practical design perspective and highlights the points that come up most often on the exams.

COPY INTO (Load) Syntax

The basic syntax for loading data from a stage into a table:

COPY INTO <table_name>
  FROM @<stage_name>/<path>/
  FILE_FORMAT = (FORMAT_NAME = '<file_format_name>'
                 | TYPE = CSV | JSON | PARQUET | AVRO | ORC | XML)
  PATTERN = '.*\.csv\.gz'
  ON_ERROR = ABORT_STATEMENT | CONTINUE | SKIP_FILE | SKIP_FILE_<N>
  PURGE = TRUE | FALSE
  FORCE = TRUE | FALSE;

Key Parameters

How COPY INTO Prevents Duplicate Loads

By default, COPY INTO keeps 64 days of load-history metadata and automatically prevents the same file from being loaded twice. When a file's path, size, and last-modified timestamp match the metadata, the file is skipped. You can override this with FORCE = TRUE, but the best practice is to leave duplicate prevention enabled in production.

COPY INTO (Unload) Syntax

The syntax for exporting data from a table out to a stage:

COPY INTO @<stage_name>/<path>/
  FROM <table_name> | (<SELECT statement>)
  FILE_FORMAT = (TYPE = CSV | JSON | PARQUET)
  SINGLE = TRUE | FALSE
  MAX_FILE_SIZE = <bytes>
  HEADER = TRUE | FALSE
  OVERWRITE = TRUE | FALSE;

Key Parameters

The FROM clause on unload accepts a SELECT statement directly, so you can export filtered, aggregated, or transformed results rather than the entire table.

Designing FILE FORMAT

FILE FORMAT is an object that defines a data format and its parsing rules. By creating a named FILE FORMAT up front, you avoid restating the options in every COPY INTO statement.

CREATE FILE FORMAT my_csv_format
  TYPE = CSV
  FIELD_DELIMITER = ','
  RECORD_DELIMITER = '\n'
  SKIP_HEADER = 1
  FIELD_OPTIONALLY_ENCLOSED_BY = '"'
  NULL_IF = ('NULL', 'null', '')
  EMPTY_FIELD_AS_NULL = TRUE
  COMPRESSION = GZIP;

Supported Formats and Use Cases

Choosing the Right ON_ERROR Option

ON_ERROR is the key parameter that defines your error-handling strategy on data load.

When you use ON_ERROR = CONTINUE, the details of skipped error rows can be inspected after the fact with the VALIDATE function. SELECT * FROM TABLE(VALIDATE(my_table, JOB_ID => '_last')) returns the error rows from the most recent COPY INTO.

VALIDATION_MODE (Dry Run)

When VALIDATION_MODE is set, no data is loaded — Snowflake returns only the file validation results. It's a must-have for pre-flight checks before a production load.

COPY INTO my_table
  FROM @my_stage/data/
  FILE_FORMAT = (FORMAT_NAME = 'my_csv_format')
  VALIDATION_MODE = 'RETURN_ERRORS';

Stage Types and How to Choose

Practical Design Best Practices

1. Always create a named FILE FORMAT

Inlining format options in every COPY INTO statement quickly becomes unmaintainable. Use CREATE FILE FORMAT to create a named object and reference it from COPY INTO with FORMAT_NAME. When the format needs to change, editing the FILE FORMAT object in one place propagates the change to every COPY INTO statement.

2. Pre-validate with VALIDATION_MODE

Especially for data whose quality you don't control — like CSVs received from external partners — run VALIDATION_MODE = 'RETURN_ERRORS' as a pre-check. It surfaces file format mismatches, data type errors, and NULL constraint violations before the production load runs.

3. Keep stages clean with PURGE = TRUE

Leaving loaded files behind in a stage accumulates storage cost and slows down file listing on subsequent COPY INTO runs. Either set PURGE = TRUE to auto-delete loaded files or run REMOVE @stage on a regular schedule.

4. Tune file sizes to 100-250 MB

Snowflake's documentation recommends 100-250 MB compressed as the sweet spot for COPY INTO file sizes. Files that are too small (a few KB to a few MB) carry too much overhead; files that are too large (over 1 GB) hurt parallel processing efficiency.

Check Your Understanding

Frequently Asked Questions