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

  1. New file arrives in the stage
  2. Run COPY with VALIDATION_MODE = 'RETURN_ERRORS'
  3. Any errors reported? Yes: Fix the file or format, or choose an ON_ERROR rule. No: Run the real COPY.
  4. COPY loads rows according to ON_ERROR
  5. Check load results and VALIDATE() for rejected rows
  6. 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

Snowflake topics

Snowflake Fundamentals

Warehouses & Performance

Staging & SnowSQL

Tables & Data Handling

Cloud Integrations

Storage & Optimization

Security & Governance

Views, Pipelines & Automation

Data Types & SQL Fundamentals

Semi-Structured Data Deep Dive

Advanced Features

Data Sharing & Collaboration

Cost Management & Monitoring

Databases, Schemas & Objects

Performance Deep Dive

Advanced Security & Governance

Stored Procedures & UDFs

Ecosystem & Integrations

SnowPro Certification Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Mastering Snowflake

The modern data cloud, from architecture to SnowPro certification. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice Snowflake SQL Sharpen the SQL you use in Snowflake: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Snowflake  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Unlike the SQL and Python courses, Snowflake needs a real account — there's no in-browser engine for it. Click Copy above, then paste it into a free Snowflake trial (30 days, no card required) and run it in Snowsight.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic