"Where the Bytes Live" — Partitioning, File Layout and Table Maintenance

Skim Card

One thing to remember: partition by how data is filtered and maintained, at the coarsest grain that still prunes; cluster inside partitions; maintenance is part of the table.

• Pick the coarsest time grain that gives each partition about a gigabyte. Larkspur’s month: ~1 GB at full volume; its day: ~34 MB.
• Partition on the date reports filter and corrections rewrite: the business date, not ingestion date. Raw stays by arrival.
• Day partitions read 30 files for a month and 313 files for one store’s year; month partitions sorted by store read 4 and 11.
• Z-order cut rows read 85% for the month and beat month partitions for the store, yet still opened 87 of 135 files for one month.
• After 41 micro-batches, October’s partition held 42 data files and 15 delete files; compaction made one file with the same answer.
• Compaction frees nothing until snapshots expire; orphan cleanup needs a cutoff longer than your longest write (the procedure refuses under 24 hours).

Use …	When …
No partitioning, a sort order	Small tables (Databricks: under 1 TB), dimensions
months() / days() on the event date	Facts where a partition reaches ~1 GB at that grain
A sort order inside partitions	A second column most queries filter on
bucket(N, key)	Very high-cardinality keys used for lookups or joins on very large tables
Z-order / Hilbert rewrite	Two columns queried equally often, and you’ll measure it

Verified against: Apache Iceberg 1.12.0 (Spark procedures), Spark 4.1.3, Apache Polaris 1.8.0, SeaweedFS 4.51 · vendor docs · 28 October 2026

Jump to: Concepts · Hands-On · Landscape · Design Drill

Story Opening

Part 7’s sales table had 123 files and no micro-batches yet. Now it was about to get four years of history, and Rohan had a layout ready: partitions by day, country and store. “Like indexes. Every filter gets one.”

Ingrid did the arithmetic on a napkin. “1,511 days times 600 stores: 906,600 partitions for 50 GB, about 55 KB each. Part 7’s table already has a partition field I promised to judge. Let’s build it three ways and count.”

The Quick Map

Concepts, with use cases

What partitioning is for

A partition groups a table’s files by a value derived from its rows, so a query or a maintenance job can ignore whole groups from metadata alone. In Iceberg it’s a transform of a real column (hidden partitioning). It has three legitimate jobs:

1. Pruning: a query on September never opens a file from August.
2. Maintenance scope: compaction, a late-data rebuild or a MERGE touches one partition, and writers on different partitions don’t conflict.
3. Retention: dropping a month older than seven years is a metadata operation, not a rewrite.

Its anti-goals, high cardinality (a store or customer ID) and tiny partitions, both make many small files. Databricks’ guidance for its tables is a good independent rule of thumb: don’t partition a table under 1 TB, and give “each partition … at least 1 GB of data”. BigQuery’s docs say to cluster instead when partitions would hold “approximately less than 10 GB”.

What breaks without it. With no partitioning and no deliberate order, every query reads everything (step 3); with too much, the table drowns in small files. Larkspur: the lab runs all 600 stores at ~30 baskets per store per day, about 1/50 of real volume (~1,500 baskets per store per day); at full volume (×50) four years of sales lines is about 50 GB: partition it by month. Elsewhere: a telecom keeps call records by day: a day is hundreds of gigabytes.

Iceberg’s transforms, and the verdict on country

Partition evolution changes the spec for new writes; old files keep theirs until rewrite_data_files moves them to the current spec (or output-spec-id).

Part 7 added identity(country) to days(event_ts) “so each country’s data can be maintained apart”. The verdict: drop it. At full volume a (day, country) partition is about 11 MiB, and per-country work doesn’t need partitions: store IDs begin with the country (GB-0042), so a sort by store_id puts each country in its own run of files. Data residency, the one hard per-country boundary, needs separate tables or buckets anyway.

Event date vs ingestion date

Partitioning by ingestion date suits the raw zone: arrival partitions never change once written, and Part 2 kept raw that way. Reporting tables should use the event date, here the local business date: it’s what finance filters on and what a late-data rebuild rewrites. By arrival, a September report must also read every upload that might hold September rows (Part 2: 30,248 of 2,338,812 POS events landed in a neighbouring day’s upload).

