UDF not allowed in SnowPipe Definition

Question

I have created a Snowflake Java UDF function snowflake_email_validation and trying to call that function from the snowpipe.

It is throwing the error as:

User-defined function 'SNOWFLAKE_EMAIL_VALIDATION' with language 'JAVA' is not allowed in pipe definition" .

This is my command

create or replace pipe emailpipe auto_ingest=true as 
  copy into TGT_EMAIL_TABLE(EMAIL, IS_VALID) 
  from (select $1,snowflake_email_validation($1) from @s3_stage) 
  on_error=continue;

Please help to fix this

Answer 1

COPY INTO has several limitations like this, and Snowpipe can work only with COPY INTO.

Try as an alternative the different query below. It can be eventually encapsulated in a stored procedure, and called periodically on a schedule by a task.

create or replace table TGT_EMAIL_TABLE(EMAIL, IS_VALID) as
  select $1, snowflake_email_validation($1)
  from @s3_stage