How to Tune Queries in Snowflake

How do I tune slow Snowflake queries?

Query tuning in Snowflake means adjusting SQL, warehouse, and data-model settings to reduce runtime and cost.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why is my Snowflake query slow?

Slow queries usually scan too much data, spill to local disk, or wait on a small warehouse. Start by checking QUERY_HISTORY for execution time, bytes scanned, and percentage of time spent on compilation, execution, and queuing.

How do I get the execution plan?

Run EXPLAIN USING TEXT <your_query>. Focus on the SCAN nodes. High “partitions scanned” or “bytes scanned” values signal poor pruning. Large “output rows” in JOIN nodes show the join order needs work.

Which warehouse settings matter most?

Warehouse size sets CPU and memory. Increase from X-SMALL to MEDIUM for complex joins. Auto-Suspend lowers costs between runs. Auto-Resume wakes the warehouse instantly, so leave it on to avoid queue waits.

How can I minimize data scanned?

Filter early with selective WHERE clauses on partition columns like order_date. Select only needed columns. Join on surrogate keys (customer_id) instead of wide text columns.

Can clustering keys help wide tables?

Yes. Add a clustering key on Orders(order_date) or a multi-column key on OrderItems(order_id, product_id). Recluster heavy-write tables during low-traffic windows.

When should I rely on the result cache?

If dashboards rerun the same query often, result caching returns data in milliseconds. Use fully qualified table names and identical SQL to hit the cache. Any DML on referenced tables invalidates the cache.

What are best practices for ecommerce schemas?

Star-schema designs keep fact tables like OrderItems narrow and dimension tables like Products wide. Declare PRIMARY KEY constraints (even though Snowflake doesn’t enforce them) to improve planner statistics.

How do I analyze query cost?

Multiply bytes scanned by your storage cost per TB to estimate scan cost. Warehouse cost equals execution time × credits per hour for the warehouse size. Reducing scan size usually saves more than downsizing warehouses.

What are fast-action tuning steps?

1) Add filters and LIMIT early. 2) Create MATERIALIZED VIEWs for common aggregates. 3) Batch INSERTs to avoid micro-partitions with tiny files. 4) Scale warehouses temporarily for backfills, then scale down.

Why How to Tune Queries in Snowflake is important

How to Tune Queries in Snowflake Example Usage


-- Tuned version of a customer spend report
WITH recent_orders AS (
    SELECT id, customer_id
    FROM Orders
    WHERE order_date >= DATEADD('day', -30, CURRENT_DATE())
),
spend AS (
    SELECT ro.customer_id,
           SUM(oi.quantity * p.price) AS total_spend
    FROM recent_orders ro
    JOIN OrderItems oi ON oi.order_id = ro.id
    JOIN Products p ON p.id = oi.product_id
    GROUP BY ro.customer_id
)
SELECT c.name, s.total_spend
FROM spend s
JOIN Customers c ON c.id = s.customer_id
ORDER BY s.total_spend DESC
LIMIT 20;

How to Tune Queries in Snowflake Syntax


-- 1. Get query profile
SELECT *
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(END_TIME_RANGE_START=>DATEADD('minute',-10,CURRENT_TIMESTAMP())));

-- 2. Explain a slow join
EXPLAIN USING TEXT
SELECT c.name, SUM(oi.quantity * p.price) AS spend
FROM Customers c
JOIN Orders o   ON o.customer_id = c.id
JOIN OrderItems oi ON oi.order_id = o.id
JOIN Products p ON p.id = oi.product_id
WHERE o.order_date BETWEEN '2023-10-01' AND '2023-10-07'
GROUP BY c.name
ORDER BY spend DESC;

-- 3. Increase warehouse just for this session
ALTER SESSION SET WAREHOUSE = my_wh_medium;

-- 4. Add a clustering key
ALTER TABLE Orders CLUSTER BY (order_date);

-- 5. Create a materialized view
CREATE MATERIALIZED VIEW mv_customer_spend AS
SELECT c.id, SUM(oi.quantity * p.price) AS spend
FROM Customers c
JOIN Orders o   ON o.customer_id = c.id
JOIN OrderItems oi ON oi.order_id = o.id
JOIN Products p ON p.id = oi.product_id
GROUP BY c.id;

Common Mistakes

Running large reports on an X-SMALL warehouse. This causes spills and long runtimes. Fix by scaling the warehouse up temporarily or creating a larger dedicated warehouse for heavy jobs.
Ignoring prune-friendly predicates. A filter like WHERE DATE(order_date) = '2023-10-01' prevents partition pruning because it wraps the column in a function. Use WHERE order_date::DATE = '2023-10-01' instead.