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?

Answer 1

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