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
In a nutshell
  • COPY load metadata lasts 64 days and stops duplicate loads of unchanged files.
  • FORCE = TRUE reloads everything; LOAD_UNCERTAIN_FILES = TRUE handles files older than 64 days; PURGE = TRUE deletes 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.

Stagestaged filesselect filesCOPY INTOfile format, optionscheckLoad metadataskip loaded filesinsertTablerows committed

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.

OptionDefaultEffect
FORCEFALSETRUE loads every file again and ignores load metadata, which can duplicate rows
LOAD_UNCERTAIN_FILESFALSETRUE loads files whose load status is unknown because metadata is older than 64 days
PURGEFALSETRUE deletes files from the stage after they load successfully
SIZE_LIMITnoneStops picking new files after this many bytes; at least one file always loads
MATCH_BY_COLUMN_NAMENONELoads columns by name from JSON, Avro, ORC, Parquet or CSV files, case sensitive or case insensitive
TRUNCATECOLUMNSFALSETRUE cuts strings that are too long instead of failing (ENFORCE_LENGTH is the reverse)
Watch out

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 transformationNot supported
Reordering columnsWHERE filtering
Omitting columnsJOIN and GROUP BY
Casts and a subset of functionsFLATTEN
Truncating stringsORDER BY, LIMIT, TOP
Pulling elements out of semi-structured dataUsing 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

ValueBehaviorTypical use
ABORT_STATEMENTStops the whole load at the first error; nothing loadsDefault for bulk COPY; strict pipelines
CONTINUELoads every good row and skips bad rowsKeep partial data, review errors later
SKIP_FILESkips any file that has an errorDefault for Snowpipe
SKIP_FILE_nSkips a file when it has n or more errorsAllow a few bad rows per file
'SKIP_FILE_n%'Skips a file when the error rate reaches n percentTolerate a percentage of bad rows
Bad row foundABORT STATEMENTWhole load failsnothing committedBad row foundCONTINUERow skippedother rows loadBad row foundSKIP FILEFile skippedother files load

VALIDATION_MODE: test before you load

VALIDATION_MODE checks files without loading any rows.

  • RETURN_n_ROWS, such as RETURN_10_ROWS, parses the first n rows and fails at the first error.
  • RETURN_ERRORS returns all errors (parsing, conversion and so on) across the files in the statement.
  • RETURN_ALL_ERRORS also includes errors in files that were partly loaded earlier with ON_ERROR = CONTINUE.

It does not work with COPY transformations or with MATCH_BY_COLUMN_NAME.

Reviewing loads after the fact

  • The VALIDATE table 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_HISTORY keeps up to 365 days, with some latency.
  • RETURN_FAILED_ONLY = TRUE makes the COPY result list only the files that failed.
Exam tip

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 = TRUE reloads everything; LOAD_UNCERTAIN_FILES = TRUE handles files older than 64 days; PURGE = TRUE deletes 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_MODE checks without loading; VALIDATE reviews errors of a past load.
  • LOAD_HISTORY excludes Snowpipe; COPY_HISTORY includes 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.