Data Engineering · Topic 5 of 16

COPY Parameters, Error Handling and Data Unloading

Master COPY INTO options for loading and error handling, then unload data with COPY INTO location: file size, single file, partitioning, formats and GET.

  • 4 min
In a nutshell
  • ON_ERROR: ABORT_STATEMENT (COPY default), SKIP_FILE (Snowpipe default), SKIP_FILE_<num>, SKIP_FILE_<num>%, CONTINUE.
  • VALIDATION_MODE checks without loading; VALIDATE reports errors from a past load.
  • MATCH_BY_COLUMN_NAME maps by name; ENFORCE_LENGTH defaults to TRUE.

What it is

COPY INTO <table> loads staged files and COPY INTO <location> unloads query results to files. Both have options that change what happens when data is bad, how much is moved and how files are named. The exam gives a symptom, such as "one bad row aborted a 10 file load" or "an unload produced thousands of small files", and asks which parameter fixes it.

Staged filesCOPY INTO tableTablegood rows loadedTable or queryCOPY INTO locationFiles in stageGET or cloud toolsConsumer

How it works

Loading options that address errors

ON_ERROR decides what a bad row does. Know each value.

ValueBehavior
ABORT_STATEMENTStops at the first error; nothing from the statement is loaded. Default for bulk COPY
SKIP_FILESkips any file that has an error and loads the rest. Default for Snowpipe
SKIP_FILE_<num>Skips a file when it has that many errors or more
SKIP_FILE_<num>%Skips a file when that percentage of rows have errors
CONTINUELoads the good rows of every file and skips only the bad rows

To find the bad rows after the fact, SELECT * FROM TABLE(VALIDATE(table, JOB_ID => '_last')) returns the errors from a previous COPY. To check before loading, add VALIDATION_MODE = RETURN_ERRORS (or RETURN_n_ROWS, RETURN_ALL_ERRORS); the data is validated and not loaded. Validation mode does not work together with a transformation query.

Other loading options

OptionEffect
FILES or PATTERNLoad a named list or a regular expression match of files
FORCE = TRUEReload files even if load metadata says they loaded
LOAD_UNCERTAIN_FILES = TRUELoad files whose status is unknown because history expired
PURGE = TRUEDelete staged files after a successful load
SIZE_LIMITStop starting new files once the byte total is reached
RETURN_FAILED_ONLY = TRUEReturn only files that failed in the result
MATCH_BY_COLUMN_NAMEMap columns by name: CASE_SENSITIVE, CASE_INSENSITIVE or NONE (default)
ENFORCE_LENGTHIf TRUE (default), a string longer than the column fails; FALSE truncates
TRUNCATECOLUMNSThe opposite sense: TRUE truncates, default FALSE

File format options matter too. ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE allows files with a different number of fields from the table. SKIP_HEADER, NULL_IF and REPLACE_INVALID_CHARACTERS clean up common data problems before they become errors.

Exam tip

A file that fails in an ON_ERROR = SKIP_FILE load is not retried automatically. Fix the file, then run COPY again; only the unloaded file is picked up because load metadata marks the others as done.

Unloading data

COPY INTO <location> writes the result of a table or a query to files.

COPY INTO @unload_stage/orders/
FROM (SELECT * FROM orders WHERE order_date >= '2026-01-01')
FILE_FORMAT = (TYPE = PARQUET)
PARTITION BY ('date=' || TO_VARCHAR(order_date))
MAX_FILE_SIZE = 268435456
HEADER = TRUE;
OptionWhat to know
FILE_FORMATCSV (the default), JSON or Parquet
SINGLEFALSE by default, so output is split into several files written in parallel; TRUE writes one file
MAX_FILE_SIZEUpper size per file, default 16 MB (16777216 bytes); larger values, up to 5 GB for a single file, give fewer bigger files
OVERWRITEFALSE by default; TRUE replaces existing files of the same name
PARTITION BYWrites files into folders by an expression, for example a date; good for data lake layouts
HEADERTRUE writes column names (CSV) or keeps them in Parquet
INCLUDE_QUERY_IDAdds the query ID to file names so reruns do not collide
DETAILED_OUTPUTReturns one row per file instead of a summary

Unloading to external storage needs a stage that uses a storage integration with write permission. To get files from an internal stage onto a local machine, use GET from SnowSQL, Snowflake CLI or a driver. JSON unloads need a single VARIANT or OBJECT column, so build one with OBJECT_CONSTRUCT(*) when the source has several columns.

The default output is split into files in parallel, which is fast but yields many files. If a downstream system wants one file, use SINGLE = TRUE with a large MAX_FILE_SIZE, accepting that a single file is written by one thread.

QuerySELECTparallel threadsMany filesSINGLE FALSEwritten toExternal stagecloud storageQueryone threadOne fileSINGLE TRUE

Common mistakes

Using CONTINUE and forgetting the skipped rows. CONTINUE silently drops bad rows. Check results with VALIDATE or the COPY_HISTORY error columns.

Using FORCE to retry. It reloads good files too and creates duplicates.

Mixing up ENFORCE_LENGTH and TRUNCATECOLUMNS. They are opposites: ENFORCE_LENGTH = FALSE and TRUNCATECOLUMNS = TRUE both truncate long strings.

Forgetting default CSV on unload. If you do not give a file format, the output is CSV text, not Parquet.

Setting SINGLE = TRUE for a huge result. A single file has a size cap and loses parallelism.

What to remember for the exam

  • ON_ERROR: ABORT_STATEMENT (COPY default), SKIP_FILE (Snowpipe default), SKIP_FILE_<num>, SKIP_FILE_<num>%, CONTINUE.
  • VALIDATION_MODE checks without loading; VALIDATE reports errors from a past load.
  • MATCH_BY_COLUMN_NAME maps by name; ENFORCE_LENGTH defaults to TRUE.
  • Unload defaults: CSV, SINGLE = FALSE, 16 MB MAX_FILE_SIZE, OVERWRITE = FALSE.
  • Use PARTITION BY for folder layouts, INCLUDE_QUERY_ID to avoid name clashes, GET to download from an internal stage.

Check yourself

  • Which ON_ERROR value loads every good row and skips only the bad rows?

    CONTINUE.

  • An unload creates many files but the consumer wants one. What do you set?

    SINGLE = TRUE, with MAX_FILE_SIZE raised enough for the data.

  • How can you test a load for errors without loading any data?

    Run COPY with VALIDATION_MODE = RETURN_ERRORS (or RETURN_ALL_ERRORS).

Topic 5 of 16 in Data Engineering

Test yourself on Data Engineering

The full Snowflake ARA-C01 question bank has 687 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.