Amey-Thakur Data partitioning
Partitioning is physical layout serving the dominant query filter. Get two numbers right (partition count, file size) and most scan performance follows; get them wrong and no cluster size saves you.
Method
- Partition by what queries filter on. Almost always event date (daily; hourly only at very high volume), because pipelines write by run date (see data-pipeline-design) and queries filter by time ranges. A second partition column must be low-cardinality and in most WHERE clauses (region, tenant tier); partitioning by high-cardinality columns (user_id) creates millions of directories and kills the metadata layer.
- Verify pruning actually happens. EXPLAIN the top queries:
partition filters must appear as partition pruning, not full scans
with post-filters. Functions wrapping the partition column
(
date(ts) = ...over a timestamp-partitioned table) defeat pruning in many engines; store and filter on the partition column directly (same discipline as indexing-strategy). - Target 128MB-1GB files inside partitions. Thousands of KB-sized files per partition drown the planner in metadata and the reader in open() calls: the small-file problem is the most common lake performance bug. Size writer parallelism to output healthy files, and compact where streaming or frequent micro-batches (see batch-vs-streaming) inevitably produce shards.
- Cluster within partitions for the second-order filter. Sort or cluster files by the next-most-filtered column (user_id, device) so min/max statistics and data skipping prune inside partitions; modern table formats (Iceberg/Delta-class) expose this as clustering/ordering directives. This is where high-cardinality selectivity belongs, instead of the partition key.
- Adopt a table format that decouples layout. Iceberg/Delta-class formats give hidden partitioning (queries need not know the scheme), safe partition evolution (change scheme for future data without rewriting history), ACID appends, and time travel: which turns partition mistakes from migration projects into configuration changes.
- Schedule maintenance as part of the pipeline. Compaction on hot-write tables, snapshot/manifest cleanup with retention (see data-retention), and partition-size monitoring (skew report: one partition 100x its siblings signals a hot key; see sharding-partitioning for the analogous OLTP problem).
Boundaries
- Warehouse-native tables (BigQuery/Snowflake-class) auto-manage micro-partitions; there your levers are clustering keys and sort order, not directory schemes: same principles, different knobs.
- Partitioning optimizes scans, not point lookups; single-row access patterns want an operational store or index, not a lake table.
- Repartitioning history is a heavyweight backfill: cost it and schedule it like any migration (see cloud-cost-optimization), which is exactly what table-format evolution helps you avoid.