Data Load Errors & Export Data
Snowflake · Staging & SnowSQL
Theory
COPY INTO checks every row as it loads and the ON_ERROR option decides what happens when a row is bad: ABORT_STATEMENT (the default for bulk COPY) stops the whole load, CONTINUE skips bad rows and loads the rest, and SKIP_FILE skips any file that has errors. VALIDATION_MODE = 'RETURN_ERRORS' or 'RETURN_5_ROWS' parses the files and reports problems without loading anything, and the VALIDATE() function shows the rows rejected by a past load.
The same command works in reverse: COPY INTO @my_stage/export/ FROM (SELECT ...) unloads query results to files in CSV, JSON or Parquet, which you can then download with GET or read from your bucket.
Why we use it
Real files contain bad rows: missing columns, text in number fields, broken dates. Without control over errors, one bad row could block a whole load or bad data could slip in silently.
When to use it
Use VALIDATION_MODE before loading a new or untrusted source. Use ABORT_STATEMENT when every row must load, CONTINUE when partial loads are acceptable and errors are reviewed later. Use COPY INTO <location> to export data for other systems.
Handling Bad Rows
ON_ERROR controls what happens when COPY INTO hits invalid data — continue past bad rows, skip the whole file, or abort the load entirely, depending on how strict the pipeline needs to be.
Previewing Failures Safely
VALIDATION_MODE lets you preview exactly which rows would fail a load without actually loading anything, so you can fix source data before committing.
Exporting Data Back Out
The same COPY INTO command works in reverse, exporting query results from a table back out to a stage as CSV, JSON, or Parquet files for downstream tools.
How it works
- New file arrives in the stage
- Run COPY with VALIDATION_MODE = 'RETURN_ERRORS'
- Any errors reported? Yes: Fix the file or format, or choose an ON_ERROR rule. No: Run the real COPY.
- COPY loads rows according to ON_ERROR
- Check load results and VALIDATE() for rejected rows
- Unload results with COPY INTO @stage when needed
Example
COPY INTO orders FROM @my_stage/orders.csv
FILE_FORMAT = (TYPE = 'CSV' SKIP_HEADER = 1)
VALIDATION_MODE = 'RETURN_ERRORS';
COPY INTO orders FROM @my_stage/orders.csv
FILE_FORMAT = (TYPE = 'CSV' SKIP_HEADER = 1)
VALIDATION_MODE = 'RETURN_5_ROWS';
COPY INTO orders FROM @my_stage/
FILE_FORMAT = (TYPE = 'CSV' SKIP_HEADER = 1 FIELD_DELIMITER = ',')
ON_ERROR = 'CONTINUE'
PURGE = TRUE;
SELECT * FROM TABLE(VALIDATE(orders, JOB_ID => '_last'));
COPY INTO @my_stage/export/orders_backup_
FROM (SELECT * FROM orders WHERE order_date >= '2024-01-01')
FILE_FORMAT = (TYPE = 'CSV' COMPRESSION = 'GZIP' HEADER = TRUE)
MAX_FILE_SIZE = 100000000
OVERWRITE = TRUE;Key points
- ON_ERROR controls whether bad rows are skipped or abort the load
- Default is ABORT_STATEMENT for COPY and SKIP_FILE for Snowpipe
- VALIDATION_MODE previews errors without loading anything
- VALIDATE() returns rows rejected by a previous COPY job
- COPY INTO <location> exports table or query data to a stage