Slow Snowflake queries don't have to be a mystery. Learn how to read Query Profiles, diagnose spill to local and remote storage, and apply clustering keys to dramatically improve performance on large analytics tables.
It's 9 AM on a Monday. Your VP of Sales is waiting on a revenue dashboard that's supposed to load in under five seconds. Instead, it's been spinning for two minutes. You pull up Snowflake and run the underlying query manually — it finishes in 90 seconds. Last week, the same query took 12 seconds. Nothing changed in the SQL. What happened?
This scenario plays out constantly in analytics teams. Snowflake abstracts away an enormous amount of complexity — you don't manage indexes, vacuum operations, or storage nodes the way you do in traditional databases. But that abstraction comes with a trade-off: when things go wrong, the symptoms aren't obvious. Queries slow down, costs balloon, and without knowing where to look, you're guessing. The good news is Snowflake gives you a genuinely powerful diagnostic tool called the Query Profile, and once you know how to read it, you can go from confused to confident in minutes.
By the end of this lesson, you'll be able to open a Query Profile, interpret what it's telling you, spot the specific warning signs that indicate your query is in trouble, and apply clustering key strategies that make a material difference for large analytics tables. These skills directly reduce both query latency and compute costs — two things your whole organization cares about.
What you'll learn:
Before we can read a Query Profile, we need a mental model of what Snowflake is actually doing when you hit "Run."
Snowflake's compute layer consists of virtual warehouses — clusters of nodes that process your query in parallel. When you run a query, Snowflake compiles it into an execution plan: a tree of operations that describes how to scan tables, apply filters, join datasets, and aggregate results. Each node in that tree is called an operator.
Here's the critical concept: Snowflake stores table data in micro-partitions — small compressed chunks of roughly 50–500MB each, stored in columnar format in cloud object storage (S3, Azure Blob, or GCS depending on your cloud). When you query a table, Snowflake scans the micro-partitions it needs and pulls them into the warehouse's local SSD storage for processing.
This architecture is why understanding storage and memory behavior is so important for performance. If your query needs more data than fits in the warehouse's local memory and SSD cache, it has to overflow to slower storage — a condition called spill, which we'll cover in depth shortly.
Understanding the physical layout of micro-partitions is foundational here. If you want to go deeper on how columnar storage and partitioning actually work before continuing, How Data Warehouses Actually Store Data: Columnar Storage, Partitioning, and Clustering Explained covers the internals in full.
Note
Snowflake automatically handles query compilation, parallelism, and result caching. Many queries benefit from the result cache (returning instantly if the exact same query was run recently) or the local disk cache (reusing micro-partitions already loaded into warehouse storage). The Query Profile will tell you which cache tier your query hit.
The Query Profile lives in the Snowflake web UI (called Snowsight). Here's exactly how to get there.
After running a query in a worksheet, look at the bottom of the screen where your query results appear. You'll see a row of tabs: Results, Query Details, and Query Profile. Click Query Profile.
Alternatively, if you want to investigate a query that ran in the past: navigate to Activity in the left sidebar, then click Query History. Find the query you're interested in by filtering on time range, user, warehouse, or query text. Click the query ID link to open its details page, then click the Query Profile tab.
The Query Profile view shows you a graph of operator nodes connected by arrows. The arrows represent data flow — data moves from bottom to top, so the leaf nodes at the bottom are your table scans, and the root node at the top is where your final result comes out. Each node shows the time it spent executing, expressed both in raw milliseconds and as a percentage of total execution time.
Tip
The node with the highest percentage of total time is your bottleneck. Start there, not at the top. Snowflake highlights the most expensive nodes in orange or red to make this easy to spot at a glance.
On the right side of the Query Profile screen, you'll see a Statistics panel that shows overall metrics: total execution time, bytes scanned, bytes written, partitions scanned vs. total partitions, and — critically — bytes spilled to local and remote storage. Keep that panel in your peripheral vision as we work through examples.
Each node in the execution plan represents a specific operation. Here are the ones you'll encounter most often in analytics workloads and what they tell you.
TableScan — This is where Snowflake reads your table from storage. The key metric here is "Partitions Scanned" vs. "Partitions Total." If you scanned 12,000 out of 12,000 partitions on a large table, you have no partition pruning — your query is reading everything. If you scanned 400 out of 12,000, Snowflake is efficiently skipping irrelevant data.
Filter — Applies a WHERE clause predicate. Watch the row count before and after: a good filter dramatically reduces row count. If a filter is barely reducing rows, that predicate isn't very selective.
Join — This is often where queries get expensive. Snowflake supports several join types (hash join, merge join, nested loop). Hash joins are the most common. The key cost here is how much data each side of the join produces and whether that data fits in memory.
Aggregate — Handles GROUP BY operations. This node can be expensive on high-cardinality columns (columns with many distinct values), because it needs to track many groups simultaneously.
Sort — Triggered by ORDER BY. Sorting requires all rows to be materialized before any output is produced, which can consume substantial memory.
ExchangeAsync / Broadcast — These nodes represent data being redistributed across the warehouse's parallel compute nodes. When you see a large number of bytes flowing through an Exchange node, it means Snowflake had to shuffle data across nodes to co-locate matching rows for a join or aggregation. This is normal, but excessive shuffling slows things down.
Let's look at a realistic example. Suppose you have a fact table fct_orders with 500 million rows and a dimension table dim_customers with 2 million rows, and you're running this query:
SELECT
c.country,
c.customer_segment,
DATE_TRUNC('month', o.order_date) AS order_month,
SUM(o.order_amount_usd) AS total_revenue,
COUNT(DISTINCT o.order_id) AS order_count
FROM fct_orders o
JOIN dim_customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2023-01-01'
GROUP BY 1, 2, 3
ORDER BY 3 DESC;
A healthy Query Profile for this query would show: TableScan on fct_orders with strong partition pruning (maybe 20% of partitions scanned due to the date filter), a small TableScan on dim_customers, an Exchange node moving data around for the join, a compact Aggregate node, and a Sort at the top. Zero bytes spilled.
An unhealthy profile might show: TableScan on fct_orders scanning 100% of partitions, a large Exchange node, and — the red flag of red flags — bytes spilled to local and remote storage.
Spill is what happens when a Snowflake operation runs out of memory on a compute node and has to write intermediate data to disk. There are two tiers of spill, and both hurt:
You'll see these in the Statistics panel on the right side of the Query Profile, labeled "Bytes Spilled to Local Storage" and "Bytes Spilled to Remote Storage." Any number above zero deserves attention. Numbers in the gigabytes or terabytes are a crisis.
Warning
Remote spill is not just slow — it's expensive. You're paying for compute time while the warehouse grinds through cloud storage I/O. A query that spills 500GB to remote storage on an X-Large warehouse can easily cost more than it would on a 2X-Large warehouse that completes the same query without spilling at all. Bigger warehouse, faster query, lower cost.
The most common causes are:
Warehouse is too small for the data volume. If you're running complex multi-table joins on hundreds of millions of rows on a Small warehouse, you're going to spill. Snowflake warehouses scale in powers of two (Small → Medium → Large → X-Large → 2X-Large, etc.), and each size doubles the available memory per node.
Cartesian explosion from a bad join. If your join condition is wrong or too loose, you can accidentally produce a result set far larger than either input table — consuming enormous memory in the process.
Aggregation on very high cardinality data. A COUNT(DISTINCT user_session_id) on a 1-billion-row table with 900 million distinct sessions requires tracking an enormous number of intermediate values.
Multiple large sorts in sequence. An ORDER BY on a wide result set can spill if the data doesn't fit.
Option 1: Scale up the warehouse. This is the blunt instrument — but it works. If a query spills 50GB to local storage on a Large warehouse, try it on an X-Large. The doubled memory may eliminate spill entirely. If it now completes in half the time, the cost equation often still favors the larger warehouse.
Option 2: Fix the query logic. Look at the join producing the most data. Is your join key correct? Run this quick diagnostic — count the rows on each side of the join before and after joining:
-- Check for join explosion: compare input vs. output row counts
-- If the output is vastly larger than the larger input, something is wrong
SELECT COUNT(*) FROM fct_orders WHERE order_date >= '2023-01-01';
-- Say this returns 50 million
SELECT COUNT(*) FROM fct_orders o
JOIN dim_customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2023-01-01';
-- If this returns 500 million, your join is multiplying rows — investigate duplicates in dim_customers
Option 3: Pre-aggregate before joining. If you're joining a huge fact table to a dimension and then aggregating, consider aggregating the fact table first in a CTE, then joining the smaller result:
-- Instead of joining 500M rows then aggregating...
-- Pre-aggregate the fact table first
WITH monthly_orders AS (
SELECT
customer_id,
DATE_TRUNC('month', order_date) AS order_month,
SUM(order_amount_usd) AS monthly_revenue,
COUNT(DISTINCT order_id) AS monthly_order_count
FROM fct_orders
WHERE order_date >= '2023-01-01'
GROUP BY 1, 2
)
SELECT
c.country,
c.customer_segment,
mo.order_month,
SUM(mo.monthly_revenue) AS total_revenue,
SUM(mo.monthly_order_count) AS order_count
FROM monthly_orders mo
JOIN dim_customers c ON mo.customer_id = c.customer_id
GROUP BY 1, 2, 3
ORDER BY 3 DESC;
This can reduce the data flowing into the join from 500 million rows to something much smaller — fewer intermediate rows, less memory pressure, less or no spill.
Key insight
If you're running dbt models that produce spill, the right fix is usually a combination of materialization strategy and query restructuring. The article on Profiling and Optimizing Slow dbt Models covers how to approach this from the dbt side.
Now let's talk about the other major lever for Snowflake performance: clustering keys.
Remember that Snowflake stores data in micro-partitions. Each micro-partition stores metadata about the range of values it contains for each column — the minimum value, maximum value, and a bloom filter. When you filter on a column in a WHERE clause, Snowflake uses this metadata to prune (skip) micro-partitions that can't possibly contain matching rows.
This pruning is automatic and free. But it only works well when the column you're filtering on is correlated with how data is physically organized across micro-partitions. If your 500-million-row fct_orders table was loaded in timestamp order, then filtering on order_date will prune beautifully — recent dates are in recent micro-partitions, and old dates are in old ones. The min/max metadata on each partition will tell Snowflake exactly which partitions to skip.
But what if your analysts mostly filter on customer_segment or product_category? Those columns were probably scattered randomly across micro-partitions during ingestion. A filter on customer_segment = 'Enterprise' might appear in every single micro-partition, so Snowflake has to scan all of them anyway.
This is the problem that clustering keys solve. A clustering key is an instruction to Snowflake to reorganize the micro-partitions of a table so that rows with similar values in the specified column(s) are stored together. After reclustering, a filter on the clustering key column will produce excellent partition pruning.
Note
Snowflake offers Automatic Clustering — a background service that continuously maintains the clustering of a table. You define the clustering key, and Snowflake handles the physical reorganization automatically as new data arrives. This is different from manually running ALTER TABLE ... RECLUSTER.
Before you add a clustering key, check whether your table actually has a pruning problem. In the Query Profile, look at the TableScan node for your large table. If "Partitions Scanned" is close to "Partitions Total," you have a pruning problem.
You can also query Snowflake's system functions to understand current clustering quality:
-- Check the clustering depth of a table on a given column
-- Lower average_depth = better clustering = better pruning
SELECT SYSTEM$CLUSTERING_INFORMATION('fct_orders', '(order_date)');
The output includes average_depth — a number representing how many micro-partitions, on average, contain a given value of the clustering key. An average_depth of 1.0 means perfect clustering (every value lives in exactly one partition). An average_depth of 100 means values are scattered across ~100 partitions on average, which will produce poor pruning.
Picking a clustering key is a judgment call. Here's how to think about it:
Cluster on columns in your most frequent WHERE filters. If 80% of your queries filter on order_date, that's your clustering key. If queries filter on both order_date and region, consider a compound key: (order_date, region).
-- Add automatic clustering on a date column
ALTER TABLE fct_orders CLUSTER BY (order_date);
-- Compound clustering key: date first, then region
ALTER TABLE fct_orders CLUSTER BY (order_date, region);
Tip
Put the lower-cardinality column second in a compound clustering key. Put the column you filter on most often first. CLUSTER BY (order_date, region) is typically better than CLUSTER BY (region, order_date) if most queries include a date range filter.
High cardinality columns work well as clustering keys. A column with many distinct values (like order_date or customer_id) creates finer-grained partitions with better pruning. Very low cardinality columns (like a boolean is_active with only two values) create poor clustering because every partition will contain both TRUE and FALSE rows.
Don't cluster on columns used only for display, not filtering. Clustering a table on order_description (a free-text column you SELECT but never filter on) wastes money and provides no benefit.
Clustering costs money. Automatic clustering uses compute credits to reorganize your data continuously. For tables under a few hundred million rows, it may not be worth it. For tables with billions of rows and predictable query patterns, it typically delivers a strong return. This is a real cost management decision — the article on Cost Management in Cloud Data Platforms covers how to track and govern these costs.
Clustering is not always the answer. Skip it when:
Here's a repeatable process you can use on any slow query:
Step 1: Run the query and open the Query Profile. Note total execution time and the percentages on each node.
Step 2: Check the Statistics panel. Record bytes spilled to local storage and remote storage. Record partitions scanned vs. total.
Step 3: Find the most expensive node. Go to the orange/red node. What is it? A TableScan with 100% partition coverage? A Join with enormous row counts? An Aggregate with high memory usage?
Step 4: Diagnose the root cause. Use the decision tree:
APPROX_COUNT_DISTINCTStep 5: Apply one fix at a time. Change one variable — either the warehouse size, the query structure, or the clustering key — then re-run and compare profiles. Changing multiple things at once makes it impossible to know what actually helped.
Step 6: Monitor the result. Check partitions scanned and spill metrics again. Did the expensive node's percentage drop?
This workflow pairs naturally with Incremental Models at Scale, which covers how to design dbt models that naturally take advantage of partition pruning — particularly useful when you're the person building the tables your analysts are querying.
Let's put this into practice. If you have a Snowflake account with the SNOWFLAKE_SAMPLE_DATA database (enabled by default on most accounts), you can run these exercises directly.
Exercise 1: Observe partition pruning
-- Query 1: No filter — full scan
SELECT COUNT(*), SUM(l_extendedprice)
FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000.LINEITEM;
-- Query 2: Date range filter
SELECT COUNT(*), SUM(l_extendedprice)
FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000.LINEITEM
WHERE l_shipdate BETWEEN '1994-01-01' AND '1994-12-31';
Run both queries. Open the Query Profile for each. Compare "Partitions Scanned" vs. "Partitions Total" in the TableScan node. You should see dramatically fewer partitions scanned in Query 2 — this is partition pruning in action.
Exercise 2: Spot the expensive node
SELECT
l_returnflag,
l_linestatus,
SUM(l_quantity) AS sum_qty,
SUM(l_extendedprice) AS sum_base_price,
COUNT(*) AS count_order
FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000.LINEITEM
WHERE l_shipdate <= '1998-09-01'
GROUP BY 1, 2
ORDER BY 1, 2;
Run this and open the Query Profile. Identify the most expensive node. Is it the scan? The aggregate? Is there any spill? Make note of what you see — the LINEITEM table at SF1000 scale is large enough to surface interesting behavior depending on your warehouse size.
Exercise 3: Check clustering information
-- Check current clustering depth on the shipdate column
SELECT SYSTEM$CLUSTERING_INFORMATION(
'SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000.LINEITEM',
'(l_shipdate)'
);
Read the JSON output. Note the average_depth value. Does this explain the pruning behavior you saw in Exercise 1?
"The Query Profile looks fine, but the query is still slow."
Check whether the query is hitting the result cache. If it returns instantly after a re-run, that's the cache. To measure true execution time, use ALTER SESSION SET USE_CACHED_RESULT = FALSE; temporarily, then re-run. Reset it to TRUE when done.
"I added a clustering key but partition pruning didn't improve."
Clustering is applied asynchronously. Depending on the table size, it may take minutes to hours before Automatic Clustering has reorganized your micro-partitions. Check SYSTEM$CLUSTERING_INFORMATION again after some time has passed. Also verify that your query's WHERE clause actually uses the clustering key column — a filter on a different column won't benefit.
"My query spills even on a large warehouse." Scaling up the warehouse may not fully fix a logical problem. If you have a join that's exploding from 50 million to 2 billion rows due to a missing join condition or duplicates in a dimension table, adding more memory just delays the inevitable. Fix the query logic first.
"The JOIN node shows a very long time but the row counts look reasonable." This can indicate a skew problem — data is unevenly distributed across compute nodes, so some nodes finish quickly while others are overloaded. Look at the node details for a "Rows" breakdown across compute nodes. If one node processed 10x more rows than others, your join key may have highly skewed distribution. This is a more advanced topic, but the first mitigation is to ensure your join key is as uniform as possible.
"I can't find the Query Profile for a query that ran yesterday." Query history is retained based on your Snowflake account settings — typically 14 days. Access it through Activity → Query History in Snowsight. If you need longer retention for performance monitoring, consider logging query metadata to a table using Snowflake's QUERY_HISTORY view in INFORMATION_SCHEMA.
You now have a complete workflow for diagnosing and fixing slow Snowflake queries. Let's recap what you've learned:
For your next steps, consider going deeper in a few directions. If you're building the models that people are querying, Building a Multi-Layer dbt Project with Staging, Intermediate, and Mart Layers will help you design a project structure that makes performance optimization easier and more systematic. If you're responsible for the ongoing health of your data platform — not just individual queries — Automating Data Freshness SLAs covers how to build monitoring that catches degradation before your VP of Sales notices it first.
Performance tuning is a skill that compounds. Every slow query you diagnose teaches you something about your data's shape and your team's query patterns. After a few months of this, you'll start anticipating problems before they happen — which is what separates a good analytics engineer from a great one.