Partitioning & Clustering Lab (Interactive)
Match flat, date-partitioned, and Z-ordered layouts to three WHERE clauses and price the scan in dollars. Compute scanned terabytes, BigQuery-style billing at $6.25/TB, and latency for directory pruning, micro-partition min/max skipping, and space-filling curve clustering.
Partition Pruning & Z-Order Clustering
Match the physical layout to the WHERE clause and watch BigQuery-style scan billing collapse.
How It Works Under the Hood
Analytical billing follows bytes scanned, so physical layout is a pricing decision. Date partitioning prunes whole directories: a one-day query over five years reads 0.06% of storage. Clustering sorts micro-partitions inside each folder so min/max metadata skips irrelevant 16-50 MB blocks for tenant or status filters. Linear multi-column sorting biases toward the leading column, leaving tenant-only queries unpruned, while Z-Ordering interleaves dimension bits along a Morton curve so filtering any subset of clustered columns prunes effectively.
Core Architectural Principles
- Directory partition pruning removes entire date folders from the execution plan before scanning.
- Micro-partition min/max bounds let clustered columns skip blocks that cannot match the predicate.
- Z-Order interleaving gives balanced pruning for single-dimension filters that linear ORDER BY cannot.
When optimizing a dashboard, present layout as a two-tier strategy: coarse date partitioning for time-slice pruning, clustering keys on high-frequency filters like tenant_id, and never partition on high-cardinality IDs because that explodes into millions of tiny files. Mention $/TB scanned economics to prove the cost reasoning, and cite re-clustering maintenance compute as the honest cost.
Pruning slashes scan cost and latency but over-partitioning and re-clustering add file sprawl and background compute.