Most "Snowflake is slow" tickets are not a Snowflake problem and not a warehouse-size problem. They are one of four things: the query scans far more micro-partitions than it needs, it spills to storage, it explodes a join, or it is running on a warehouse that is queueing behind other work. Each has a specific signature in the Query Profile and a specific fix, and only one of them is fixed by paying for more compute.
This tutorial is the workflow we use on client engagements: triage, diagnose, fix, verify.
Step 1 — Triage: find the queries that actually matter
Do not start with the query someone complained about. Start with the ones that consume the account.
SELECT query_parameterized_hash,
ANY_VALUE(query_text) AS sample_sql,
COUNT(*) AS executions,
SUM(total_elapsed_time)/1000/60 AS total_minutes,
AVG(total_elapsed_time)/1000 AS avg_secs,
SUM(bytes_scanned)/POW(1024,4) AS tb_scanned,
SUM(bytes_spilled_to_remote_storage)/POW(1024,3) AS gb_remote_spill
FROM snowflake.account_usage.query_history
WHERE start_time >= DATEADD(day, -7, CURRENT_TIMESTAMP())
AND execution_status = 'SUCCESS'
AND warehouse_name IS NOT NULL
GROUP BY 1
ORDER BY total_minutes DESC
LIMIT 25;
QUERY_PARAMETERIZED_HASH groups the same query shape across different literals, which is what you want: one dashboard tile running 4,000 times a day beats one nightly monster almost every time. Add QUERY_ATTRIBUTION_HISTORY if you want credits rather than minutes:
SELECT query_parameterized_hash, SUM(credits_attributed_compute) AS credits
FROM snowflake.account_usage.query_attribution_history
WHERE start_time >= DATEADD(day, -7, CURRENT_TIMESTAMP())
GROUP BY 1 ORDER BY credits DESC LIMIT 25;
Then separate execution time from waiting time. A query that takes 40 seconds of which 30 is queuing is a concurrency problem (multi-cluster, or move the workload), not a tuning problem:
SELECT query_id,
queued_overload_time/1000 AS queued_overload_s,
queued_provisioning_time/1000 AS queued_provisioning_s,
compilation_time/1000 AS compile_s,
execution_time/1000 AS exec_s
FROM snowflake.account_usage.query_history
WHERE query_parameterized_hash = 'PASTE_HASH_HERE'
AND start_time >= DATEADD(day, -2, CURRENT_TIMESTAMP())
ORDER BY start_time DESC
LIMIT 50;
High compilation_time on a short query is its own smell — usually a view stack a dozen layers deep, or hundreds of thousands of files in an external/Iceberg table.
Step 2 — Read the Query Profile like a pro
Open the query in Snowsight → Query Profile. Four numbers decide everything.
- Partitions scanned / partitions total. This is pruning. Scanning 100% of a 40,000-partition table to return 12 rows is the single most common cause of slow queries.
- Bytes spilled to local / remote storage. Local spill is tolerable; remote spill means the operation did not fit and is now paying network latency per row.
- Most expensive node. Usually a
TableScan, aJoin, or aSort/Aggregate. Its percentage tells you where to spend effort. - Rows out vs rows in on joins. If a join emits more rows than either input, you have a row explosion — a duplicated key or a missing predicate.
You can get most of this in SQL for automation:
SELECT * FROM TABLE(GET_QUERY_OPERATOR_STATS('01b1c2d3-0000-0000-0000-000000000000'));
That function returns per-operator statistics — pruning counts, spill, row counts — which is how you build a "regressed queries" monitor instead of clicking through the UI.
Step 3 — Fix pruning first
Snowflake prunes micro-partitions using per-partition min/max metadata on the columns in your WHERE clause. Pruning fails for predictable reasons:
Functions wrapped around the filtered column. The classic:
-- No pruning: TO_CHAR() hides order_ts from the metadata
WHERE TO_CHAR(order_ts, 'YYYY-MM') = '2026-03'
-- Prunes: a range on the raw column
WHERE order_ts >= '2026-03-01' AND order_ts < '2026-04-01'
The same applies to CAST(order_ts AS DATE) = ..., UPPER(country) = 'US', and arithmetic on the column. Move the function to the literal side, always.
Non-deterministic or opaque predicates. WHERE order_ts >= DATEADD(day, -7, CURRENT_TIMESTAMP()) prunes fine; a filter that depends on a correlated subquery or a UDF generally does not, because Snowflake cannot evaluate it before the scan. Materialize the bound into a variable or a small filtered CTE first.
Leading wildcards. LIKE '%1234' cannot prune and cannot use search optimization; LIKE 'ACME%' can.
Data that arrived in the wrong order. Natural clustering comes from load order. If you load by ingest_batch but filter by event_date, your min/max ranges overlap across every partition. Check it:
SELECT SYSTEM$CLUSTERING_INFORMATION('analytics.events', '(event_date)');
Read average_overlaps and average_depth in the result. Depth near 1 means excellent clustering on that key; a depth in the tens or hundreds on a large table means every query filtering that column reads nearly everything.
Step 4 — Clustering key or Search Optimization Service?
These are the two structural fixes, and they solve different query shapes. Choosing wrong is expensive because both cost credits continuously.
| Clustering key (Automatic Clustering) | Search Optimization Service | |
|---|---|---|
| Best for | Range and low-cardinality filters: dates, region, tenant, and joins on the clustered key | Point lookups on high-cardinality columns: ids, emails, UUIDs, VARIANT fields, LIKE prefix, geo |
| Rows returned | Any volume; helps big scans | Selective — a handful of rows out of billions |
| How it works | Physically reorders data so min/max metadata prunes | Builds and maintains a separate search access path |
| Cost | Serverless credits on re-clustering, driven by how much the table churns | Serverless credits to build and maintain, roughly proportional to table size and change rate |
| Limit | One clustering key per table (can be multi-column) | Configure per column or per whole table |
-- Range/date-shaped workload: cluster it. Order matters: low cardinality first.
ALTER TABLE analytics.events CLUSTER BY (event_date, tenant_id);
-- Watch the cost before you leave it on
SELECT table_name, SUM(credits_used) AS credits, SUM(num_bytes_reclustered)/POW(1024,4) AS tb_reclustered
FROM snowflake.account_usage.automatic_clustering_history
WHERE start_time >= DATEADD(day, -14, CURRENT_TIMESTAMP())
GROUP BY 1 ORDER BY credits DESC;
-- Needle-in-haystack workload: search optimization on specific columns
ALTER TABLE analytics.orders ADD SEARCH OPTIMIZATION ON EQUALITY(order_id, customer_email);
ALTER TABLE analytics.events ADD SEARCH OPTIMIZATION ON EQUALITY(payload:user_id);
SELECT * FROM TABLE(SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS('analytics.orders', 'EQUALITY(order_id)'));
SELECT table_name, SUM(credits_used) AS credits
FROM snowflake.account_usage.search_optimization_history
WHERE start_time >= DATEADD(day, -14, CURRENT_TIMESTAMP())
GROUP BY 1 ORDER BY credits DESC;
Rules of thumb from real engagements:
- Cluster large, append-mostly tables on the column your users filter by, not on the primary key. A clustering key on a unique id is nearly always waste.
- Do not cluster a table that is rewritten every night by a full-refresh job — you are paying to reorder data that is about to be deleted. Fix the load pattern (or use
INSERT ... ORDER BYon the write) instead. - Search optimization earns its keep for API-style lookups,
VARIANTkey filters, and support tooling that hits one row at a time. It does nothing for scans that return millions of rows. - Before either one, ask whether the answer is simply a materialized view or a Dynamic Table that pre-aggregates the dashboard query. Pre-computing 200 rows beats tuning a scan of 2 billion.
Also verify the cheap structural wins: replace a stack of nested views with one Dynamic Table where the same joins run dozens of times a day, and make sure large tables are not being read through a view that adds SELECT * on 300 columns. Snowflake is columnar; selecting only the columns you need is a real, measurable saving.
Step 5 — Kill spill and join explosion
Remote spill appears on sorts, large aggregations, and hash joins whose build side does not fit in memory. In priority order:
- Reduce the data: filter and project before the join, not after. Push aggregation down into a CTE.
- Fix the join order and cardinality — a
DISTINCTon the build side often collapses a huge dimension into something that fits. - Only then size up. A single step up doubles memory; if a query spills 400 GB remotely, no size will rescue a fundamentally bad plan.
Row explosion looks like a join node with far more output rows than input rows. Test the join key uniqueness directly:
SELECT customer_id, COUNT(*) AS dupes
FROM dim.customers
GROUP BY 1 HAVING COUNT(*) > 1
ORDER BY dupes DESC LIMIT 20;
Duplicated dimension rows from an SCD load with an open-ended valid_to is the number-one culprit we find. Fix the model; do not paper over it with SELECT DISTINCT, which converts a cardinality bug into a giant sort.
Other recurring offenders:
ORDER BYin a subquery or view that nobody consumes — free work, delete it.- Window functions partitioned by nothing across an entire fact table.
UNIONwhereUNION ALLwas meant — an implicit global de-duplication.- Row-by-row UDFs in a filter, which block pruning and vectorization. Rewrite as SQL or a vectorized Python UDF where possible.
Step 6 — Use the caches on purpose
- Result cache: identical query text, same role, unchanged data, within 24 hours → zero compute. Dashboards benefit enormously if the SQL is byte-identical, so avoid injecting
CURRENT_TIMESTAMP()or a random comment into generated queries. - Warehouse (local) cache: warms as a warehouse runs. This is the one real argument for
AUTO_SUSPENDabove 60 seconds on a heavily used BI warehouse — measurepercentage_scanned_from_cacheinQUERY_HISTORYbefore deciding. - Query Acceleration Service: for a warehouse sized entirely for its few worst scans, shrink the warehouse and let QAS burst those instead. Check
QUERY_ACCELERATION_ELIGIBLEfirst so you know the ceiling.
Step 7 — Verify, then keep it verified
Tuning without a before/after number is guesswork. Re-run the workload and compare the same metrics you triaged on:
SELECT DATE_TRUNC('day', start_time) AS day,
COUNT(*) AS executions,
AVG(execution_time)/1000 AS avg_exec_s,
AVG(partitions_scanned / NULLIF(partitions_total, 0)) AS avg_prune_ratio,
SUM(bytes_spilled_to_remote_storage)/POW(1024,3) AS gb_remote_spill
FROM snowflake.account_usage.query_history
WHERE query_parameterized_hash = 'PASTE_HASH_HERE'
AND start_time >= DATEADD(day, -14, CURRENT_TIMESTAMP())
GROUP BY 1 ORDER BY day;
A good outcome moves avg_prune_ratio toward zero and spill to zero. Turn that query into a scheduled task with an alert so a schema change or a new dashboard filter does not silently undo the work. Note that ACCOUNT_USAGE views have latency (typically up to ~45 minutes for QUERY_HISTORY); use the INFORMATION_SCHEMA table functions when you need immediate feedback during a tuning session.
A tuning checklist to hand to your team
- Rank by total minutes and credits per query hash, not by loudest complaint.
- Split queue time from execution time before touching SQL.
- Read partitions scanned/total first; fix predicate shape before buying anything.
- Cluster for ranges, search-optimize for point lookups, pre-aggregate when neither fits.
- Eliminate remote spill by reducing data, not by resizing first.
- Check join keys for duplicates whenever rows out exceed rows in.
- Keep query text stable so the result cache can work.
- Re-measure and monitor; treat a pruning regression as a bug.
PowderInsights runs Snowflake performance and cost reviews and embeds senior Snowflake architects and developers with client teams to fix the model, not just the warehouse size. Get in touch with your slowest workload and we will tell you which of the four problems it is.