Data Loading, Unloading, and Connectivity · Topic 3 of 7
COPY INTO and Error Handling Options
Master COPY INTO for the SnowPro Core exam: load metadata, FORCE and PURGE, transformations, ON_ERROR choices, VALIDATION_MODE and load history views.
- 5 min
- COPY load metadata lasts 64 days and stops duplicate loads of unchanged files.
FORCE = TRUEreloads everything;LOAD_UNCERTAIN_FILES = TRUEhandles files older than 64 days;PURGE = TRUEdeletes loaded files.- COPY transformations allow reorder, omit, cast and truncate, but not WHERE, JOIN, GROUP BY or FLATTEN.
What it is
COPY INTO <table> is Snowflake's bulk load command. It reads staged files on a running virtual warehouse, parses them with a file format, optionally transforms the columns, and inserts the rows in one transaction per statement.
Its copy options decide which files to read, whether to reload old files, what to do with files after the load, and how to react to bad rows. The error handling options are a heavy exam topic, because each scenario has exactly one setting that fits.
How it works
Choosing the files
COPY INTO sales
FROM @my_stage/2026/11/
FILE_FORMAT = (FORMAT_NAME = 'my_csv')
PATTERN = '.*sales_.*[.]csv[.]gz'
ON_ERROR = 'SKIP_FILE';
- Path: a folder prefix after the stage name limits the scan.
FILES = ('a.csv', 'b.csv'): names files explicitly, up to 1,000 file names.PATTERN: a regular expression matched against file paths.
Load metadata: why files are not loaded twice
Snowflake keeps load metadata for each table: which files were loaded, with their checksum. It expires after 64 days. A rerun of the same COPY skips files already loaded, so COPY is safe to repeat.
| Option | Default | Effect |
|---|---|---|
FORCE | FALSE | TRUE loads every file again and ignores load metadata, which can duplicate rows |
LOAD_UNCERTAIN_FILES | FALSE | TRUE loads files whose load status is unknown because metadata is older than 64 days |
PURGE | FALSE | TRUE deletes files from the stage after they load successfully |
SIZE_LIMIT | none | Stops picking new files after this many bytes; at least one file always loads |
MATCH_BY_COLUMN_NAME | NONE | Loads columns by name from JSON, Avro, ORC, Parquet or CSV files, case sensitive or case insensitive |
TRUNCATECOLUMNS | FALSE | TRUE cuts strings that are too long instead of failing (ENFORCE_LENGTH is the reverse) |
If a file is changed and staged again with the same name, its checksum changes and COPY loads it again. If it is unchanged, COPY skips it silently and reports "0 files loaded", which is a common exam trap.
Transforming during the load
COPY can use a SELECT over the staged columns $1, $2 and so on, which is called a COPY transformation.
COPY INTO customers (id, name, signup_date)
FROM (SELECT $1, UPPER($2), TO_DATE($4) FROM @my_stage)
FILE_FORMAT = (FORMAT_NAME = 'my_csv');
| Supported in a COPY transformation | Not supported |
|---|---|
| Reordering columns | WHERE filtering |
| Omitting columns | JOIN and GROUP BY |
| Casts and a subset of functions | FLATTEN |
| Truncating strings | ORDER BY, LIMIT, TOP |
| Pulling elements out of semi-structured data | Using it from a table stage |
To filter or join, load into a staging table first and transform with SQL, or query the stage directly in an INSERT ... SELECT.
ON_ERROR: what happens to bad rows
| Value | Behavior | Typical use |
|---|---|---|
ABORT_STATEMENT | Stops the whole load at the first error; nothing loads | Default for bulk COPY; strict pipelines |
CONTINUE | Loads every good row and skips bad rows | Keep partial data, review errors later |
SKIP_FILE | Skips any file that has an error | Default for Snowpipe |
SKIP_FILE_n | Skips a file when it has n or more errors | Allow a few bad rows per file |
'SKIP_FILE_n%' | Skips a file when the error rate reaches n percent | Tolerate a percentage of bad rows |
VALIDATION_MODE: test before you load
VALIDATION_MODE checks files without loading any rows.
RETURN_n_ROWS, such asRETURN_10_ROWS, parses the first n rows and fails at the first error.RETURN_ERRORSreturns all errors (parsing, conversion and so on) across the files in the statement.RETURN_ALL_ERRORSalso includes errors in files that were partly loaded earlier withON_ERROR = CONTINUE.
It does not work with COPY transformations or with MATCH_BY_COLUMN_NAME.
Reviewing loads after the fact
- The
VALIDATEtable function returns the errors from a past COPY by its query ID. COPY_HISTORY(INFORMATION_SCHEMA table function) shows load activity for the last 14 days, including Snowpipe.LOAD_HISTORY(INFORMATION_SCHEMA view) shows COPY loads for the last 14 days but not Snowpipe loads.SNOWFLAKE.ACCOUNT_USAGE.COPY_HISTORYkeeps up to 365 days, with some latency.RETURN_FAILED_ONLY = TRUEmakes the COPY result list only the files that failed.
Exam wording maps cleanly. "Load nothing if any row is bad" is ABORT_STATEMENT. "Load the good rows" is CONTINUE. "Check the files first without loading" is VALIDATION_MODE. "See the errors from yesterday's load" is VALIDATE.
Common mistakes
Using FORCE to fix a skipped file. FORCE reloads every file in scope and can duplicate data. If only one old file is uncertain, LOAD_UNCERTAIN_FILES or a FILES list is safer.
Expecting VALIDATION_MODE with a SELECT transformation. It is not supported. Validate the raw load first, then add the transformation.
Filtering rows in COPY. A WHERE clause is not allowed in a COPY transformation. Load to a staging table and filter there.
Assuming CONTINUE is the default. Bulk COPY defaults to ABORT_STATEMENT; only Snowpipe defaults to SKIP_FILE.
What to remember for the exam
- COPY load metadata lasts 64 days and stops duplicate loads of unchanged files.
FORCE = TRUEreloads everything;LOAD_UNCERTAIN_FILES = TRUEhandles files older than 64 days;PURGE = TRUEdeletes loaded files.- COPY transformations allow reorder, omit, cast and truncate, but not WHERE, JOIN, GROUP BY or FLATTEN.
ON_ERROR:ABORT_STATEMENT(bulk default),CONTINUE,SKIP_FILE(Snowpipe default),SKIP_FILE_n,SKIP_FILE_n%.VALIDATION_MODEchecks without loading;VALIDATEreviews errors of a past load.LOAD_HISTORYexcludes Snowpipe;COPY_HISTORYincludes it.
Check yourself
You rerun the same COPY and it reports zero files loaded. Why?
Load metadata shows those files were already loaded in the last 64 days, so COPY skips them unless FORCE = TRUE.
A load must keep all good rows and skip bad ones. Which ON_ERROR value fits?
CONTINUE, which loads the valid rows and skips rows with errors.
How do you check files for errors without loading any data?
Run COPY INTO with VALIDATION_MODE, such as RETURN_ERRORS or RETURN_10_ROWS.
Topic 3 of 7 in Data Loading, Unloading, and Connectivity
Test yourself on Data Loading, Unloading, and Connectivity
The full Snowflake SnowPro Core COF-C03 question bank has 845 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.