Object Storage Partitioning and Columnar File Formats

Published by Programming Line in Data Engineering in Depth. Last updated .

Purpose and Outcome

Object storage is the durable storage layer behind many data lakes and lakehouses. It stores immutable objects under string keys such as events/dt=2026-09-07/hour=13/part-000.parquet. Partitioning is the practice of encoding frequently filtered values into those keys. Columnar file formats, especially Parquet and ORC, store values by column instead of by row. Together they let query engines skip whole directories, skip row groups inside files, read only selected columns, and recover from writer failures with simple object-level rules.

After this lesson, you should be able to design a lake table layout, explain why a query scans a specific amount of data, choose reasonable file sizes and partition columns, and diagnose common symptoms such as slow selective queries, too many small files, missing partitions, and schema mismatch errors.

How the Mechanism Works

Object stores do not behave like local disks. A file path is really an object key. A listing call returns keys with a prefix, and a read call fetches byte ranges from an object. There is usually no cheap in-place append or rename. Data engineering systems therefore write new objects and update metadata or manifests that point readers at the current set of objects.

Hive-style partitioning represents partition values as directory segments, for example country=US/dt=2026-09-07/. A query engine can use a predicate such as WHERE dt = DATE '2026-09-07' to list only the matching prefix or to select matching entries from a table catalog. This is partition pruning. It avoids opening files that cannot contain qualifying rows. Partition pruning works best when predicates are expressed on the partition columns themselves and when the catalog has current partition metadata.

Columnar files add another layer of pruning. Parquet files contain a footer with schema metadata, row group boundaries, column chunk locations, compression information, encodings, and statistics such as minimum, maximum, and null counts. A reader usually fetches the footer first, decides which row groups and column chunks are needed, then performs byte-range reads for only that data. If a query selects customer_id and total, the engine need not read the payload_json column. If a row group records event_time values from 10:00 to 10:14 and the query asks for 11:00 to 11:05, that row group can be skipped.

The hierarchy is therefore: catalog table, partition values, object files, row groups or stripes, column chunks, pages, and encoded values. Partitioning eliminates objects before file open. Column statistics eliminate parts of opened files. Projection eliminates unneeded columns. Compression and encoding reduce bytes transferred and CPU costs in different proportions.

Layout Anatomy

A practical table layout has three parts: a table root, one or more partition columns, and data files. A typical batch table might use s3://lake/silver/orders/dt=2026-09-07/part-00000.parquet. The root is s3://lake/silver/orders. The partition key is dt. The file name is not usually semantically important; it should be unique, stable, and written atomically by the job.

Partition columns should be low or moderate cardinality and commonly used in filters. Good candidates include ingestion date, event date, region, tenant group, or coarse device class. Poor candidates include unique user IDs, UUIDs, precise timestamps, and free-form search terms. High-cardinality partitioning creates many prefixes and tiny files, which increases planning time, listing calls, commit overhead, and metadata size.

Columnar format options matter. Compression codecs such as Snappy favor speed and broad compatibility, while Zstandard often provides higher compression at more CPU cost. Row group size controls how much data can be skipped internally. Larger row groups improve compression and sequential reads; smaller row groups improve selective skipping but add metadata overhead. Schema evolution is safer when new columns are appended with nullable defaults and when writers preserve logical types such as decimal precision and timestamp semantics.

Example 1: Partition Paths

The first example shows how rows become Hive-style object keys. The deterministic output illustrates why a date predicate can map directly to a prefix.

from datetime import date

rows = [
    {'order_id': 101, 'country': 'US', 'dt': date(2026, 9, 7)},
    {'order_id': 102, 'country': 'CA', 'dt': date(2026, 9, 7)},
]

for row in rows:
    key = f"orders/dt={row['dt'].isoformat()}/country={row['country']}/part-000.parquet"
    print(key)

The output is orders/dt=2026-09-07/country=US/part-000.parquet and orders/dt=2026-09-07/country=CA/part-000.parquet. A query for one date can ignore other dt=... prefixes. A query for one country within that date can prune further if country is included in the predicate.

Example 2: Query Pruning

This SQL fragment selects only two columns and filters on the partition column. A lake query engine can prune partitions before reading Parquet footers, then project only the requested columns from files that remain.

SELECT order_id, total_amount
FROM lake.sales.orders
WHERE dt = DATE '2026-09-07'
  AND country = 'US';

Expected behavior is that objects under other dates and countries are not scanned. If the table is cataloged by partition, the planner may not list the whole table root. If the catalog is stale, the engine might miss new partitions or spend time listing paths to discover them.

Example 3: Row Group Statistics

Partitioning is coarse. Columnar metadata handles finer skipping inside files. This example models two row groups and checks which groups overlap a query range.

row_groups = [
    {'id': 0, 'min_total': 1, 'max_total': 49},
    {'id': 1, 'min_total': 50, 'max_total': 500},
]

query_min = 100
for group in row_groups:
    might_match = group['max_total'] >= query_min
    print(group['id'], 'read' if might_match else 'skip')

