[Wranglers] SQL pipe syntax is coming to Snowflake soon

[Ali, Saqib]

Here is a blogpost on streamlining SQL Workflows with Snowflake’s Pipe
Operator (->>):
https://medium.com/@vivekmcm1/streamlining-sql-workflows-with-snowflake-pipe-operator-c3adfe505ee1
<https://qosf.com>



On Wed, May 21, 2025 at 6:57 PM Ali, Saqib <docbook.xml at gmail.com> wrote:

> Looks like Snowflake is officially calling the "->>" a Flow Operator. See:
> https://docs.snowflake.com/en/sql-reference/operators-flow
>
> Flow Operator allows you to chain SQL statements together, where the
> results of one statement serve as the input to another statement. Very
> useful for applying SQL operations on the results of metadata queries, like
> SHOW and DESCRIBE. e.g.
>
> SHOW TABLES
> ->>
> SELECT
>   "name"
>   , "automatic_clustering"
>   , "cluster_by"
>   , "rows"
>   , "bytes"
> FROM $1 -- using $1 to reference the result from the previous query
> WHERE "bytes" > 500000;
>
> On Wed, May 21, 2025 at 10:44 AM Ali, Saqib <docbook.xml at gmail.com> wrote:
>
>> 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/20250627/ff59b11d/attachment.htm>