Loading Data with COPY INTO

Analogy categoryAnalogies

COPY INTO <table> is Snowflake's primary bulk-loading command: it reads files that have already been placed in a stage (internal or external) and inserts their contents into a target table according to a file format definition. Snowflake tracks metadata about every file it has loaded for up to 64 days, so re-running the same COPY INTO command against an already-loaded file is a no-op by default, which makes the command safe to schedule repeatedly without creating duplicate rows.

🏏

Cricket analogy: Like a scorer who only logs a delivery once even if the umpire replays the footage twice — Snowflake's load metadata acts as that scorer, refusing to re-credit runs for a ball (file) already recorded in the innings.

Stage-to-Table Loading Mechanics

The loading pipeline runs in three stages: files land in a stage location, COPY INTO reads and parses each file using the rules in its FILE_FORMAT (delimiter, encoding, compression, header handling), and a running virtual warehouse provides the compute to transform rows and write them into the table's micro-partitions. Because parsing and writing happen on compute you control, the size of the warehouse directly affects load throughput for large batches, while the stage itself is just object storage and costs nothing extra to read from.

🏏

Cricket analogy: Like a net session where the ball hopper (stage) holds the balls, the bowling machine settings (file format) determine pace and line, and the batter's reflexes (warehouse compute) determine how many deliveries get processed per minute — a stronger batter handles a faster machine.

Snowflake automatically parallelizes loading across files: each file is assigned to a single thread, so a batch of many moderately sized files loads far faster than one enormous file that only one thread can chew through. The recommended sweet spot is compressed files between roughly 100 MB and 250 MB, since files below that waste parallelism overhead and files far above it become a bottleneck on a single thread regardless of how large the warehouse is.

🏏

Cricket analogy: Like splitting a long list of catches to review into several umpire review clips rather than one giant highlight reel — several match officials (threads) can review clips in parallel, but one official stuck with a 90-minute reel becomes the bottleneck.

File Formats and Error Handling

A named file format object, created with CREATE FILE FORMAT, captures reusable parsing rules such as TYPE (CSV, JSON, PARQUET, AVRO), FIELD_DELIMITER, SKIP_HEADER, NULL_IF, and COMPRESSION, so you don't have to repeat these options inline on every COPY INTO call. The ON_ERROR option controls what happens when a row fails to parse: CONTINUE skips just the bad row, SKIP_FILE abandons the whole file on the first error, and ABORT_STATEMENT (the default) halts the entire load the moment any error is hit.

🏏

Cricket analogy: Like a ground's standing playing conditions (file format) — over-rate rules, boundary size — defined once by the venue rather than re-negotiated every match, while the match referee's ruling on a no-ball (ON_ERROR) decides whether play continues, that over is scrapped, or the match is abandoned.

sql

-- Create a reusable file format for CSV loads
CREATE OR REPLACE FILE FORMAT csv_standard
  TYPE = 'CSV'
  FIELD_DELIMITER = ','
  SKIP_HEADER = 1
  NULL_IF = ('NULL', 'null', '')
  EMPTY_FIELD_AS_NULL = TRUE
  COMPRESSION = 'GZIP';

-- Load staged files into a table, skipping bad rows
COPY INTO sales.raw_orders
  FROM @sales.raw_stage/orders/
  FILE_FORMAT = (FORMAT_NAME = 'csv_standard')
  PATTERN = '.*orders_[0-9]{8}\\.csv\\.gz'
  ON_ERROR = 'CONTINUE'
  PURGE = TRUE;

Auditing Loads and Re-Running Safely

Before committing to a load you can run COPY INTO with VALIDATION_MODE = 'RETURN_ERRORS' (or RETURN_N_ROWS) to dry-run the parse without writing anything, catching schema mismatches early. After loading, the COPY_HISTORY table function (or the LOAD_HISTORY view in ACCOUNT_USAGE) lets you audit exactly which files loaded, how many rows they contributed, and any errors encountered, which is essential when a pipeline needs to prove every source file was accounted for.

🏏

Cricket analogy: Like a DRS review checking a delivery before the umpire's final call is locked in — VALIDATION_MODE is that review, while COPY_HISTORY is the match scorecard you consult afterward to confirm every over was recorded correctly.

COPY INTO will silently skip a file it believes it already loaded, based on file name and checksum in Snowflake's 64-day load metadata window. If you truly need to reload an unchanged file (for example after fixing a downstream table), you must explicitly pass FORCE = TRUE, otherwise the command reports success but loads zero new rows.

COPY INTO bulk-loads staged files into a table and tracks load metadata for 64 days to avoid duplicate loads.
Loading is automatically parallelized per file, so many 100-250 MB files load faster than one huge file.
Named FILE_FORMAT objects centralize parsing rules like delimiter, header, and compression settings.
ON_ERROR controls failure behavior: CONTINUE skips bad rows, SKIP_FILE drops the file, ABORT_STATEMENT halts everything.
VALIDATION_MODE lets you dry-run a load to catch schema errors before committing any rows.
COPY_HISTORY and LOAD_HISTORY provide an audit trail of exactly which files loaded and any errors.
Reloading an already-loaded file requires FORCE = TRUE, otherwise the command silently loads nothing new.