[Wranglers] SQL pipe syntax is coming to Snowflake soon

[Ali, Saqib]

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/20250521/15de2684/attachment-0001.htm>