[Wranglers] SQL pipe syntax is coming to Snowflake soon

[Ali, Saqib]

One thing I realized is that if you convert an existing [complex] query to
use Pipe Operator for readability, the pushdown optimization will not be
applied. Pushdown optimization improves performance by filtering out
unneeded rows as early as possible during query processing. Pushdown
optimization can also reduce memory consumption. However, due to how the
Pipe Operator works (i.e. a DAG pattern), Snowflake will not be able to
apply these types of Optimizations. So while you can use the Pipe Operator
to make the SQL more readable, you may be losing on the Pushdown
Optimization benefits in Snowflake. Just something to think about.



On Mon, May 19, 2025 at 10:32 PM Ali, Saqib <docbook.xml at gmail.com> wrote:

> Here is another example of Snowflake Pipe Operator (aka. Arrow Operator)
> using the Pseudo-SQL SHOW WAREHOUSES:
>
> show warehouses
> ->> select
>   "name"
>   , "state"
>   , "type"
>   , "size"
> from $1
> where "state" in ('STARTED', 'SUSPENDED');
>
>
> On Mon, May 19, 2025 at 10:10 PM Ali, Saqib <docbook.xml at gmail.com> wrote:
>
>> Snowflake's Pipe Operator (aka the Arrow Operator) is much more than the
>> Google Bigquery's Pipe (|>) operator. It is a way to write SQL based DAGs.
>> Here is an example:
>>
>> select
>>   c_mktsegment
>>   , count(*) as num_of_customer_per_segment
>> from SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER
>> group by c_mktsegment
>> ->> create or replace  table delete_me as select *
>> from $1 where num_of_customer_per_segment > 30000
>> -- note the use of $1 to refer to the output from the previous statement
>> ->> select * from delete_me;
>> ;
>>
>> Snowflake's Pipe Operator (aka the Arrow Operator) supports multiple SQL
>> statements. Statements can be SELECTs, DMLs and pseudo-SQL statements (like
>> SHOW, DESCRIBE etc. in Snowflake) and Stored Procedures etc. Statements can
>> refer to results from any of the previous statements using the $1,$2, $3
>> syntax.
>>
>> Here is example with using the pseudo-SQL statement "describe":
>>
>> describe table SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER
>> ->> select *
>> from $1
>> where "type" ilike '%varchar%' ;
>>
>>
>>
>> On Tue, May 13, 2025 at 10:27 PM Ali, Saqib <docbook.xml at gmail.com>
>> wrote:
>>
>>>  Maybe Snowflake should have called it an Arrow Operator instead of Pipe
>>> Operator:
>>>
>>> Bigquery and Databricks use "|>"
>>> Snowflake uses "->>"
>>>
>>> On Mon, May 12, 2025 at 6:01 AM Ali, Saqib <docbook.xml at gmail.com>
>>> wrote:
>>>
>>>> I am curious why Snowflake chose "->>" as the Pipe Operator when
>>>> Google Bigquery has already adopted "|>" as the Pipe Operator for SQL.
>>>> ¯\_(ツ)_/¯
>>>>
>>>>
>>>> On Sat, May 10, 2025 at 10:21 PM Ali, Saqib <docbook.xml at gmail.com>
>>>> wrote:
>>>> >
>>>> > Following in Google BigQuery's footsteps, Snowflake will be adding
>>>> support for the SQL Pipe Operator soon. The new pipe operator (->>) will
>>>> enable chaining of  SQL statements. In the chain of SQL statements, the
>>>> results of one statement can serve as the input to another statement. The
>>>> pipe operator can simplify the execution of dependent SQL statements and
>>>> improve the readability and flexibility of complex SQL operations.
>>>> >
>>>>
>>>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://analyticsengineering.net/pipermail/wranglers/attachments/20250521/ecb8f5c0/attachment.htm>