Course contents

SnowPro Core COF-C03 Study GuidePart 4: Data loading and storageLoading Data: COPY INTO <table>

Loading Data: COPY INTO <table>

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.

Ingestion path · Bulk COPY
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
COPY INTO
ORDER_IDSTOREAMOUNTORDERED_AT
10001PAR-0142.502026-10-25 09:14:00
10002LYO-03NULL2026-10-25 09:15:12
10003 rejected: 'abc' isn't a number
  1. 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.

  2. Upload the files from the laptop

    -- In SnowSQL; files are gzip-compressed on upload by default
    PUT 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.

  3. Check what arrived

    LIST @sales_db.raw.raw_files/orders/;

    ExpectTwo .csv.gz files with their sizes and MD5 hashes. No warehouse is needed.

  4. 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.

  5. 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.

  6. Audit the load

    SELECT * FROM TABLE(VALIDATE(sales_db.core.orders, JOB_ID => '_last'));
    
    SELECT file_name, row_count, row_parsed, error_count, status
    FROM 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.

  7. 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

The same two staged files loaded under four ON_ERROR values. orders_a.csv has 1,000 rows with one bad row, orders_b.csv has 1,000 valid rows. ABORT_STATEMENT, the bulk default, loads 0 rows and the statement fails. CONTINUE loads 1,999 rows and reports the error. SKIP_FILE, the Snowpipe default, skips orders_a and loads 1,000 rows. SKIP_FILE_5 skips a file only once it has 5 errors, so here it loads 1,999 rows. Counts are illustrative.STAGE · TWO FILESON_ERROR VALUE → RESULTFILEorders_a.csv1,000 rowsrow 412 is badFILEorders_b.csv1,000 rowsall validSTATEMENTCOPY INTOORDERSON_ERRORABORT_STATEMENTbulk COPY default0 rowsstatement failsON_ERRORCONTINUEskip bad rows1,999 rows1 error reportedON_ERRORSKIP_FILESnowpipe default1,000 rowsorders_a skippedON_ERRORSKIP_FILE_5skips a file at 5 errors1,999 rowsunder the limit

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_STATEMENTbulk COPY default

    0 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

Figure 4.4 The same two files under four ON_ERROR values. ABORT_STATEMENT, the bulk default, loads nothing when any row fails. SKIP_FILE drops the whole file that contains the error. CONTINUE and SKIP_FILE_5 load every valid row here. The row counts are illustrative.
ValueBehaviorFits
ABORT_STATEMENT (default for bulk COPY)The first error stops the whole COPY; nothing from the statement is loadedFinance data, where a partial load is worse than none
CONTINUESkip bad rows, load the restClickstream, logs
SKIP_FILE (default for Snowpipe)Skip any file that contains an errorFiles that must be all-or-nothing one by one
SKIP_FILE_n, SKIP_FILE_n%Skip a file once its errors reach a count or a percentage; otherwise load its valid rowsTolerating 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.

One file's load history over time. Day 0: COPY loads orders_01.csv and records it. Day 1: the same COPY skips it and loads 0 rows. Day 2: COPY with FORCE = TRUE reloads it and creates duplicate rows. Bulk COPY keeps this load metadata for 64 days, on the target table. Snowpipe keeps its own load history for 14 days, in the pipe. Day 65: the record has expired, so the status is uncertain only if the file's last-modified date, the table's first load and any earlier load of this file are all more than 64 days old, and then COPY skips it unless LOAD_UNCERTAIN_FILES = TRUE.DAY 0COPYloaded · recordedDAY 1Same COPYskipped · 0 rowsDAY 2COPY … FORCEreloaded · duplicatesDAY 65Record expiredneedsLOAD_UNCERTAIN_FILES012146465daysBulk COPY: 64 days, stored on the target tableSnowpipe: 14 days, stored in the pipeInside the window a repeated COPY skips the file; FORCE = TRUE overrides it. Axis not to scale.
0

DAY 0COPYloaded · recorded

1

DAY 1Same COPYskipped · 0 rows

2

DAY 2COPY … FORCEreloaded · duplicates

//
14
64
65

DAY 65Record expiredneedsLOAD_UNCERTAIN_FILES

days
  • 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.

Figure 4.5 One file's load metadata over time. FORCE = TRUE reloads the file anyway and creates duplicate rows. COPY skips a file as uncertain when it can no longer tell whether the file was loaded. That happens only when the file's last-modified date, the table's first load and any earlier load of this file are all more than 64 days old. LOAD_UNCERTAIN_FILES = TRUE loads such files and still skips the ones it knows.

Checking a load before and after

Checking a load at three moments. Before: VALIDATION_MODE turns COPY into a dry run that loads nothing and can't be used with a transforming SELECT. During: COPY INTO loads the rows and gets a query ID. After: VALIDATE with that job ID returns the rejected rows. Any time: COPY_HISTORY shows the status of each file, 14 days through the table function and 365 days through the ACCOUNT_USAGE view.BEFOREVALIDATION_MODEdry run, loads nothingno transforms allowedLOADCOPY INTOrows committedreturns a query IDAFTERVALIDATE(table,JOB_ID => …)rows that failedin that loadANY TIMECOPY_HISTORYstatus per file14 d function · 365 d viewVALIDATION_MODE is a dry run before; VALIDATE() reports on a load that already ran

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

Figure 4.6 Three tools, three moments. VALIDATION_MODE is a dry run before the load and can't be used on a COPY that transforms data. VALIDATE reports the failed rows of one load that already ran. COPY_HISTORY shows file status for both COPY and Snowpipe: 14 days through the table function, 365 days through the ACCOUNT_USAGE view.

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.

What a COPY transformation may and may not do. Allowed inside COPY INTO … FROM (SELECT …): reorder columns, omit columns, cast types, add METADATA$FILENAME and use a subset of functions. Not allowed: WHERE filters, JOIN, GROUP BY and aggregates, FLATTEN, ORDER BY and LIMIT. To filter rows, run INSERT INTO … SELECT … FROM @stage WHERE …, which doesn't use load metadata, so a rerun inserts the rows again.ALLOWED IN COPY … FROM (SELECT …)NOT ALLOWED IN COPYReorder columns ($3, $1)✓Omit columns✓Cast ($1::NUMBER)✓METADATA$FILENAME✓A subset of functions✓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 againCOPY keeps load metadata:a rerun skips files already loaded

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

Figure 4.7 What a COPY transformation may and may not do. The allowed list reshapes columns; anything that filters, combines or reorders rows belongs in a normal query over the stage instead.
-- Reshape while loading: pick, cast, and record the source file
COPY 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 COPY
INSERT 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.

Watch out
  • 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.
Exam takeaway
  • 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.