Snowflake Validate Option does not return Failed records When using TO_DATE function in Copy Command

Question

I have used Copy Command as below:

copy into test2 from @%test2 file_format = (format_name = 'CSV') on_error = 'CONTINUE';

My file contains some Character data in Number field ( for all records) so Copy result is LOAD_FAILED and I can get fail records using below query ( in this case all records are failed records):

select * from table(validate("TEST2", job_id=>'Corresponding JOB ID'));

I also tried giving invalid dates and still got all bad records from above query.

Now I tried Copy command as below: copy into test2(test1,test2) from (select $1,to_date($2,'YYYYDDD') from @%test2) file_format = (format_name = 'CSV') on_error = 'CONTINUE';

Copy result was again LOAD_FAILED but I do not get any fail record from below query now: select * from table(validate("TEST2", job_id=>'Corresponding JOB ID'));

does this work only for regular copy without any conversion function in Copy or there is any other reason?

Adding One more example after seeing response from Mike below: File data: 1,2018-1-34 2,2/3/2016 3,2020124

table-> create table test2(test1 number,test2 date)

copy into test2(test1,test2) from (select $1,to_date($2,'YYYYDD') from @%test2) file_format = (format_name = 'CSV') on_error = 'CONTINUE';

first and third record are available in Validate query. Only the second record in not present in this case. its weird. ( all three records failed in copy) .

As Mike said below in comments Validate does not work with transform data in copy but why does it provide two records in that case. it should wither not provide anything at all or all of them?

Is this correct or do you have a typo in this post? 'YYYYDDD' — Mike Walton
this is correct, I was trying Julian Date 2018134. I know this is invalid syntax, snowflake does not have DDD but I wanted to understand why error records are not in validate option. I even tried with YYYYDD as well still same result ( just to make sure DDD is not the cause of not showing fail records in Validate query), still no records in validate query . ( note my data is like 2020134 etc, this is why copy fails with YYYYDD). — PythonDeveloper

Mike Walton Mike Walton · Accepted Answer · 2020-09-15T17:08:32

Per the documentation, since you are transforming the data during the COPY INTO, the VALIDATE function will no longer work:

This function does not support COPY INTO statements that transform data during a load.

https://docs.snowflake.com/en/sql-reference/functions/validate.html#usage-notes

Snowflake Validate Option does not return Failed records When using TO_DATE function in Copy Command

1 Answers