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
