Partitioning by Date/Range/Hash: Pruning, Retention, Backfill, Small Partitions, and Skew

Learning outcomes

AtlasMart frequently asks for one day, seven days, or a customer-window slice. “Partition by date” sounds obvious, but physical partitioning creates files/metadata, affects backfills and retention, and may be useless for customer-only access. Lesson 3 turns those tradeoffs into deterministic file and byte counts rather than slogans.

1. Partitioning is a physical grouping of rows/files—not the business grain

A partition key decides which physical partition receives a row. Pruning is the ability to exclude partitions from a query because partition metadata proves they cannot match the predicate. Range partitioning groups contiguous key ranges; hash partitioning maps keys through a deterministic hash to buckets; date partitioning is a common range specialization.

One order line remains one fact row whether its file is grouped by day, month, hash bucket, or not partitioned. Confusing partition key with grain leads teams to drop necessary columns or duplicate rows merely to fit a storage layout.

2. The benchmark layouts contain identical rows and bytes

The harness writes exactly the same 21,600 canonical rows three ways: 60 daily JSONL files, 3 monthly JSONL files, and 8 customer-hash bucket files. Each layout totals 1,411,200 bytes because JSON serialization is identical; only grouping changes.

These are exact local file sizes, not estimates of a columnar engine. A real columnar format would add file/footer metadata, compression, row groups, statistics, and projection/predicate pushdown effects. Chapter 18 explores those mechanisms.

3. Date partitioning wins Q1/Q2 here because the predicates prove date bounds

Q1 reads 23,520 bytes from one daily file instead of all 1,411,200 bytes. Q2 reads seven daily files totaling 164,640 bytes. The monthly layout must scan 493,920 bytes for either query because both dates fall in September.

This is not “daily is always better.” Daily files increase object/partition count and can become tiny at low volume. Month partitions can be preferable when daily volume is small or most queries scan month-sized ranges. Partition choice is an economic/workload decision.

4. Hash partitioning can localize equality access and still be terrible for another query

Q3 knows customer C002, so the stable customer hash identifies one bucket containing 319,200 bytes. But Q1 is date-only; the same customer-hash layout must inspect all eight buckets and all 1,411,200 bytes. Partitioning helps only when the query predicates align with the partition metadata.

There is another problem: skew. The five synthetic customers map into only three nonempty buckets; one holds 16,800 rows and another 4,800, giving a nonempty max/min ratio of 3.5. A distributed engine could turn that skew into uneven worker load. Hash functions distribute keys, not business frequency.

5. Tiny and high-cardinality partitions multiply metadata and maintenance

If AtlasMart partitioned by date × product, the 60-day/4-product benchmark would create 240 partitions averaging about 5,880 bytes each in this JSONL model. If it partitioned by a unique order-line identity, it would create 21,600 partitions—one per row. Neither changes the logical grain, but both create disproportionate file/metadata/listing/retention/backfill overhead.

Most analytical engines have their own minimum useful partition/file sizes and metadata costs; do not import a universal threshold. Measure actual object counts, partition pruning, file sizes, metadata planning time, and compaction/maintenance behavior in the target platform.

6. Retention and backfill are first-class partition requirements

Partitioning can simplify lifecycle operations: deleting an expired date range or rebuilding one damaged day can be safer than row-by-row operations. But that benefit exists only when retention/backfill scope aligns with the partition key. Customer-hash buckets are awkward for “rebuild 2026-09-20” because every bucket may contain that date.

Chapter 16 already taught that backfill scope is semantic. Chapter 17 adds a physical question: can the storage layout isolate the affected history without rewriting unrelated data?

7. Controlled failure: date partition every table

A tiny customer dimension with one current row per customer gains little from daily partitioning and may become harder to query/maintain. A large event fact can benefit because most queries/retention policies are date-bounded. Apply partitioning to the workload and lifecycle, not to the word “warehouse.”

8. Hands-on acceptance

import jsonfrom pathlib import Pathr=json.loads(Path("atlasmart_ch17_lab/reports/benchmark_report.json").read_text())assert r["file_bytes"]["date_files"] == 60assert r["scan"]["Q1_daily_gmv"]["date"] == {"files": 1, "bytes": 23520}assert r["scan"]["Q1_daily_gmv"]["hash_customer"]["bytes"] == 1411200assert r["file_bytes"]["estimated_date_product_files"] == 240assert r["file_bytes"]["high_cardinality_partition_count"] == 21600print("partition evidence accepted")