The warehouse mental model — micro-partitions, clustering, scan vs compute

At a glance

Learning objectives

• Name the three numbers that describe a warehouse query — bytes scanned, partitions scanned ratio, queue time
• Explain why a table is a set of micro-partitions and what a clustering key prunes
• Separate a scan problem from a compute problem before touching any knob

What you need

• A warehouse to reason about — Snowflake, BigQuery, or Redshift
• Install: npx de-agent-platform add warehouse-clustering-advisor
• One slow query and one fast query from your own history to compare

Steps

1. The advisor's scope is the mental model's table of contents: clustering keys, warehouse sizing, result caching, and the difference between a scan problem and a compute problem. (from skill-warehouse-clustering-advisor )
2. Describe any warehouse query with the three numbers on the runbook's monitoring list — bytes scanned, partitions scanned ratio, queue time. (from runbook-warehouse-query-slow )
3. A table is physically a set of micro-partitions; the runbook lists data volume growth crossing a micro-partition threshold as a cause — a query can go slow with no change at all. (from runbook-warehouse-query-slow )
4. Diagnose first: a query that scans far more partitions than it needs is a clustering/partition problem; a scan that is optimal but still waits is a warehouse sizing or concurrency problem. (from skill-warehouse-clustering-advisor )
5. The clustering key is picked from workload shape — the column that appears in the WHERE clause of the most expensive queries, with reasonable cardinality, most selective first. (from skill-warehouse-clustering-advisor )
6. Compute is elastic and separate from storage — if queue time is far above execution time for short queries, scale out (more clusters), not up (bigger warehouse). (from skill-warehouse-clustering-advisor )
7. Under the engine sits the logical layer the lesson arc builds — the star schema that stops analysts re-deriving the same joins and disagreeing on metric definitions. (from pattern-dimensional-model )

Check yourself

• You can read a query's bytes scanned, partitions scanned ratio, and queue time and say which side is sick.
• Asked why a query got slow when nothing changed, you answer micro-partition thresholds before blaming code.
• You can name the clustering key you would pick for your most expensive query and why.

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;

npx de-agent-platform add warehouse-clustering-advisor

Sourced from

• Warehouse Clustering Advisor
• Warehouse query is slow
• Dimensional Model (star schema)

Security scan

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

Common gotchas

• Bytes scanned and queue time are different diseases — fix the predicate vs add clusters; sizing up a scan-bound query buys nothing.
• Clustering on a low-selectivity key stops pruning entirely.
• Data volume growth silently crosses micro-partition thresholds — a query can go slow with no change at all.

Production considerations

• Alert on the partitions_scanned/partitions_total ratio from day one.
• Scale out for queue time on short queries; scale up never fixes a scan-bound query.

Diagram

warehouse mental model
   |
   |- table = micro-partitions --> volume growth crosses thresholds silently
   |
   |- scan side --> bytes + partitions scanned --> clustering key / predicate
   |
   |- compute side --> queue time --> scale out (more clusters), not up
   |
   |- logical layer --> star schema under the engine

Registry stats