What transformations are supported in the below SQL statement?

The SQL statement is a command for creating a pipe in Snowflake, which is an object that defines the COPY INTO

statement used by Snowpipe to load data from an ingestion queue into tables1. The statement uses a subquery in the FROM clause to transform the data from the staged files before loading it into the table2.

The transformations supported in the subquery are as follows2:

Data can be filtered by an optional WHERE clause, which specifies a condition that must be satisfied by the rows returned by the subquery. For example:

SQLAI-generated code. Review and use carefully. More info on FAQ.

createpipe mypipeas

copyintomytable

from(

select*from@mystage

wherecol1='A'andcol2>10

);

Columns can be reordered, which means changing the order of the columns in the subquery to match the order of the columns in the target table. For example:

SQLAI-generated code. Review and use carefully. More info on FAQ.

createpipe mypipeas

copyintomytable (col1, col2, col3)

from(

selectcol3, col1, col2from@mystage

);

Columns can be omitted, which means excluding some columns from the subquery that are not needed in the target table. For example:

SQLAI-generated code. Review and use carefully. More info on FAQ.

createpipe mypipeas

copyintomytable (col1, col2)

from(

selectcol1, col2from@mystage

);

The other options are not supported in the subquery because2:

Type casts are not supported, which means changing the data type of a column in the subquery. For example, the following statement will cause an error:

SQLAI-generated code. Review and use carefully. More info on FAQ.

createpipe mypipeas

copyintomytable (col1, col2)

from(

selectcol1::date, col2from@mystage

);

Incoming data can not be joined with other tables, which means combining the data from the staged files with the data from another table in the subquery. For example, the following statement will cause an error:

SQLAI-generated code. Review and use carefully. More info on FAQ.

createpipe mypipeas

copyintomytable (col1, col2, col3)

from(

selects.col1, s.col2, t.col3from@mystages

joinothertable tons.col1=t.col1

);

The ON ERROR - ABORT statement command can not be used, which means aborting the entire load operation if any error occurs. This command can only be used in the COPY INTO

statement, not in the subquery. For example, the following statement will cause an error:

SQLAI-generated code. Review and use carefully. More info on FAQ.

createpipe mypipeas

copyintomytable

from(

select*from@mystage

onerror abort

);

1: CREATE PIPE | Snowflake Documentation

2: Transforming Data During a Load | Snowflake Documentation