The deterministic output is 0 skip and 1 read. Real Parquet readers apply this idea with footer statistics. Statistics are most useful when data is clustered so each row group has narrow min and max ranges. If values are randomly distributed, every row group may overlap the predicate and skipping becomes weak.

Design Choices and Trade-offs

Choose partition columns from query patterns, not from every available dimension. Date partitioning is common because most analytical work has time windows. Adding a second partition such as country can help if queries frequently filter by country, but it can hurt if many countries have only a few rows per day. A balanced table has enough partitions to avoid wasteful scans but not so many that each write produces hundreds of small objects.

File size is another trade-off. Very small files waste time in planning, metadata reads, network round trips, and task scheduling. Very large files reduce scheduling overhead but limit parallelism and make rewrites expensive. Many lake teams target roughly hundreds of megabytes per file for analytical tables, then adjust based on engine behavior, compression, and query concurrency.

Columnar format selection depends on the ecosystem. Parquet is broadly supported by Spark, Trino, Presto, DuckDB, Hive-compatible catalogs, and many cloud warehouses. ORC is also strong, especially in Hive-oriented stacks. Avro and JSON are useful for row-oriented interchange or streaming logs, but they generally scan more data for selective analytical queries.

Failure Modes and Troubleshooting

Slow query with a selective date filter. Symptom: a query for one day scans months of data. Cause: the predicate is wrapped in a function such as DATE(event_time) = DATE '2026-09-07' while the partition column is dt, or the table is not registered with partition metadata. Diagnose by reading the query plan and checking scanned paths. Correct by filtering directly on dt, repairing or refreshing partitions, or adding generated partition columns at write time.

Planning takes longer than execution. Symptom: a dashboard waits before tasks start, and object store request metrics show many list or head operations. Cause: too many small files or too many tiny partitions. Diagnose by counting files per partition and measuring median file size. Correct by compaction, coarser partitioning, and writer settings that batch output into larger files.

Missing data after a successful write. Symptom: objects exist in storage but queries return old results. Cause: the catalog or table manifest was not updated, or a commit failed after file creation. Diagnose by comparing object paths with catalog partitions or manifest entries. Correct by rerunning the commit step, recovering orphan files if appropriate, or using a table format such as Iceberg, Delta Lake, or Hudi that manages atomic snapshots.

Schema errors across partitions. Symptom: one partition reads amount as decimal while another reads it as string. Cause: independent writers inferred different schemas. Diagnose with file footer inspection or a schema comparison job. Correct by enforcing a writer schema, backfilling incompatible partitions, and adding schema checks before publish.

Security, Performance, and Reliability

Partition values often appear in object keys, logs, catalogs, and access audit records. Do not partition by sensitive raw values such as email addresses or full account identifiers. Use coarse tenant groups or hashed buckets when isolation and lookup patterns require it.

Performance depends on pruning, compression, parallelism, and metadata freshness. A well-designed table lets the engine answer a query by reading a small set of prefixes, footers, row groups, and column chunks. Reliability depends on publishing complete sets of objects. Writers should avoid exposing partial output as committed data. Common patterns include writing to temporary prefixes, validating row counts and schemas, then committing through a catalog update or table-format transaction.

Hands-on Lab

Prerequisites: Python 3, local shell access, and optionally DuckDB with Parquet support. The lab can be done with local directories because object-store partitioning is path based.

  1. Create a directory named lake_lab/orders.
  2. Create three Hive-style folders: dt=2026-09-06/country=US, dt=2026-09-07/country=US, and dt=2026-09-07/country=CA.
  3. Place one small CSV or Parquet file in each folder with columns order_id, total_amount, and status.
  4. Run a query or script that reads only dt=2026-09-07. If using DuckDB, use a glob that includes that date folder and select only order_id and total_amount.
  5. Verify that the result contains only rows from September 7, 2026, and that files under dt=2026-09-06 were not needed.
  6. Cleanup by removing the lake_lab directory.

Verification should include both row results and path inspection. The important lesson is not the local file format; it is that a filter aligned to the partition layout becomes a smaller set of paths to read.

Assessment

  1. A table is partitioned by user_id and receives millions of users per day. Explain two operational problems this creates and propose a better layout for daily revenue queries.
  2. A Parquet table is partitioned by date, but a query filtering one hour still scans every file for that date. What layout or file-level techniques could reduce the scan?
  3. You discover 50,000 files averaging 200 KB in yesterday’s partition. Describe the likely symptoms and a correction plan.
  4. A new writer adds amount as a string in one partition while older partitions use decimal. What checks would you add before publish?
  5. Compare partition pruning and Parquet row group pruning using one concrete query predicate.

Summary

Object storage partitioning makes lake data discoverable and skippable at the path or catalog level. Columnar formats make the remaining files efficient by storing metadata, compressed column chunks, and row group statistics. Good data engineering design aligns partition keys with common filters, controls file sizes, preserves schemas, refreshes metadata, and uses transactional table layers when multiple writers or recovery requirements make raw object paths too fragile.