Snowflake's VALIDATION_MODE='RETRUN_ERROR' is not validating the file correctly

user_4290220 0
Viewed 562

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,user1@gmail.com,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 ?

snowflake-cloud-data-platform
1 Answers

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.

user_121789800
Related