Skip to content
Tutorials Center
My learning

Topics

Lesson 13 of 38 ยท Loading and Unloading Data

Validating Loads and Handling Errors

Find and fix bad rows when loading data into Snowflake. Use VALIDATION_MODE, ON_ERROR options, the VALIDATE function and COPY_HISTORY, plus a quarantine pattern.

Real files are messy: a date in the wrong format, a text value in a number column, a row with an extra comma. A good loading process expects this. Snowflake gives you tools to check a file before loading, decide what happens to bad rows and review what went wrong afterwards.

Dry run: VALIDATION_MODE

Add VALIDATION_MODE to COPY INTO and Snowflake parses the files and reports problems without loading anything.

SQL
-- Snowflake SQL (read-only)
COPY INTO shop.raw.orders
FROM @shop.raw.landing/orders/
FILE_FORMAT = (FORMAT_NAME = 'shop.raw.csv_fmt')
VALIDATION_MODE = RETURN_ERRORS;

Values: RETURN_ERRORS (all errors), RETURN_n_ROWS (the first n parsed rows, to eyeball them), RETURN_ALL_ERRORS (also errors from files partially loaded before). The output lists the file, line number, column, error code and message, for example "Numeric value 'abc' is not recognized".

Deciding what happens: ON_ERROR

ON_ERROR controls what a real load does when it meets a bad row:

Value Behavior
ABORT_STATEMENT Stop; nothing from this statement is committed (default for bulk COPY)
CONTINUE Skip bad rows, load the rest of the file
SKIP_FILE Skip any file that has an error
SKIP_FILE_5 Skip a file when it has 5 or more bad rows
SKIP_FILE_2% Skip a file when 2 percent or more of rows are bad
SQL
-- Snowflake SQL (read-only)
COPY INTO shop.raw.orders
FROM @shop.raw.landing/orders/
FILE_FORMAT = (FORMAT_NAME = 'shop.raw.csv_fmt')
ON_ERROR = 'SKIP_FILE_3';

Choose by data quality needs. Financial data usually wants ABORT_STATEMENT or SKIP_FILE (all or nothing), so you never load half a file. Clickstream logs may tolerate CONTINUE. Never use CONTINUE blindly: you lose rows silently, and the result only shows errors_seen.

Reading the errors after a load

If you used CONTINUE or SKIP_FILE, ask Snowflake which rows failed. The VALIDATE table function re-reads the last (or a given) COPY job and returns its errors:

SQL
-- Snowflake SQL (read-only)
SELECT * FROM TABLE(VALIDATE(shop.raw.orders, JOB_ID => '_last'));

To see load activity across time use COPY_HISTORY:

SQL
-- Snowflake SQL (read-only)
SELECT file_name, status, row_count, row_parsed, first_error_message, first_error_line_number
FROM TABLE(information_schema.copy_history(
  TABLE_NAME => 'ORDERS',
  START_TIME => DATEADD(hours, -24, CURRENT_TIMESTAMP())))
ORDER BY last_load_time DESC;

Statuses include LOADED, LOAD_FAILED, PARTIALLY_LOADED and LOAD_SKIPPED. Build a small monitoring query that alerts when a file shows anything other than LOADED.

The quarantine pattern

A robust pipeline separates "got it" from "needs a human". Load everything leniently into a raw table of strings, then move valid rows to a typed table and bad rows to a reject table with the reason. You can use TRY_CAST-style functions (Snowflake has TRY_TO_NUMBER, TRY_TO_DATE, TRY_TO_TIMESTAMP) to test without failing:

SQL
-- Snowflake SQL (read-only)
INSERT INTO shop.clean.orders
SELECT id::NUMBER, customer, TRY_TO_NUMBER(total, 10, 2), TRY_TO_DATE(ordered_on)
FROM shop.raw.orders_text
WHERE TRY_TO_NUMBER(total, 10, 2) IS NOT NULL AND TRY_TO_DATE(ordered_on) IS NOT NULL;

INSERT INTO shop.clean.orders_rejects
SELECT * FROM shop.raw.orders_text
WHERE TRY_TO_NUMBER(total, 10, 2) IS NULL OR TRY_TO_DATE(ordered_on) IS NULL;

The same pattern runs in your browser with SQLite's CAST checks. The stand-in uses a GLOB test to flag text that is not a number, then splits clean rows from rejects:

SQL Stand-in: split good rows from rejects

Press Run (or Ctrl + Enter) to see the result here.

A second stand-in records why each row was rejected, which is what your real reject table should hold:

SQL Stand-in: a reject table with reasons

Press Run (or Ctrl + Enter) to see the result here.

Common errors and fixes

Message (shortened) Likely cause Fix
Numeric value '...' is not recognized Text in a number column, or wrong delimiter Check FIELD_DELIMITER, quotes, use NULL_IF
Number of columns in file does not match Extra or missing field ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE or fix the file
Date '...' is not recognized Format mismatch Set DATE_FORMAT or convert with TO_DATE($4, 'DD/MM/YYYY')
String '...' is too long Value exceeds VARCHAR(n) Widen the column or TRUNCATECOLUMNS = TRUE
Found character ... instead of field delimiter Unescaped quote Set FIELD_OPTIONALLY_ENCLOSED_BY = '"'
JSON parse error Concatenated objects or outer array STRIP_OUTER_ARRAY = TRUE, check the file

Start every new feed with VALIDATION_MODE = RETURN_10_ROWS, look at the parsed rows, then RETURN_ERRORS, and only then load for real.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does VALIDATION_MODE = RETURN_ERRORS do?

    It is a dry run. No data is loaded.
  2. 2. A file with 1 bad row among 1,000,000 must not load at all. Which ON_ERROR value fits?

    SKIP_FILE (or ABORT_STATEMENT) ensures partial files are not loaded.
  3. 3. Which function lets you find the rows that failed in the previous COPY?

    TABLE(VALIDATE(table, JOB_ID => '_last')) returns the errors of that load.
  4. 4. What is a quarantine (reject) table for?

    It separates unusable rows from the clean table without losing them.

Summary

  • VALIDATION_MODE checks files without loading.
  • ON_ERROR decides between all-or-nothing and skipping bad rows or files; CONTINUE can hide data loss.
  • VALIDATE and COPY_HISTORY show what failed after the fact.
  • TRY_TO_* functions and a reject table give a robust pipeline.
  • Validate new feeds with a dry run before the first real load.

Next: Continuous Loading with Snowpipe