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
ON_ERROR:ABORT_STATEMENT(COPY default),SKIP_FILE(Snowpipe default),SKIP_FILE_<num>,SKIP_FILE_<num>%,CONTINUE.VALIDATION_MODEchecks without loading;VALIDATEreports errors from a past load.MATCH_BY_COLUMN_NAMEmaps by name;ENFORCE_LENGTHdefaults 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.
How it works
Loading options that address errors
ON_ERROR decides what a bad row does. Know each value.
| Value | Behavior |
|---|---|
ABORT_STATEMENT | Stops at the first error; nothing from the statement is loaded. Default for bulk COPY |
SKIP_FILE | Skips 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 |
CONTINUE | Loads 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
| Option | Effect |
|---|---|
FILES or PATTERN | Load a named list or a regular expression match of files |
FORCE = TRUE | Reload files even if load metadata says they loaded |
LOAD_UNCERTAIN_FILES = TRUE | Load files whose status is unknown because history expired |
PURGE = TRUE | Delete staged files after a successful load |
SIZE_LIMIT | Stop starting new files once the byte total is reached |
RETURN_FAILED_ONLY = TRUE | Return only files that failed in the result |
MATCH_BY_COLUMN_NAME | Map columns by name: CASE_SENSITIVE, CASE_INSENSITIVE or NONE (default) |
ENFORCE_LENGTH | If TRUE (default), a string longer than the column fails; FALSE truncates |
TRUNCATECOLUMNS | The 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.
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;
| Option | What to know |
|---|---|
FILE_FORMAT | CSV (the default), JSON or Parquet |
SINGLE | FALSE by default, so output is split into several files written in parallel; TRUE writes one file |
MAX_FILE_SIZE | Upper size per file, default 16 MB (16777216 bytes); larger values, up to 5 GB for a single file, give fewer bigger files |
OVERWRITE | FALSE by default; TRUE replaces existing files of the same name |
PARTITION BY | Writes files into folders by an expression, for example a date; good for data lake layouts |
HEADER | TRUE writes column names (CSV) or keeps them in Parquet |
INCLUDE_QUERY_ID | Adds the query ID to file names so reruns do not collide |
DETAILED_OUTPUT | Returns 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.
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_MODEchecks without loading;VALIDATEreports errors from a past load.MATCH_BY_COLUMN_NAMEmaps by name;ENFORCE_LENGTHdefaults to TRUE.- Unload defaults: CSV,
SINGLE = FALSE, 16 MBMAX_FILE_SIZE,OVERWRITE = FALSE. - Use
PARTITION BYfor folder layouts,INCLUDE_QUERY_IDto avoid name clashes,GETto download from an internal stage.
Check yourself
Which
ON_ERRORvalue 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, withMAX_FILE_SIZEraised enough for the data.How can you test a load for errors without loading any data?
Run COPY with
VALIDATION_MODE = RETURN_ERRORS(orRETURN_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.