Snowflake's VALIDATION_MODE='RETRUN_ERROR' is not validating the file correctly
I am running below command to validate the data of a sample file with VALIDATION_MODE option but i do not get the expected errors.
copy into users from @%users/user_account.txt.gz
file_format=(type = 'CSV') validation_mode='RETURN_ERRORS';
the first 2 rows in the file are below
id,name,emailid,signupdate
1,user1,[email protected],13/10/2021
since the fist line has all character values (due to header) and in the copy into command i have not used the SKIP_HEADER and DATE_FORMAT file format options so i am expecting the command to return error information.However i get 0 records returned with a successful execution of above command.
When i use below command
copy into users from @%users/user_account.txt.gz
file_format=(type = 'CSV') validation_mode='RETURN_1_ROWS';
i get data type error for header record.Am i not using the first command in proper way ?
Revising answer... I believe what you are running into is a combination of on_error and validation_mode that isn't working together.
copy into users from @%users/user_account.txt.gz
file_format=(type = CSV)
validation_mode=RETURN_ERRORS
on_error=CONTINUE;
Please try above and let me know if it works as expected.