Snowflake SQL Code Syntax Shortcuts

Snowflake SQL includes several syntax extensions, that can simplify your SQL code.

**SELECT * EXCLUDE / REPLACE**

Enables excluding a column list or transforming target columns when querying wide tables. This is useful on wide tables — stops you having to enumerate 30 columns to leave out or mask one. When combining these keywords, EXCLUDE must always be placed before REPLACE. Ref.

-- Drops sensitive columns while retrieving all others   
SELECT * EXCLUDE (ssn, credit_card_number)
FROM customers;

-- Replace a column value inline, keep everything else
SELECT * REPLACE (price * 1.1 AS price)
FROM products;

-- Combine both
SELECT * EXCLUDE (raw_json) REPLACE (upper(name) AS name)
FROM customers;

QUALIFY Clause

QUALIFY eliminates the most common subquery bloat pattern in analytics SQL. Filters window function results directly, eliminating the need to wrap code inside a nested Common Table Expression (CTE) or subquery in order to filter it. Ref.

Instead of writing:

SELECT 
    * 
FROM 
(
    SELECT 
        employee, 
        department, 
        salary,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    FROM 
        employees
) WHERE rn = 1;

use this:

SELECT 
   employee_id, 
   department, 
   salary,
   ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM 
    employees
QUALIFY rn = 1;

RESULT_SCAN – reuse cached query results

Query the result set of a previous statement by its query ID — no re-execution needed. This is a common idiom in Snowflake SQL. Ref.

-- Run an expensive query once
SELECT customer_id, SUM(revenue) AS ltv
FROM orders
GROUP BY customer_id;

-- Immediately reuse its result (free — uses cache)
SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE ltv > 10000
ORDER BY ltv DESC
LIMIT 100;

GROUP BY ALL

Eliminates the need to manually list out every non-aggregated column in your select clause. Ref.

SELECT country, city, department, SUM(sales) 
FROM sales_table 
GROUP BY ALL;

Note*: selecting and grouping by all columns is sometimes a code smell.

**SELECT * ILIKE**

Case-insensitive pattern matching. ILIKE ANY matches against multiple patterns in one expression. Ref.

-- Case-insensitive LIKE
SELECT * FROM products
WHERE category ILIKE '%widget%';

-- Match against multiple patterns at once
SELECT * FROM products
WHERE category ILIKE ANY ('%widget%', '%thingy%', '%gizmo%');

MIN_BY() and MAX_BY()

Finds the value of a column based on the minimum or maximum value of a completely different column without requiring a self-join. Ref.

-- Finds the name of the employee who has the absolute highest salary
SELECT MAX_BY(employee_name, salary) FROM employees;

ANY_VALUE Aggregate Function

Use ANY_VALUE() to pull an arbitrary value from a non-grouped column. Ref.

SELECT 
    user_id, 
    SUM(purchase_amount) AS total_spent,
    ANY_VALUE(latest_device_used) AS last_device
FROM 
    transactions
GROUP BY 
    user_id;

ARRAY_AGG and OBJECT_AGG

Can be used to simplify string concatenations or unnesting tables into multiple rows. Ref.

ARRAY_AGG: Groups multiple values into a single array per row

OBJECT_AGG: Pivots row-level attributes into a single JSON/Variant object

-- Returns a JSON object like: {"Electronics": 1500, "Clothing": 400}
SELECT 
    store_id, 
    OBJECT_AGG(category_name, total_sales) AS grp
FROM 
    sales_data
GROUP BY 
    store_id;

RATIO_TO_REPORT

Calculate each row’s proportion of a partition total without a self-join or subquery. Ref.

SELECT
    region,
    product,
    revenue,
    RATIO_TO_REPORT(revenue) OVER (PARTITION BY region) AS percent_of_region
FROM 
    sales;

Note*: The ORDER BY clause within the OVER clause is allowed in this function for syntactic consistency with other window functions but does not affect the calculation. Snowflake recommends not including the ORDER BY clause when using this function.:

SAMPLE / TABLESAMPLE – fast approximate queries

Run queries on a random sample of rows or blocks for fast exploratory analysis. Ref.

-- Row-level sampling (Bernoulli) — ~10% of rows
SELECT * FROM orders SAMPLE (10);

-- Block-level sampling (system) — faster on large tables
SELECT * FROM orders SAMPLE SYSTEM (5);

-- Repeatable sample with seed
SELECT * FROM orders SAMPLE (10) SEED (97);

Note*: For SYSTEM or BLOCK sampling, the sample might be biased, in particular for small tables.

ASOF JOIN — nearest-match join

Join tables on the nearest (as-of) timestamp without expensive range join workarounds. For every row in the left table, it pairs with exactly one row from the right table that has the closest preceding or following timestamp. Ref.

-- Get the exchange rate in effect at the time of each order
SELECT
    o.order_id,
    o.order_ts,
    o.amount_usd,
    r.rate,
    o.amount_usd * r.rate AS amount_eur
FROM 
    orders o
ASOF JOIN fx_rates r
    MATCH_CONDITION (o.order_ts >= r.rate_ts) ON o.currency = r.currency;

INSERT OVERWRITE

Truncate-and-reload a table (or partition) atomically in one statement. Ref.

-- Atomically replace entire table
INSERT OVERWRITE INTO daily_summary
SELECT 
    date_trunc('day', order_ts) AS order_date,
    SUM(revenue) AS total_revenue
FROM 
    orders
GROUP BY 1;

CREATE OR REPLACE + SWAP

Atomic table replacement: SWAP WITH lets you swap two tables instantly (blue/green deployments). Ref.

-- Rebuild without downtime
CREATE OR REPLACE TABLE orders_new AS
SELECT * FROM orders_staging WHERE is_valid = true;

-- Atomic swap — zero downtime
ALTER TABLE orders_new SWAP WITH orders;

-- Drop the old table (now named orders_new after swap)
DROP TABLE orders_new;

CLONE – zero-copy clone

Instantly clone tables, schemas, or entire databases. Uses no extra storage until data diverges. Ref.

-- Clone a table (instant, no storage cost)
CREATE TABLE orders_backup CLONE orders;

-- Clone to a point in time (time travel)
CREATE TABLE orders_yesterday CLONE orders
    AT (TIMESTAMP => DATEADD('day', -1, CURRENT_TIMESTAMP()));

-- Clone an entire schema for a dev environment
CREATE SCHEMA dev_schema CLONE prod_schema;

-- Clone an entire database
CREATE DATABASE mytestdb_dev_clone CLONE mytestdb_prod;

DIV0 and DIV0NULL

Performs safe division where divide-by-zero errors return 0 or NULL instead of throwing an error. Ref.

SELECT DIV0(10, 0); -- Returns 0

COUNT_IF

Returns the number of records that satisfy a condition or NULL if no records satisfy the condition. Ref.

SELECT 
    COUNT_IF(status = 'completed') AS num_orders_completed
FROM 
    orders

Rather than:

SELECT 
    SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS num_orders_completed
FROM 
    orders

SELECT * EXCLUDE / REPLACE