Snowflake Performance Tuning and Caching
A query that runs in 2 seconds serves a dashboard user instantly. The same query taking 4 minutes frustrates everyone and burns credits unnecessarily. Query performance tuning is the discipline of identifying why a query is slow and applying the right fix. Snowflake provides detailed execution metadata — including three layers of caching — that makes diagnosing and fixing slow queries systematic rather than guesswork.
Snowflake's Three Caching Layers
Snowflake caches query results and data at three different levels. Each layer has a different scope, lifetime, and benefit. Understanding all three helps you predict query behaviour and design workloads that exploit caching naturally.
THREE CACHING LAYERS DIAGRAM ============================== LAYER 1: RESULT CACHE (Cloud Services layer) Stores: Complete query result sets Scope: Account-wide (all users share it) Expiry: 24 hours, or reset if underlying data changes Benefit: Identical repeated queries return in milliseconds, zero compute cost LAYER 2: LOCAL DISK CACHE (Virtual Warehouse nodes) Stores: Compressed micro-partition files from storage Scope: Per-warehouse (each warehouse has its own cache) Expiry: Cleared when warehouse suspends Benefit: Similar queries on same table skip storage reads, much faster LAYER 3: METADATA CACHE (Cloud Services layer) Stores: Min/max values, row counts, null counts per micro-partition Scope: Account-wide (always available) Expiry: Never (updated whenever data changes) Benefit: Enables partition pruning — query skips irrelevant partitions entirely CACHE HIT PRIORITY ORDER (fastest to slowest): 1. Result Cache hit --> instant, 0 credits 2. Local Disk hit --> fast, warehouse must be running (credits used) 3. Storage read --> slowest, reads from S3/Azure/GCS
Layer 1: Result Cache in Detail
When you run a SELECT query, Snowflake saves the entire result set in the Result Cache associated with Cloud Services. If the same query runs again within 24 hours — by any user in the account — Snowflake returns the cached result directly. The virtual warehouse does not activate. Zero credits are consumed.
RESULT CACHE EXAMPLE ===================== 09:00 AM — User Alice runs: SELECT country, SUM(revenue) FROM SALES GROUP BY country ORDER BY 2 DESC; --> Warehouse runs query: takes 8 seconds, returns 50 rows --> Result cached in Cloud Services layer 09:15 AM — User Bob runs identical query: SELECT country, SUM(revenue) FROM SALES GROUP BY country ORDER BY 2 DESC; --> Cloud Services recognises identical SQL + same underlying data --> Returns 50 rows from cache in <100 milliseconds --> Warehouse never wakes up, zero credits charged 09:30 AM — New rows inserted into SALES table --> Snowflake invalidates the cached result immediately 09:31 AM — User Carol runs the same query: --> Cache miss (data changed), warehouse executes fresh query --> New result cached for next 24 hours RESULT CACHE REQUIREMENTS (ALL must be true): - Identical SQL text (including whitespace and case) - Same database, schema, and table context - Querying user has SELECT permission on the table - Underlying data has not changed since cache was populated - No non-deterministic functions used (no CURRENT_TIMESTAMP(), RANDOM(), etc.)
Layer 2: Local Disk Cache in Detail
Each virtual warehouse node maintains a local disk cache (SSD storage) that holds copies of micro-partition files recently downloaded from cloud storage. When a query needs to read a micro-partition already in cache, the warehouse reads from local disk instead of going back to S3, Azure, or GCS. Local disk reads are 10–100x faster than reading from object storage.
LOCAL DISK CACHE BEHAVIOUR ============================ First run of query on fresh warehouse: Query scans ORDERS table (1TB, 500 micro-partitions) Warehouse downloads all 500 partitions from S3 → local cache Query time: 90 seconds Second run of same query (warehouse still running): Warehouse reads 500 partitions from local SSD cache Query time: 8 seconds (11x faster — no storage I/O) Warehouse suspended and resumed: Local cache cleared on suspension Next query re-downloads all partitions from storage Query time: 90 seconds again STRATEGIES TO MAXIMISE LOCAL CACHE: - Keep warehouse running between related query runs (longer auto-suspend) - Dedicate one warehouse per team/workload (prevents cache eviction by others) - Schedule batch jobs on the same warehouse to benefit from cached partitions
Reading the Query Profile
The Query Profile in Snowsight is the most powerful tool for diagnosing slow queries. Open it by clicking any query in your Query History and selecting the Profile tab. The profile shows a visual execution plan broken into operator nodes, with timing and data volume statistics for each node.
QUERY PROFILE — KEY METRICS TO CHECK ====================================== METRIC WHAT IT TELLS YOU ------ ----------------- Bytes Scanned Total data read from storage + cache Bytes Scanned from Cache How much came from local disk cache (want this HIGH) Partitions Scanned How many micro-partitions the warehouse read Partitions Total Total micro-partitions in the scanned table(s) Pruning Ratio Partitions Scanned ÷ Partitions Total (want this LOW) Rows Produced Number of rows in the final result Spillage to Disk Memory overflow written to disk (want this ZERO) Remote Spillage Overflow written to cloud storage (serious problem) HEALTHY QUERY INDICATORS: Bytes from Cache: > 70% of bytes scanned Pruning Ratio: < 20% (only 20% of partitions actually scanned) Spillage: 0 bytes PROBLEM INDICATORS: Bytes from Cache: < 20% (cache cold or wrong warehouse) Pruning Ratio: 100% (all partitions scanned — no pruning happening) Spillage: Any non-zero value
Partition Pruning: The Most Impactful Optimisation
Snowflake stores metadata — the min and max value of every column in every micro-partition — in the Cloud Services layer. When you include a filter in your WHERE clause on a column where data is naturally ordered (like a date column), Snowflake uses that metadata to skip entire partitions that cannot contain matching rows. This is called partition pruning.
PARTITION PRUNING ILLUSTRATION ================================ TABLE: ORDERS — 1,000 micro-partitions, each holding ~100K rows Partition metadata (simplified): Partition 1: order_date MIN=2022-01-01 MAX=2022-03-31 Partition 2: order_date MIN=2022-04-01 MAX=2022-06-30 ... Partition 800: order_date MIN=2024-04-01 MAX=2024-06-30 Partition 801: order_date MIN=2024-07-01 MAX=2024-09-30 ... YOUR QUERY: WHERE order_date = '2024-05-15' Partitions 1-799: MAX < 2024-05-15 → PRUNED (skipped) Partition 800: MIN ≤ 2024-05-15 ≤ MAX → SCANNED Partitions 801+: MIN > 2024-05-15 → PRUNED (skipped) Result: 999 partitions skipped, only 1 scanned Query scans 0.1% of the table's data — extremely fast POOR PRUNING QUERY: WHERE UPPER(product_name) = 'HEADPHONES' Snowflake cannot apply metadata to a function result → scans ALL partitions Fix: WHERE product_name = 'Headphones' (remove the function from the column)
Rules for Good Pruning
WRITE THIS (good pruning) NOT THIS (poor pruning)
---------------------------- -------------------------
WHERE order_date = '2024-01-01' WHERE TO_CHAR(order_date,'YYYY-MM-DD')='2024-01-01'
WHERE amount > 1000 WHERE amount * 1.1 > 1100
WHERE status = 'SHIPPED' WHERE LOWER(status) = 'shipped'
WHERE country_code = 'US' WHERE SUBSTR(country_code,1,2) = 'US'
Rule: Never apply functions to a filtered column in WHERE.
Apply the function to the literal value instead.
Spillage to Disk: A Major Performance Killer
When a warehouse node does not have enough memory to hold intermediate query results (for example during a large sort or a join between two massive tables), Snowflake writes the overflow to local disk (local spillage) or to cloud storage (remote spillage). Both slow queries significantly. Remote spillage is especially severe — it can make a query 10–50x slower.
SPILLAGE PREVENTION STRATEGIES
================================
PROBLEM: Sort or join runs out of memory
SOLUTION 1: Resize warehouse to a larger size (more RAM per node)
SOLUTION 2: Reduce data before the sort/join using earlier WHERE filters
SOLUTION 3: Pre-aggregate before joining large tables
PROBLEM: DISTINCT on massive datasets causes spillage
SOLUTION: Use APPROXIMATE_COUNT_DISTINCT() if exact count not needed
(returns within 2% accuracy, much less memory)
PROBLEM: GROUP BY on high-cardinality columns causes spillage
SOLUTION: Filter to a smaller date range or partition before grouping
Practical Performance Checklist
PERFORMANCE CHECK ACTION IF FAILING ----------------- ----------------- SELECT * used Replace with specific column list No WHERE clause on large table Add a date or status filter Function wrapping a filtered column Move function to the literal side JOIN on VARCHAR columns Consider using INT surrogate keys Multiple large table JOINs Filter each table with CTE before joining High remote spillage Increase warehouse size one step Low cache hit rate Keep warehouse running between queries Long queue time Enable multi-cluster or add warehouse Identical query running repeatedly Check if result cache disabled; re-enable
Using EXPLAIN to Preview the Execution Plan
-- See the query plan without actually running the query
EXPLAIN
SELECT
c.country,
COUNT(DISTINCT o.customer_id) AS unique_buyers,
SUM(o.amount) AS total_revenue
FROM ORDERS o
JOIN CUSTOMERS c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2024-01-01'
GROUP BY c.country
ORDER BY total_revenue DESC;
-- EXPLAIN output shows:
-- Operation type (TableScan, Join, Aggregate, Sort)
-- Which table each operation reads
-- Estimated row counts at each stage
-- Whether indexes or pruning will be applied
Automatic Query Optimisation Features
Snowflake applies several automatic optimisations that require no manual action from you. Knowing they exist helps you trust Snowflake's decisions and avoid manually overriding them incorrectly.
- Automatic micro-partition pruning: Snowflake always evaluates partition metadata before scanning — you cannot disable this, and you want it always active
- Automatic join reordering: Snowflake's query optimiser rearranges joins to process smaller tables first and reduce intermediate result sizes
- Automatic result caching: Enabled by default for all queries; disable only for testing with ALTER SESSION SET USE_CACHED_RESULT = FALSE
- Automatic columnar compression: Snowflake chooses optimal compression algorithms per column type — no manual compression settings needed
- Search Optimisation Service: An optional paid feature that builds secondary indexes on high-cardinality columns for point lookup queries (WHERE customer_id = 12345)
Key Points
- Snowflake has three caching layers: Result Cache (24-hour account-wide), Local Disk Cache (per-warehouse, lost on suspend), and Metadata Cache (permanent, used for pruning)
- The Result Cache returns identical queries in milliseconds with zero compute cost — design dashboards to exploit this with consistent SQL
- The Query Profile in Snowsight shows bytes scanned, cache hit rate, partition pruning ratio, and spillage — check these for every slow query
- Partition pruning is the highest-impact optimisation: filter on natural ordering columns (dates, IDs) without wrapping them in functions
- Any spillage to disk or remote storage indicates insufficient warehouse memory — increase warehouse size one step and retest
- Never apply transformation functions to columns on the WHERE clause left-hand side — this defeats pruning; apply functions to the literal value instead