Bird
Raised Fist0
SnowflakeDebug / FixBeginner · 4 min read

How to Handle Errors During Load in Snowflake

In Snowflake, handle errors during data load by using VALIDATION_MODE to check data before loading and ON_ERROR options to control error behavior. Use ERROR_LIMIT and ERRORS table functions to capture and review load errors for troubleshooting.
🔍

Why This Happens

Errors during data load in Snowflake usually happen because the data format or content does not match the table schema or constraints. For example, a string in a numeric column or missing required fields causes the load to fail.

sql
COPY INTO my_table
FROM @my_stage/file.csv
FILE_FORMAT = (TYPE = 'CSV')
ON_ERROR = 'ABORT_STATEMENT';
Output
SQL execution error: Numeric value 'abc' cannot be converted to NUMBER at row 5
🔧

The Fix

Change the ON_ERROR option to CONTINUE or SKIP_FILE to allow loading to proceed despite errors. Use VALIDATION_MODE = RETURN_ERRORS to preview errors without loading data. Capture errors using the VALIDATE command or query the LOAD_HISTORY and ERRORS table functions to analyze problem rows.

sql
COPY INTO my_table
FROM @my_stage/file.csv
FILE_FORMAT = (TYPE = 'CSV')
ON_ERROR = 'CONTINUE'
VALIDATION_MODE = 'RETURN_ERRORS';
Output
[{"row_number":5,"error":"Numeric value 'abc' cannot be converted to NUMBER"}, ...]
🛡️

Prevention

To avoid load errors, always validate data before loading using VALIDATE or VALIDATION_MODE. Use consistent file formats and schemas. Set ON_ERROR to handle errors gracefully and monitor load history regularly. Automate error logging and alerting to catch issues early.

⚠️

Related Errors

Common related errors include:

  • File format mismatch: Data does not match the declared file format.
  • Data truncation: Values exceed column size limits.
  • Permission errors: Insufficient access to stage or table.

Fix these by verifying file format settings, adjusting schema, and checking permissions.

✅

Key Takeaways

Use ON_ERROR options like CONTINUE to control load error handling.
Validate data before loading with VALIDATION_MODE to catch errors early.
Review error details using VALIDATE command and ERRORS table function.
Maintain consistent file formats and schemas to prevent errors.
Monitor load history and automate alerts for quick error detection.