Late data still reopens old event-date partitions, so the rebuild must be partition-scoped and idempotent: Part 2’s overwrite, or a keyed MERGE. And Iceberg can’t derive a local business date from a UTC event_ts: days(event_ts) doesn’t prune a business_date filter by partition.

What breaks without it. Reports scan extra partitions; restatements land in the wrong place. Larkspur: business_date. Elsewhere: an airline partitions flown segments by departure date, not the day the booking system exported them.

Clustering: sort orders, Z-order and buckets

Clustering decides which rows sit together within partitions. An Iceberg sort order (WRITE ORDERED BY store_id, business_date), applied by Spark’s writes and rewrite_data_files, gives each file and row group a narrow range of the sort columns for statistics to skip on. A linear sort favours its first column. Z-order (and, in Iceberg 1.12, Hilbert) interleaves several columns into one curve so each gets some locality; Iceberg applies them during compaction (sort_order => 'zorder(c1, c2)'). Bucketing hashes a key into N partitions: good for joins and lookups on very large tables, more files on small ones.

What breaks without it. A store query reads every file of every month in range. Larkspur: months, sorted by store then date. Elsewhere: a payments processor sorts each day by merchant, so a merchant’s statement reads a slice of each day.

File size and the small-file problem

Iceberg’s writers target 512 MiB files (write.target-file-size-bytes) and 128 MiB row groups. Files come out small when writers commit often, spread rows over many partitions, or partitions are small; each costs a GET, a footer, a manifest entry and worse compression. What breaks: reads slow and storage grows; step 2’s day layout is 34% bigger than the month layout for the same rows. Elsewhere: an IoT platform committing every 10 seconds writes 8,640 files a day per partition.

Maintenance: four jobs Iceberg won’t do for you

• Compaction (rewrite_data_files) rewrites small files into target-sized ones, optionally sorted, applying merge-on-read delete files on the way (Part 7 owed this).
• Snapshot expiry (expire_snapshots) forgets old snapshots and deletes files only they reference.
• Orphan cleanup (remove_orphan_files) deletes files in the table’s location that no metadata references: debris from writers that died before committing.
• Manifest rewriting (rewrite_manifests) regroups manifests by partition so planning opens fewer; rewrite_position_delete_files compacts delete files.

Dropping a table is a fifth case: DROP TABLE … PURGE asks the catalog to delete the files (Polaris refuses unless purge is enabled, Part 7); without it, the files are orphans no procedure will find, because procedures need the table. Part 1’s leftover files were exactly that.

What breaks without it. Reads slow week by week; storage keeps every version. Larkspur: October after a day of micro-batches (step 5). Elsewhere: a health-records platform’s erasure request isn’t done until expiry and orphan cleanup remove the old files (Part 23).

The partition-strategy playbook

1. List the top queries and maintenance operations (rebuilds, MERGEs, deletes, retention).
2. Size it: rows per day × bytes per row, measured on real files (Larkspur’s sales lines: 14.8 bytes, step 2).
3. Pick the time column reports filter on and corrections rewrite: the event or business date.
4. Pick the coarsest grain giving ~1 GB per partition; under about 1 TB in total, consider none.
5. Cluster the next filter with a sort order; Z-order for two equally used columns, bucket only for huge keys.
6. Plan maintenance with the layout: compaction behind streaming writers, expiry windows, retention by dropping partitions.
7. Measure files and rows read for the top queries; re-check yearly, since evolution is cheap.

Under the Hood

Three layers of skipping

Planning goes top-down: manifest-list partition ranges skip manifests, each entry’s partition value and column min/max skip files, row-group statistics skip row groups (Part 3). Partitioning only guarantees clustering on its column; statistics prune any clustered column, partitioned or not, even when the clustering is an accident nobody chose and the next writer can undo (step 3 has one).

What maintenance commits

Compaction is a normal commit swapping small files and delete files for new ones; older snapshots still reference the old files, so they stay. expire_snapshots removes snapshots older than a cutoff (default 5 days, keeping at least 1, overridable with history.expire.*; tagged and branched snapshots stay), then deletes every file no surviving snapshot references, except metadata JSON (Gotchas).

