The Problem with Scanning Everything
Even with columnar storage, a query against a 10-trillion-row fact table that scans every row is slow and expensive. Cloud warehouses charge by data scanned (BigQuery) or by compute time (Snowflake). Scanning unnecessary data costs both time and money.
The solution is to physically organize data so the engine can skip irrelevant portions entirely. Two mechanisms accomplish this: partitioning and clustering.
Partitioning
Partitioning divides a table into discrete segments based on the value of a column — most commonly a date column. Each partition is stored as a separate set of files. In BigQuery, for example, adding PARTITION BY DATE(event_timestamp) to a table's CREATE TABLE statement organizes it into one partition per day; a later query filtering WHERE DATE(event_timestamp) = '2024-01-15' then reads only that single day's partition instead of scanning the entire table.
Without partitioning: scan 5 years × 365 days = 1,825 partitions worth of data.
With partitioning: scan 1 day's partition. Cost reduction: ~99.95%.
Partition granularity matters. Daily partitioning is the most common choice for event data. Hourly partitioning is useful for very high-volume streams but creates many small files (the "small files problem"). Monthly partitioning is appropriate for slower-moving data.
Clustering (Sort Keys)
Clustering physically sorts data within each partition by one or more columns. This enables the engine to skip blocks within a partition using zone maps (min/max metadata per block). In Snowflake, this is a single ALTER TABLE ... CLUSTER BY (store_id, product_category) statement naming the columns queries filter on most.
After clustering, all rows for store_id = 42 are physically adjacent on disk. A query filtering WHERE store_id = 42 reads a small contiguous range instead of scanning the entire partition.
Partitioning vs. Clustering — when to use each:
| Scenario | Use |
|---|---|
| Queries almost always filter by date | Partition by date |
| Queries filter by a high-cardinality column (user_id, store_id) | Cluster by that column |
| Queries filter by both date and another column | Partition by date, cluster by the other column |
| Table is small (< 1 GB) | Neither — overhead exceeds benefit |
Senior depth — partition explosion: Over-partitioning is a real failure mode. A table partitioned by user_id with 100 million users creates 100 million partitions. Metadata operations (listing partitions, planning queries) become slower than the queries themselves. The rule of thumb: partition only on columns with low cardinality (dates, regions, status codes) and use clustering for high-cardinality columns.