Loading Data: COPY INTO <table>
In shortCOPY INTO a table is the bulk loader: it reads staged files on a warehouse you choose and writes the rows as new micro-partitions. Nothing happens until someone runs it.
- Source
- Files in an internal or external stage
- Trigger
- You run the statement: by hand, from a task or an orchestrator
- Processor
- Your virtual warehouse
- Destination
- A table
- Compute and billing
- Warehouse credits per second while it runs, 60-second minimum per start
The role running COPY needs INSERT on the table, USAGE on the file format if it names one, and READ on an internal stage or USAGE on an external one.
Walkthrough: from a CSV on Thomas's laptop to Alice's query
Thomas exports two days of orders from the point-of-sale system: orders_2026-10-25.csv and orders_2026-10-26.csv, about 120 MB each. Here is a slice of the first file and the rows it should produce.
order_id, store, amount, ordered_ at 10001,PAR-01, 42. 50, 2026-10-25 09:14:00 10002,LYO-03, , 2026-10-25 09:15:12 10003,PAR-01, abc, 2026-10-25 09:17:40
| ORDER_ | STORE | AMOUNT | ORDERED_ |
|---|---|---|---|
| 10001 | PAR-01 | 42.50 | 2026-10-25 09:14:00 |
| 10002 | LYO-03 | NULL | 2026-10-25 09:15:12 |
| 10003 rejected: 'abc' isn't a number | |||
Create the target table, once
CREATE TABLE sales_db. core. orders ( order_id NUMBER, store STRING, amount NUMBER(10, 2), ordered_at TIMESTAMP_ NTZ); ExpectAn empty table owned by DATA_ENGINEER.
Upload the files from the laptop
-- In SnowSQL; files are gzip-compressed on upload by defaultPUT file:/ / / exports/ orders_ 2026-10-2*. csv @sales_ db. raw. raw_ files/ orders/ ; ExpectOne result row per file with status UPLOADED and the target name ending in
.csv.gz.Check what arrived
LIST @sales_db. raw. raw_ files/ orders/ ; ExpectTwo
.csv.gzfiles with their sizes and MD5 hashes. No warehouse is needed.Dry run: find problems without loading
COPY INTO sales_db. core. orders FROM @sales_db. raw. raw_ files/ orders/ FILE_FORMAT = (FORMAT_NAME = 'sales_db. )raw. csv_ std' VALIDATION_MODE = RETURN_ERRORS; ExpectOne error row for order 10003 ('abc' in a NUMBER column). The table stays empty.
Load for real, keeping good rows
USE WAREHOUSE elt_wh;COPY INTO sales_db. core. orders FROM @sales_db. raw. raw_ files/ orders/ FILE_FORMAT = (FORMAT_NAME = 'sales_db. )raw. csv_ std' ON_ERROR = CONTINUE;ExpectOne result line per file: PARTIALLY_LOADED for the file with the bad row, LOADED for the other, with rows parsed, rows loaded and the first error. The empty amount becomes NULL because of EMPTY_FIELD_AS_NULL. ELT_WH is billed for the seconds it ran, with its 60-second minimum.
Audit the load
SELECT * FROM TABLE(VALIDATE( sales_ db. core. orders, JOB_ID => '_last')); SELECT file_name, row_count, row_parsed, error_count, statusFROM TABLE(sales_ db. INFORMATION_ SCHEMA. COPY_ HISTORY( TABLE_NAME => 'SALES_DB. ,CORE. ORDERS' START_TIME => DATEADD('hour', -1, CURRENT_TIMESTAMP( )))); ExpectVALIDATE returns the rejected row with its error message. COPY_HISTORY returns one row per file with its status.
Alice queries
As soon as the COPY commits, Alice can run
SELECT * FROM sales_db.core.orders, provided ANALYST holds the four grants from Part 3 (warehouse, database and schema USAGE, plus SELECT). Thomas leaves the files in the stage, so rerunning the load step tomorrow loads zero rows: the table's load metadata already records both files.
ON_ERROR: what one bad record costs
STAGE · TWO FILES
FILEorders_a.csv1,000 rowsrow 412 is bad
FILEorders_b.csv1,000 rowsall valid
STATEMENTCOPY INTOORDERS
ON_ERROR VALUE → RESULT
ON_ERRORABORT_
STATEMENT bulk COPY default0 rowsstatement fails
ON_ERRORCONTINUEskip bad rows
1,999 rows1 error reported
ON_ERRORSKIP_FILESnowpipe default
1,000 rowsorders_a skipped
ON_ERRORSKIP_FILE_5skips a file at 5 errors
1,999 rowsunder the limit
| Value | Behavior | Fits |
|---|---|---|
ABORT_ (default for bulk COPY) | The first error stops the whole COPY; nothing from the statement is loaded | Finance data, where a partial load is worse than none |
CONTINUE | Skip bad rows, load the rest | Clickstream, logs |
SKIP_ (default for Snowpipe) | Skip any file that contains an error | Files that must be all-or-nothing one by one |
SKIP_, SKIP_ | Skip a file once its errors reach a count or a percentage; otherwise load its valid rows | Tolerating a little noise per file |
Load metadata: why the same file isn't loaded twice
For each table, Snowflake keeps load metadata that records which files were loaded, identified by name, size and ETag (a checksum). A later COPY into the same table skips those files. The metadata expires after 64 days. TRUNCATE TABLE deletes the table's load metadata, so the next COPY loads the same files again. COPY decides what to skip from this 64-day load metadata; COPY_HISTORY only reports loads, for 14 or 365 days, so it can forget a load that COPY still remembers.
DAY 0COPYloaded · recorded
DAY 1Same COPYskipped · 0 rows
DAY 2COPY … FORCEreloaded · duplicates
DAY 65Record expiredneedsLOAD_UNCERTAIN_FILES
- Bulk COPY: 64 days, stored on the target table
- Snowpipe: 14 days, stored in the pipe
Inside the window a repeated COPY skips the file; FORCE = TRUE overrides it. Axis not to scale.
Checking a load before and after
BEFORE
VALIDATION_MODEdry run, loads nothingno transforms allowed
LOAD
COPY INTOrows committedreturns a query ID
AFTER
VALIDATE(table, JOB_ID => …)rows that failedin that load
ANY TIME
COPY_HISTORYstatus per file14 d function · 365 d view
VALIDATION_MODE is a dry run before; VALIDATE() reports on a load that already ran
Transforming during a load
COPY can load from a SELECT over the staged files. That SELECT can reshape columns but not filter rows. To keep only some rows, load everything and filter afterward, or query the stage with INSERT … SELECT. That second option doesn't use load metadata, so running it again inserts the rows again.
ALLOWED IN COPY … FROM (SELECT …)
✓Reorder columns ($3, $1)
✓Omit columns
✓Cast ($1::NUMBER)
✓METADATA$FILENAME
✓A subset of functions
COPY keeps load metadata: a rerun skips files already loaded
NOT ALLOWED IN COPY
✗WHERE filter
✗JOIN
✗GROUP BY, aggregates
✗FLATTEN
✗ORDER BY, LIMIT
USE INSTEADNeed to filter rows?INSERT INTO … SELECT …FROM @stage WHERE …no load metadata: a rerun inserts again
-- Reshape while loading: pick, cast, and record the source fileCOPY INTO sales_db. core. orders_ lineage (order_id, amount, source_file) FROM (SELECT $1::NUMBER, $3:: NUMBER( 10, 2), METADATA$FILENAME FROM @sales_db. raw. raw_ files/ orders/ ) FILE_FORMAT = (FORMAT_NAME = 'sales_db. );raw. csv_ std' -- Filter rows: a query over the stage, not a COPYINSERT INTO sales_db. core. big_ orders SELECT $1::NUMBER, $2, $3:: NUMBER( 10, 2), $4: : TIMESTAMP_ NTZ FROM @sales_db. raw. raw_ files/ orders/ (FILE_FORMAT => 'sales_ db. )raw. csv_ std' WHERE TRY_TO_ NUMBER( $3, 10, 2) > 1000;
MATCH_BY_COLUMN_NAME maps source columns to table columns by name instead of position. It works for semi-structured formats, and for CSV when the format sets PARSE_HEADER = TRUE. PARSE_HEADER = TRUE reads the column names from the first line, and you don't use SKIP_HEADER with it. MATCH_BY_COLUMN_NAME can't be combined with a transforming SELECT or with VALIDATION_MODE.
- COPY has no WHERE clause, even inside its SELECT.
- VALIDATION_MODE can't be used with a transforming SELECT.
- A COPY from a table stage (
@%t) can't use a transforming SELECT. Use a named stage for that. - VALIDATE reports rejected rows only for a load that skipped errors (ON_ERROR = CONTINUE or SKIP_FILE). It returns nothing for an ABORT_STATEMENT load, and it fails for a transforming COPY or once the load's metadata has expired.
JOB_ID => '_last'means the last load in your session. - FORCE = TRUE reloads files and duplicates rows.
- Bulk COPY runs on your warehouse; the default ON_ERROR is ABORT_STATEMENT.
- Load metadata lives on the target table for 64 days; FORCE = TRUE reloads and can duplicate.
- VALIDATION_MODE = before (dry run, no transforms). VALIDATE() = after.
- COPY transformations: reorder, omit, cast. No WHERE, JOIN, GROUP BY or FLATTEN.

