Snowflake query tuning in depth: Query Profile, partition pruning, clustering keys and search optimization

The usual reaction to a slow Snowflake query is a bigger warehouse. Sometimes that is right. Often the query scans 100% of a table when it needs 1%, and a bigger warehouse just scans it faster at a higher price.

#Step 1: read the Query Profile

Open the query in Snowsight > Query History > Query Profile. Look at:

#Step 2: find candidates with SQL

SELECT query_id, warehouse_name, total_elapsed_time/1000 AS sec,
       partitions_scanned, partitions_total,
       ROUND(partitions_scanned / NULLIF(partitions_total,0) * 100, 1) AS pct_scanned,
       bytes_spilled_to_local_storage, bytes_spilled_to_remote_storage,
       LEFT(query_text, 120) AS q
FROM snowflake.account_usage.query_history
WHERE start_time > DATEADD(day, -7, CURRENT_TIMESTAMP())
  AND warehouse_size IS NOT NULL
  AND partitions_total > 1000
ORDER BY total_elapsed_time DESC
LIMIT 50;

Account usage views lag by up to a few hours; use INFORMATION_SCHEMA.QUERY_HISTORY() for the last minutes.

#Step 3: fix pruning in the query first

Pruning uses min/max metadata per micro-partition. It fails when:

• The filter wraps the column in a function: WHERE TO_DATE(event_ts) = '2026-10-28'. Prefer a range on the raw column: WHERE event_ts >= '2026-10-28' AND event_ts < '2026-10-29'.
• Types mismatch and force a cast on the column side.
• The filter is on a join to a dimension rather than on the fact table itself. Push the predicate onto the fact table when you can.

#Step 4: clustering keys for big, range-filtered tables

Data loaded in time order is naturally clustered on time. Tables filtered on something else (customer, region) are not. Check:

SELECT SYSTEM$CLUSTERING_INFORMATION('analytics.events', '(customer_id)');
SELECT SYSTEM$CLUSTERING_DEPTH('analytics.events', '(event_date)');

High average_depth and a wide overlap histogram mean poor clustering on that expression.

Add a key:

ALTER TABLE analytics.events CLUSTER BY (event_date, customer_id);

Automatic Clustering then reorganises data in the background and bills credits. Monitor:

SELECT table_name, SUM(credits_used) AS credits
FROM snowflake.account_usage.automatic_clustering_history
WHERE start_time > DATEADD(day, -30, CURRENT_TIMESTAMP())
GROUP BY 1 ORDER BY 2 DESC;

Warning

Clustering a table with heavy, constant DML on random keys can cost more credits than it saves. Clustering pays off for large tables (multi-TB is a common rule of thumb) that are read far more than written and filtered on the key.

Guidelines:

• Put the lowest-cardinality, most-filtered column first (date), then a higher-cardinality one.
• Use expressions to reduce cardinality: CLUSTER BY (TO_DATE(event_ts), customer_id).
• Three or four columns at most.

#Step 5: search optimization for needle-in-a-haystack lookups

Point lookups (WHERE order_id = 'X', WHERE email = ..., substring search, ARRAY_CONTAINS, geospatial) on huge tables do not prune well with clustering. Search optimization builds a persistent access structure:

ALTER TABLE analytics.orders ADD SEARCH OPTIMIZATION ON EQUALITY(order_id, email);
ALTER TABLE analytics.logs ADD SEARCH OPTIMIZATION ON SUBSTRING(message);
SHOW TABLES LIKE 'orders';            -- search_optimization_progress column
SELECT SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS('analytics.orders', 'EQUALITY(order_id)');

It has storage and maintenance costs; estimate first and enable it only for the columns your lookups use.

#Step 6: then size the warehouse

Query Acceleration Service can help large scans with selective filters on otherwise right-sized warehouses:

SELECT SYSTEM$ESTIMATE_QUERY_ACCELERATION('<query_id>');
ALTER WAREHOUSE wh_reporting SET ENABLE_QUERY_ACCELERATION = TRUE QUERY_ACCELERATION_MAX_SCALE_FACTOR = 4;

#Step 7: check the result

Compare before and after for the same query: elapsed time, partitions scanned, bytes scanned, and credits (warehouse time). A good fix reduces partitions scanned, not just time.

#Prevent it next time

• Weekly report of the top queries by elapsed time and by partitions scanned percentage.
• Review clustering and search optimization credits monthly; drop what no longer pays.
• Code review rule: no functions on filter columns of large fact tables.