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_MODEdefaults to loading data, so no validation-only check happens. - Using
RETURN_ERRORSreturns only the first error, which might hide other issues. - For large files,
RETURN_ALL_ERRORScan 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 Value | Description |
|---|---|
| RETURN_NONE | Default. Loads data without validation-only mode. |
| RETURN_ERRORS | Returns first errors found and stops validation. |
| RETURN_ALL_ERRORS | Returns 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.