Orphan cleanup lists every object under the table’s location, subtracts every file any metadata references, and deletes the rest older than older_than. An in-flight write’s files look exactly like orphans, so the cutoff must exceed the longest write. The default is 3 days; Iceberg 1.12’s procedure refuses anything under 24 hours.

Hands-On

The code is in the every-record-counts folder of the companion-workspaces repository (tag every-record-counts/part-10). Spark 4.1.3 runs in local mode with the lab’s limits (2.5 GB container, 1.5 GB driver, 8 shuffle partitions, up to three attempts per task); everything peaked at 3.55 GiB of containers, Spark at 2.13. Times are from a MacBook Pro M3 Pro (Docker VM with 4 CPUs). Outputs below are trimmed: separators, progress lines and columns not discussed are cut, and objects tables are condensed to one line.

make up-spark                                                    # core + Spark
uv run python scripts/duck.py --local sql/part07/stage.sql       # Part 7's staged POS lines, if not there yet
uv run python -m scripts.part10_layout reset
uv run python -m scripts.part10_layout spark 01_build.sql 02_layouts.sql

Step 1: four years of sales lines, three layouts

The lab has 126 business days of tills. sql/part10/spark/01_build.sql makes 1,511 from them, plainly synthetic: twelve copies of every basket line, each shifted back another 126 days (18 weeks, so weekdays stay put), basket IDs re-dated, 4 October held back for step 5. Trimmed:

CREATE TEMPORARY VIEW history AS
SELECT ..., date_sub(business_date, 126 * k) AS business_date, ...   -- event_ts, uploaded_at shifted alike
FROM lines LATERAL VIEW explode(sequence(0, 11)) copies AS k
WHERE NOT (k = 0 AND business_date = DATE '2026-10-04');

CREATE TABLE lakehouse.part10.pos_flat USING iceberg
TBLPROPERTIES ('write.target-file-size-bytes' = '8388608', 'write.parquet.row-group-size-bytes' = '2097152')
AS SELECT * FROM history;                                           -- A: no partitions, no sort order

CREATE TABLE lakehouse.part10.pos_daily USING iceberg PARTITIONED BY (business_date)
TBLPROPERTIES (..., 'write.spark.fanout.enabled' = 'false')         -- B: one partition per day
AS SELECT * FROM lakehouse.part10.pos_flat;

CREATE TABLE lakehouse.part10.pos_monthly USING iceberg PARTITIONED BY (months(business_date))
TBLPROPERTIES (...) AS SELECT * FROM lakehouse.part10.pos_flat WHERE false;
ALTER TABLE lakehouse.part10.pos_monthly WRITE ORDERED BY store_id, business_date;   -- C: months, sorted
INSERT INTO lakehouse.part10.pos_monthly SELECT * FROM lakehouse.part10.pos_flat;

    rows   first_day    last_day  days
--------  ----------  ----------  ----
72620578  2022-08-15  2026-10-03  1511

Step 2: what each layout wrote

sql/part10/spark/02_layouts.sql reads Iceberg’s files metadata table (split_offsets has one entry per row group):

           layout  partitions  data_files  total_mib  avg_file_mib  min_file_mib  row_groups
-----------------  ----------  ----------  ---------  ------------  ------------  ----------
           A flat           1         168     1079.1          6.42          4.76        3987
          B daily        1511        1511     1377.2          0.91          0.76        4749
C monthly, sorted          51         197     1026.9          5.21          0.59        4008

           layout  days_per_file
-----------------  -------------
           A flat         1511.0
          B daily            1.0
C monthly, sorted           30.2

B’s files average 0.91 MiB against an 8 MiB target and take 34% more space than C. Every file of A spans all 1,511 days: the build’s explode emitted each line’s twelve copies together. C holds 0.68 MiB a day; ×50, about 34 MiB a day and 1 GiB a month at full volume.

Step 3: two questions, three layouts

sql/part10/queries/revenue.sql asks finance’s question, store_promo.sql the promotions team’s:

SELECT country, currency, count(*) AS lines, sum(amount_minor) AS sales_minor FROM {table}
WHERE business_date BETWEEN DATE '2026-09-01' AND DATE '2026-09-30' GROUP BY country, currency

