Question 12 - ARA-C01 discussion

The SQL statement is a command for creating a pipe in Snowflake, which is an object that defines the COPY INTO <table> 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.

create pipe mypipe as

copy into mytable

from (

select * from @mystage

where col1 = 'A' and col2 > 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.

create pipe mypipe as

copy into mytable (col1, col2, col3)

from (

select col3, col1, col2 from @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.

create pipe mypipe as

copy into mytable (col1, col2)

from (

select col1, col2 from @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.

create pipe mypipe as

copy into mytable (col1, col2)

from (

select col1::date, col2 from @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.

create pipe mypipe as

copy into mytable (col1, col2, col3)

from (

select s.col1, s.col2, t.col3 from @mystage s

join othertable t on s.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 <table> 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.

create pipe mypipe as

copy into mytable

from (

select * from @mystage

on error abort

);

1: CREATE PIPE | Snowflake Documentation

2: Transforming Data During a Load | Snowflake Documentation