Make queries prune — clustering keys and scan diagnosis

At a glance

Learning objectives

• Choose a clustering key from predicate selectivity
• Prove pruning with partitions_scanned versus partitions_total
• Fix the scan or compute problem you actually measured

What you need

• A shared warehouse with a query history you can read
• Install: npx de-agent-platform add warehouse-clustering-advisor
• Your top-cost queries and their WHERE clauses

Steps

1. Pick the clustering column from the workload — the one that appears in the WHERE clause of the most expensive queries, with reasonable cardinality; order matters, so put the most selective first. (from skill-warehouse-clustering-advisor )
2. Know the failure mode — clustering on a low-selectivity key stops pruning entirely. (from runbook-warehouse-query-slow )
3. Diagnose before changing anything — separate scan cost from compute time (bytes/partitions scanned vs queue time) and compare the plan against the clustering/partition key. (from runbook-warehouse-query-slow )
4. Measure the prune directly — query_id, partitions_scanned, partitions_total, and bytes_scanned from the session's query history. (from runbook-warehouse-query-slow )
5. Match the fix to the finding — over-scan means fixing the predicate or adding/adjusting the clustering key; rebuilding clustering on a low-selectivity key. (from runbook-warehouse-query-slow )
6. Keep it fixed — alert on the partitions_scanned/partitions_total ratio and review query history weekly for the top cost queries. (from runbook-warehouse-query-slow )

Check yourself

• Your clustering key's first column is the most selective predicate of your costliest queries.
• You can quote partitions_scanned/partitions_total for a query before and after the change.
• Scan-bound and queue-bound queries get different fixes, and you can name which yours is.

Worked example

-- Snowflake
SELECT query_id, partitions_scanned, partitions_total, bytes_scanned
FROM table(information_schema.query_history_by_session())
ORDER BY start_time DESC LIMIT 10;

Pick the column that appears in the WHERE clause of the *most expensive*
queries, with reasonable cardinality. Order matters: put the most
selective first.

Sourced from

• Warehouse Clustering Advisor
• Warehouse query is slow

Security scan

Last sweep 2026-10-24: 0 finding(s), 0 marked as examples.

Common gotchas

• Clustering on a low-selectivity key stops pruning entirely.
• Scan is optimal but the query waits → warehouse sizing or concurrency problem.
• Bytes scanned and queue time are different diseases — fix the predicate vs add clusters; sizing up a scan-bound query buys nothing.

Production considerations

• Alert on the partitions_scanned/partitions_total ratio.
• Review weekly query history for the top-cost queries.

Diagram

clustering and pruning
   |
   |- most expensive queries --> WHERE columns --> cardinality --> key order
   |
   |- query history --> partitions_scanned / partitions_total --> prune proof
   |
   |- over-scan --> fix predicate / adjust key
   |
   |- waits with optimal scan --> sizing or concurrency, not the key

Registry stats