SELECT date_trunc('MONTH', business_date) AS month, count(*) AS promo_lines, sum(discount_minor) AS discount_minor
FROM {table} WHERE store_id = 'GB-0042' AND discount_minor > 0 AND business_date >= DATE '2025-10-01' GROUP BY 1

spark/jobs/part10_measure.py runs each query once to warm up, then three times (median shown), and reads the Iceberg scan’s metrics from the Spark UI’s REST API: files read and skipped, delete files, bytes, and rows returned after row-group skipping. result hashes the answer.

uv run python -m scripts.part10_layout measure pos_flat pos_daily pos_monthly

table        query        files_read skipped deletes MiB_in_files  rows_read seconds  result
--------------------------------------------------------------------------------------------
pos_flat     revenue             168       0       0       1079.1 68,298,496    3.27  c613dfc6
pos_daily    revenue              30   1,481       0         27.2  1,434,587    0.34  c613dfc6
pos_monthly  revenue               4     193       0         20.2  1,434,587    0.18  c613dfc6
pos_flat     store_promo           8     160       0         52.1    274,395    0.27  90e2e333
pos_daily    store_promo         313   1,198       0        285.9 14,816,879    0.86  90e2e333
pos_monthly  store_promo          11     186       0         70.6    237,809    0.17  90e2e333

Same answers, different work:

• Revenue. A read all 168 files, 68 million rows. B and C read exactly September’s 1.43 million rows, C from 4 files instead of 30.
• Store promotion. B can’t skip a store: 313 day files, 14.8 million rows (the 55 days it skipped had no discounts at all). C read 11 month files and 62 times fewer rows, thanks to the store sort. A did well by accident: the build’s dedup window sorted each task’s rows by basket_id, which starts with the store, so GB-0042 sat in one file per task (Part 1’s accident again).

Step 4: fix A without partitioning it

Databricks’ advice for tables under 1 TB is to cluster rather than partition. Iceberg’s equivalent is a Z-order rewrite:

uv run python -m scripts.part10_layout spark 03_zorder.sql && uv run python -m scripts.part10_layout measure pos_flat

CALL lakehouse.system.rewrite_data_files(
    table => 'part10.pos_flat', strategy => 'sort',
    sort_order => 'zorder(business_date, store_id)', options => map('rewrite-all', 'true'));

rewritten_data_files_count  added_data_files_count  rewritten_bytes_count  ...
                       168                     135             1131466739
table        query        files_read skipped deletes MiB_in_files  rows_read seconds  result
pos_flat     revenue              87      48       0        647.1 10,334,571    1.44  c613dfc6
pos_flat     store_promo           3     132       0         22.9     91,465    0.17  90e2e333

In 90 seconds, rows read fell 85% for the month and 67% for the store, where Z-ordered A now beats C (3 files and 91,465 rows against 11 and 237,809). But the month still touched 87 of 135 files: Iceberg widens a DATE to 8 bytes, so its changing bits come last while the store ID’s change in its first bytes, and the curve behaved like a sort by store, then date. Z-order is a compromise worth measuring, not a free second partition.

Step 5: a day of micro-batches

spark/jobs/part10_microbatches.py appends 4 October to C as a streaming ingester would, one commit per hour of uploads (41), then deletes SG-0007’s day with merge-on-read.

uv run python -m scripts.part10_layout microbatch
uv run python -m scripts.part10_layout spark 05_october.sql
uv run python -m scripts.part10_layout measure pos_monthly --queries october

micro-batch  1: uploads of 2026-10-03 18:00,     4 lines
micro-batch 20: uploads of 2026-10-04 13:00, 4,221 lines
micro-batch 41: uploads of 2026-10-05 10:00,     9 lines
correction: SG-0007's 4 October lines deleted (merge-on-read)
content  files  avg_kib  records
-------  -----  -------  -------
      0     42     74.7   206932
      1     15      1.7       99
snapshots  manifests_in_current_snapshot
       44                             43
table        query        files_read skipped deletes MiB_in_files  rows_read seconds  result
pos_monthly  october              42     196      15          3.1    206,833    0.45  427813df

