Making Snowflake Flow Better: Cleaner Queries

In a recent Snowflake release, a slick new operator quietly entered the scene: ->>. This little guy can make certain query workflows both more readable and more efficient—especially when you’re dealing with multi-step commands like SHOW, LIST, or DESCRIBE.

The Classic Way: RESULT_SCAN(LAST_QUERY_ID(-1))

You may already be familiar with the pattern below:

SHOW WAREHOUSES;

SELECT name, state, type, size
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID(-1)));

Effective? Sure.

Readable? Ehhh.

Memorable? Only if you’re a human compiler.

The LAST_QUERY_ID(-1) function references the previous query, but it’s not exactly intuitive. Worse—if you’re running dozens or hundreds of queries a day, it’s easy to lose track of what “the last query” even was.

The New Hotness: ->> Chaining

Enter Snowflake’s ->> operator. With this, you can chain a query directly to a SHOW, LIST, or DESCRIBE command like so:

SHOW WAREHOUSES
->> SELECT name, state, type, size FROM $1;

Same result. One less step. Way more readable.

How It Works

• ->> pipes the output of the command into the next query.
• $1, $2, etc., refer to the positional output of the prior statement.
• You must use the $n tokens in the FROM clause (you can’t use $1 to reference a column name or expression outside of it).

A Real-World Example: LISTing Files

Here’s a real use case from my workflow.

Previously, to extract file metadata from a staging location, I had to do this:

LIST @TheStage;

INSERT INTO FileMetaData -- A temp table I created to save this data for this purpose
SELECT $1, 
       $2, 
       TO_TIMESTAMP_TZ($4, ''DY, DD MON YYYY HH24:MI:SS-TZD'')
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID(-1)));

Now? This:

LIST @TheStage
->> SELECT "name", "size", 
      TO_TIMESTAMP_TZ("last_modified", ''DY, DD MON YYYY HH24:MI:SS-TZD'') 
FROM $1 WHERE "name" ilike $newfile

Clean, direct, and easier to understand at a glance.

Bonus Notes

• Yes, you can chain more than two statements—though I wouldn’t try to break records. Let’s just say the limit is comfortably high.
• ->> works with SHOW, LIST, DESCRIBE, and other similar metadata commands.
• If you’re seeing $1 outside of a ->> context, double-check where it’s used—remember, it’s only valid in the FROM clause.

TL;DR

Old way:

• Multi-step
• Verbose
• Error-prone

New way:

• Streamlined
• Cleaner syntax
• Easier debugging

As they say in both dry cleaning and SQL optimization: cleaner is always better.

Now, if you’ll excuse me, I’ve got reports to generate, ETL processes to orchestrate, and Snowflake & SQL Server environments to babysit—all while trying to stay in flow. 🧘‍♂️