How to Handle Errors During Load in Snowflake
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.
COPY INTO my_table FROM @my_stage/file.csv FILE_FORMAT = (TYPE = 'CSV') ON_ERROR = 'ABORT_STATEMENT';
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.
COPY INTO my_table FROM @my_stage/file.csv FILE_FORMAT = (TYPE = 'CSV') ON_ERROR = 'CONTINUE' VALIDATION_MODE = 'RETURN_ERRORS';
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.
