Most Snowflake performance problems I've been called in to fix aren't query problems — they're warehouse-sizing problems or clustering problems wearing a query's clothes. Snowflake's separation of storage and compute means the levers are different from a traditional database: you rarely add an index, you almost never rewrite a join by hand, but you do choose a clustering key, size a warehouse, and read a query profile that tells you exactly where the time went. This is the tuning workflow I actually use, in the order I use it.
Read the query profile before touching anything
Snowflake's query profile (in Snowsight, under the query history detail view) breaks a query into operator nodes with time and bytes attributed to each one. Before changing clustering keys or warehouse size, look at the profile for two specific numbers: percentage of time in "Table Scan" versus everything else, and "bytes scanned" versus "bytes scanned from cache." A query dominated by table scan time with low pruning efficiency is a clustering problem. A query with a small scan but a huge "sort" or "aggregate" node relative to the warehouse size is a compute-sizing problem, not a data-layout problem. Tuning the wrong lever is the single most common waste of time I see — people resize a warehouse to fix a query that's actually scanning 40x more micro-partitions than it needs to.
Clustering keys and partition pruning
Snowflake automatically partitions data into micro-partitions (roughly 50-500MB compressed) and stores min/max metadata per column per partition. A query with a selective filter can skip partitions entirely if the filtered column is well-clustered — this is "pruning." Tables loaded in roughly chronological order are naturally well-clustered on a timestamp column for free. Tables that get updated out of order, or queried heavily on a non-ingestion column (customer_id, region), often need an explicit clustering key.
-- Check current clustering health
SELECT SYSTEM$CLUSTERING_INFORMATION('sales.fact_orders', '(customer_id)');
-- Add a clustering key (Snowflake reclusters in the background)
ALTER TABLE sales.fact_orders CLUSTER BY (customer_id, order_date);
-- Multi-column keys should go low-to-high cardinality, coarsest first
ALTER TABLE events.raw_events CLUSTER BY (DATE_TRUNC('day', event_ts), tenant_id);Automatic reclustering runs as a background service and consumes credits separately from your warehouses. Only add a clustering key on tables large enough (multi-TB, heavily filtered) that the pruning win outweighs the ongoing reclustering cost — on a small table it's pure overhead.
Right-sizing virtual warehouses
A Snowflake virtual warehouse is a cluster of compute resources billed per-second while running, in sizes from X-Small to 6X-Large, each doubling the compute (and cost) of the last. Bigger is not always faster: a warehouse only helps a single query if that query can actually use the extra parallelism — a query scanning 200MB doesn't get faster on a 4X-Large than an X-Small, it just burns more credits per second doing the same work. Warehouse sizing is mostly about matching concurrency needs (how many queries run at once) rather than making one query faster. Scale up for big scans and heavy joins over large tables; scale out (multi-cluster warehouses) for concurrency — many users hitting the same warehouse at once — not for raw query speed.
SHOW WAREHOUSES;
-- Find warehouses with high queuing (undersized) or low utilization (oversized)
SELECT
warehouse_name,
AVG(avg_running) AS avg_concurrent_queries,
AVG(avg_queued_load) AS avg_queued,
SUM(credits_used) AS total_credits
FROM snowflake.account_usage.warehouse_load_history
WHERE start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP())
GROUP BY warehouse_name
ORDER BY total_credits DESC;
ALTER WAREHOUSE etl_wh SET
WAREHOUSE_SIZE = 'MEDIUM'
AUTO_SUSPEND = 60
AUTO_RESUME = TRUE;Result caching and when it actually helps
Snowflake keeps a result cache for 24 hours per query, keyed on the exact SQL text and the state of the underlying data — an identical query re-run with no changes to the source tables returns instantly with zero compute cost. This is real, but it's also the thing people over-credit when a dashboard "feels fast": the cache only helps for literally repeated queries, not for similar queries with different filter values or for queries against tables that changed since the last run. For dashboards with parameterized filters, the win comes from warehouse-level data caching (the local SSD cache on a running warehouse) rather than the account-level result cache, which is why keeping a warehouse warm (not auto-suspending too aggressively) can matter more than the result cache for BI workloads.
A low AUTO_SUSPEND (say, 60 seconds) saves credits but cold-starts the warehouse cache on every burst of queries, losing the local disk cache benefit. For a warehouse serving a BI tool with steady daytime traffic, a longer suspend window (5-10 minutes) often costs less in wasted compute than it saves in cache misses — measure both, don't assume the aggressive setting is cheaper.
Query rewrites that actually move the needle
Once clustering and warehouse size are right, the remaining wins are mostly about reducing bytes scanned and avoiding unnecessary re-computation: filter as early as possible (predicate pushdown works, but only if the filter is on a clustered or naturally-ordered column), avoid `SELECT *` on wide tables when you need three columns, and use materialized views or a scheduled task for aggregations that get queried dozens of times a day rather than recomputing the same GROUP BY on every dashboard refresh.
Wrapping up
Snowflake tuning has a clear order of operations: check the query profile to find out whether you have a scan problem or a compute problem, fix clustering if pruning is bad, size the warehouse for the concurrency and data volume you actually have, and only then look at caching and query rewrites. Skipping straight to "just make the warehouse bigger" is the expensive default, and it's the one Snowflake's per-second billing will happily let you get away with for months before the bill makes the mistake obvious.
Independent software engineer in Nairobi specialising in Acumatica customisations, Laravel backends, and tax fiscalisation integrations across East and Southern Africa.