Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

How to Use validation_mode in COPY INTO Command in Snowflake

In Snowflake, use the validation_mode option in the COPY INTO command to check data file validity without loading data. Set validation_mode='RETURN_ERRORS' to return errors or validation_mode='RETURN_ALL_ERRORS' to get all errors found during validation.
📐

Syntax

The validation_mode option in the COPY INTO command controls how Snowflake validates data files before loading. It accepts these values:

  • RETURN_ERRORS: Returns the first errors found and stops.
  • RETURN_ALL_ERRORS: Returns all errors found in the files.
  • RETURN_NONE (default): Loads data without validation-only mode.

Use it as a separate option in the COPY INTO command, not inside the FILE_FORMAT.

sql
COPY INTO my_table
FROM @my_stage/data.csv
FILE_FORMAT = (TYPE = 'CSV')
VALIDATION_MODE = 'RETURN_ERRORS';
💻

Example

This example shows how to validate a CSV file for errors without loading it into the table. It returns the first errors found.

sql
COPY INTO my_table
FROM @my_stage/data.csv
FILE_FORMAT = (TYPE = 'CSV' FIELD_OPTIONALLY_ENCLOSED_BY='"')
VALIDATION_MODE = 'RETURN_ERRORS';
Output
1 validation error found in file data.csv at line 3: invalid integer value in column 2
⚠️

Common Pitfalls

  • Not specifying VALIDATION_MODE defaults to loading data, so no validation-only check happens.
  • Using RETURN_ERRORS returns only the first error, which might hide other issues.
  • For large files, RETURN_ALL_ERRORS can be slow and produce many errors.
  • Ensure the file format matches the data to avoid false errors.
sql
/* Wrong: No validation_mode, data loads directly */
COPY INTO my_table
FROM @my_stage/data.csv
FILE_FORMAT = (TYPE = 'CSV');

/* Right: Validate and return all errors */
COPY INTO my_table
FROM @my_stage/data.csv
FILE_FORMAT = (TYPE = 'CSV')
VALIDATION_MODE = 'RETURN_ALL_ERRORS';
📊

Quick Reference

VALIDATION_MODE ValueDescription
RETURN_NONEDefault. Loads data without validation-only mode.
RETURN_ERRORSReturns first errors found and stops validation.
RETURN_ALL_ERRORSReturns all errors found in the data files.
✅

Key Takeaways

Use VALIDATION_MODE in COPY INTO to check data files without loading.
RETURN_ERRORS returns the first error; RETURN_ALL_ERRORS returns all errors.
Without VALIDATION_MODE, COPY INTO loads data directly without validation.
Match file format settings to your data to avoid false validation errors.