October now has 42 data files averaging 75 KiB (41 from the batches, one from step 1), and 15 delete files covering 99 deleted rows that every reader must apply.

Step 6: compact, then expire

06_compact.sql rewrites October in the table’s sort order, drops delete files that no longer apply and regroups manifests; 07_expire.sql keeps only the current snapshot (production keeps days: Gotchas). objects counts the table’s objects in SeaweedFS by kind.

uv run python -m scripts.part10_layout spark 06_compact.sql 05_october.sql
uv run python -m scripts.part10_layout measure pos_monthly --queries october
uv run python -m scripts.part10_layout objects
uv run python -m scripts.part10_layout spark 07_expire.sql
uv run python -m scripts.part10_layout objects

CALL lakehouse.system.rewrite_data_files(table => 'part10.pos_monthly', strategy => 'sort',
    where => "business_date >= '2026-10-01'", options => map('remove-dangling-deletes', 'true'));
CALL lakehouse.system.rewrite_manifests('part10.pos_monthly');
-- older_than takes a literal or current_timestamp(); current_timestamp() - INTERVAL 1 MINUTE failed to bind
CALL lakehouse.system.expire_snapshots(table => 'part10.pos_monthly', older_than => current_timestamp(), retain_last => 1);

rewritten_data_files_count  added_data_files_count  rewritten_bytes_count  failed_data_files_count  removed_delete_files_count
                        42                       1                3211054                        0                          15
pos_monthly  october               1     196       0          2.9    206,833    0.24  427813df
after compaction:  data files 239, delete files 15, manifest lists 47, manifests 88, metadata files 49
deleted_data_files_count  deleted_position_delete_files_count  ...  deleted_manifest_files_count  deleted_manifest_lists_count
                      42                                   15  ...                            86                            46
after expiry:      data files 197, manifest lists 1, manifests 2, metadata files 50

One 2.9 MiB file, no deletes, half the time, the same 427813df. Compaction took the current snapshot from 43 manifests to 3, since most listed only replaced files; rewrite_manifests made it 2. But the bucket grew: the 42 old files stayed for older snapshots until expiry deleted them, along with the 15 delete files, 86 manifests and 46 manifest lists.

Step 7: an orphan

crash uploads a data file into October’s folder without committing it, like a writer that died:

uv run python -m scripts.part10_layout crash
uv run python -m scripts.part10_layout spark 08_orphans.sql
uv run python -m scripts.part10_layout orphans

