Handling load errors in Snowflake - Commands & Configuration

Introduction

When loading data into Snowflake, sometimes errors happen because of bad data or format issues. Handling load errors means finding and fixing these problems so your data loads correctly.

When you load CSV files and some rows have wrong data types.

When you want to skip bad records but still load the good ones.

When you want to see which rows failed and why.

When you want to automatically log load errors for later review.

When you want to prevent your entire load from failing due to a few bad rows.

Commands

Create a simple table to load data into. This table has three columns: id, name, and age.

Terminal

CREATE OR REPLACE TABLE my_table (id INT, name STRING, age INT);

Expected Output

Table MY_TABLE successfully created.

Upload the local CSV file data.csv to the Snowflake internal stage for the my_table table.

Terminal

PUT file:///tmp/data.csv @%my_table;

Expected Output

100% |################################| 1 MB | 1 MB/s | 00:01 | Upload successful.

Load data from the staged CSV file into my_table. The ON_ERROR='CONTINUE' option tells Snowflake to skip bad rows and continue loading the rest.

Terminal

COPY INTO my_table FROM @%my_table/data.csv FILE_FORMAT = (TYPE = 'CSV' FIELD_OPTIONALLY_ENCLOSED_BY='"') ON_ERROR = 'CONTINUE';

Expected Output

COPY INTO my_table executed successfully.

Rows loaded: 95

Rows skipped: 5

Errors logged in table LOAD_ERRORS.

→

ON_ERROR='CONTINUE' - Skip bad rows and continue loading good rows.

Check which rows failed during the last load job by using the VALIDATE function with the job ID from the load history.

Terminal

SELECT * FROM TABLE(VALIDATE(TABLE_NAME => 'MY_TABLE', JOB_ID => (SELECT MAX(LAST_LOAD_JOB_ID) FROM INFORMATION_SCHEMA.LOAD_HISTORY WHERE TABLE_NAME = 'MY_TABLE')));

Expected Output

ROW_NUMBER | ERROR_CODE | ERROR_MESSAGE

3          | 1001       | Invalid integer value in column 'age'

7          | 1002       | Missing required field 'id'

Key Concept

If you remember nothing else from this pattern, remember: use ON_ERROR options to control how Snowflake handles bad data during loads and use VALIDATE to find load errors.

Common Mistakes

Not using ON_ERROR option and expecting the load to skip bad rows automatically.

Without ON_ERROR='CONTINUE' or similar, Snowflake stops loading at the first error.

Always specify ON_ERROR='CONTINUE' or ON_ERROR='SKIP_FILE' to handle errors gracefully.

Not checking load errors after COPY INTO command.

You might miss which rows failed and why, leading to incomplete or wrong data.

Use VALIDATE function or check load history tables to review errors.

Summary

Create a target table before loading data.

Upload data files to Snowflake internal stage using PUT.

Use COPY INTO with ON_ERROR='CONTINUE' to skip bad rows during load.

Use VALIDATE function to find and review load errors.