uploaded, never committed: s3://larkspur-lake/warehouse/part10/pos_monthly/data/business_date_month=2026-10/00000-0-crashed-writer-00001.parquet (3,017,251 bytes)
orphan_file_location
--------------------
> CALL lakehouse.system.remove_orphan_files(
ERROR: IllegalArgumentException: Cannot remove orphan files with an interval less than 24 hours. ...
deleted 1 orphan file(s):
  s3://larkspur-lake/warehouse/part10/pos_monthly/data/business_date_month=2026-10/00000-0-crashed-writer-00001.parquet

The default cutoff found nothing (the file could be an in-flight write) and a cutoff of now was refused. spark/jobs/part10_orphans.py used the Spark action the message points to (deleteOrphanFiles(table).olderThan(now).usePrefixListing(true)), safe only because nothing else writes this table; objects showed 197 data files again.

uv run pytest -q tests/test_part10.py repeats the sequence and checks every claim above against the metadata tables, the scan metrics and the bucket: 7 passed in 521 s.

The Landscape

As of 28 October 2026, from each vendor’s docs.

Tips & Gotchas

Gotcha — expiry deletes your time travel. The lab expired everything to show the files go. Production keeps days of snapshots and tags what must outlive them (Part 7’s q3_close).

Gotcha — compaction is a writer. It can fail validation against a concurrent MERGE on the same partition. Compact partitions writers have finished with, and use partial-progress.enabled so one conflict doesn’t discard the whole rewrite.

Gotcha — metadata files outlive expiry. Every commit writes a metadata JSON, and step 6 ended with 50. Only write.metadata.delete-after-commit.enabled (default false) prunes them, keeping write.metadata.previous-versions-max (default 100); set it on tables with frequent commits.

Tip — Delta owes the same debt. Part 8’s deletion vectors (experimental in Delta’s docs) stay until OPTIMIZE compacts their files or REORG TABLE … APPLY (PURGE) rewrites them; VACUUM (Delta’s file cleanup, after the retention window) then removes the old files.

Tip — the files metadata table is your layout dashboard. File counts, sizes, row groups and bounds per partition (step 2) show a layout problem before query times do.

Gotcha — sort order isn’t free on write. In step 1, Spark wrote A in 57 s, B in 75 s and C in 85 s: a range shuffle and a sort. For streaming appends, write unsorted and let a scheduled sort compaction cluster finished partitions.

Troubleshooting

Design Drill

The question: “An online travel agency logs 5 billion events a day (searches, clicks, bookings) from web and apps. Analysts query by date range and often by customer, to follow a journey before booking. Data is kept 3 years, and customers can ask for erasure. Partition and cluster it, and say how you’ll keep it healthy.”

Clarify first

• Bytes per event compressed? (It sets daily volume.)
• How late do events arrive (app offline queues), and must late events land in their event date?
• What fraction of queries filter by customer, and over what ranges: a day, or a 90-day journey?
• How fresh must data be: minutes (streaming commits) or hours?

The shape of a strong answer

• Size first: 5 B × ~50 bytes ≈ 250 GB a day, ~275 TB for 3 years; an hour is ~10 GB.
• Partition by days(event_ts) on event time; hourly only if most queries are sub-day.
• Sort each day by customer_id; add bucket(N, customer_id) only if customer lookups dominate, sized so each (day, bucket) stays near 1 GB: N ≈ 128–256.
• Commit every few minutes; sort-compact each closed hour or day, so readers see target-sized, customer-sorted files.
• Late events land in their event-date partition, which is recompacted; recent days stay provisional within a lateness window.
• Expire snapshots after a few days, tag what audits need, drop partitions older than 3 years.
• Erasure: keyed merge-on-read deletes, then compaction of the touched days, expiry and orphan cleanup on a schedule; the customer sort keeps touched files few.
• Prove it with scan metrics for the top 10 queries.

Trade-offs to say out loud

• Bucketing helps customer lookups and joins but makes date-only scans open N times more files.
• Frequent commits buy freshness at the cost of compaction compute and snapshot churn.
• Z-order on (date, customer) avoids a second partition field but gives each column only partial locality; measure it.

Common weak answers

• Partitioning by customer_id: hundreds of millions of partitions.
• Partitioning by ingestion date “because that’s when we get it”: every journey query and every late-event correction spans extra partitions.
• No word on compaction, expiry or erasure: the table works on day one and degrades by month three.

Further Reading

1. Apache Iceberg, “Partitioning” and “Table Spec: Partition Transforms”. https://iceberg.apache.org/docs/latest/partitioning/
2. Apache Iceberg, “Spark Procedures” (rewrite_data_files, expire_snapshots, remove_orphan_files, rewrite_manifests). https://iceberg.apache.org/docs/latest/spark-procedures/
3. Apache Iceberg, “Maintenance”. https://iceberg.apache.org/docs/latest/maintenance/
4. Databricks, “When to partition tables on Databricks”. https://docs.databricks.com/aws/en/tables/partitions
5. Google Cloud, “Introduction to partitioned tables” (when to cluster instead). https://docs.cloud.google.com/bigquery/docs/partitioned-tables

Story Closing

Rohan’s napkin went in the bin: months by business date, sorted by store, compaction on closed days, snapshots kept a week, and Part 7’s country field dropped. Of the five promises from Part 2, this part served timeliness: four years of history answer in the time one month used to, and a day of micro-batches doesn’t slow every reader.

“Fifty gigabytes at full volume,” Rohan said. “That’s not big data.”

“No,” Ingrid said. “It might not even need a cluster.”

In Part 11, Rohan runs Larkspur’s views on one machine with DuckDB and Polars, and finds out where a laptop stops being enough.

This is Part 10 of “Every Record Counts: Data Engineering for Backend Engineers” — Season 1: Foundations & the Lakehouse.