Data systems engineering, part 3: Parquet files, partitioned storage, and the small-files tradeoff

A database table hides much of its physical layout. A file-based data platform cannot. The directory names, file count, row groups, statistics, and write partitions become part of the system's performance and operating behavior.

In the Spark POC, I wrote the same twelve ecommerce orders in two ways:

Unpartitioned Parquet:
  A directory containing Parquet part files.

Partitioned Parquet:
  A directory containing one subdirectory per order_date value.

The purpose was not to benchmark twelve rows. It was to separate three concepts that are easy to call “partitions” even though they are not the same thing:

Spark execution partition:
  A temporary unit of work processed by a task.

Storage partition:
  A directory selected by a partition column value.

Parquet file:
  The physical column-oriented file containing records.

That distinction is the key to reading both the filesystem and the Spark UI.

What Parquet represents

CSV is a convenient interchange format. It is plain text and usually requires a reader to parse values and infer or apply types row by row.

Parquet is an open, binary, column-oriented file format. A simplified file looks like this:

Parquet file
├── file metadata
├── row group 0
│   ├── order_id column chunk
│   ├── customer_id column chunk
│   ├── amount column chunk
│   └── ...
├── row group 1
│   ├── order_id column chunk
│   ├── customer_id column chunk
│   ├── amount column chunk
│   └── ...
└── footer metadata

The file can carry information about:

• column names and types;
• row-group boundaries;
• compression and encodings;
• minimum and maximum values;
• null counts;
• the physical location of column chunks.

This enables two different forms of skipping:

Column pruning:
  Do not read columns that the query does not need.

Predicate pushdown:
  Push a filter toward the file reader and use file metadata
  where the format and reader support it.

Parquet is not a Spark-only format. Spark wrote these files, but DuckDB was able to read them independently. That separation matters: the data is not trapped in the Spark application that produced it.

The writer makes two independent choices

The experiment's writer contains the essential distinction:

(
    orders
    .repartition(partitions)
    .write.mode("overwrite")
    .parquet(unpartitioned_path)
)

(
    orders
    .repartition(partitions, "order_date")
    .write.mode("overwrite")
    .partitionBy("order_date")
    .parquet(partitioned_path)
)

The first choice is:

.repartition(partitions)

This controls how Spark distributes work before writing.

The second choice is:

.partitionBy("order_date")

This controls the directory layout on disk.

They are related, but they are not interchangeable.

What the six storage partitions mean

The clean source contains two orders on each of six dates:

2026-03-01 → O1001, O1002
2026-03-02 → O1003, O1004
2026-03-03 → O1005, O1006
2026-03-04 → O1007, O1008
2026-03-05 → O1009, O1010
2026-03-06 → O1011, O1012

The partitioned output therefore contains directories like:

partitioned_by_order_date/
├── order_date=2026-03-01/
├── order_date=2026-03-02/
├── order_date=2026-03-03/
├── order_date=2026-03-04/
├── order_date=2026-03-05/
└── order_date=2026-03-06/

These six directories mean:

“This directory contains records whose order_date is this value.”

They do not mean six workers, six CPUs, or six Spark tasks. They are storage partitions: physical groups that can be selected or skipped using the directory name.

The partition column is part of the path, so a reader can reconstruct it from a path such as:

order_date=2026-03-01

That is why the partitioned Parquet scan's physical ReadSchema did not need to read order_date from every file. The path supplies that value logically.

The controlled partition-count experiment

The same writer was run with four requested Spark execution partition counts:

for p in 1 2 6 12; do
  bash scripts/run_poc2.sh \
    --layout \
    --partitions "$p" \
    --output-root "data/parquet-layout-p$p"
done

The observed output was:

Every filtered read returned two rows for March 1.

This table is the practical distinction:

Changing Spark execution partitions:
  Changed the number of unpartitioned output files.

Changing the input's distinct dates:
  Would change the number of order_date directories.

The date-partitioned file count happened to remain six in this run because the writer repartitioned by order_date before writing. The six date values each had a physical file. That is an observed result for this input and configuration, not a universal rule that every partitioned write must produce exactly one file per value.

A date directory can contain multiple files when several tasks write data for that value, when upstream distribution differs, or when retries and write patterns create additional output fragments. Storage partition count and file count must be measured rather than inferred from one another.

Why twelve partitions are excessive for twelve rows

The --partitions 12 unpartitioned result produced twelve Parquet files from twelve input rows. That is a useful teaching fixture and a poor production layout. Each file carries metadata and creates work for future readers. A query that must open twelve tiny files can spend more time planning and opening files than processing the records.

This is the small-files problem:

Too few output files:
  Large files may reduce parallelism or make one task too heavy.

Too many output files:
  Metadata, scheduling, file-open, and planning overhead grow.

A useful layout:
  Balances scan parallelism with healthy file sizes and access patterns.

The right file size and partition count depend on data volume, storage system, reader behavior, concurrency, and workload shape. A laptop experiment cannot produce a trustworthy universal file-size target. It can prove the direction of the tradeoff.

Partition pruning versus pushed filters

The writer also compared a filtered read of each layout:

spark.read.parquet(path).where(
    "order_date = DATE '2026-03-01'"
)

Both layouts returned:

O1001
O1002

The unpartitioned plan contained:

Scan parquet
PushedFilters: [IsNotNull(order_date), EqualTo(order_date,2026-03-01)]

The filter is pushed toward the Parquet reader. The date is stored inside the file schema, so Spark still has to plan a scan of the unpartitioned dataset and use file-level capabilities to reduce work.

The partitioned plan contained:

Scan parquet
PartitionFilters: [isnotnull(order_date), (order_date = 2026-03-01)]

This is directory-level pruning. Spark can identify that the requested date is order_date=2026-03-01 and avoid reading the other date directories as data files.

In plain language:

Pushed filter:
  “Read the files, but push this condition toward the reader.”

Partition filter:
  “Use the directory name to eliminate unrelated groups first.”

Partition pruning is powerful when the partition column matches common query filters. It is not a replacement for Parquet statistics, column pruning, or a well-designed table format.

Seeing the layout in the Spark UI

The Spark UI is an execution monitor rather than a filesystem browser. Run the layout job with:

bash scripts/run_poc2.sh \
  --layout \
  --enable-ui \
  --keep-alive

Then open the printed URL, normally:

http://localhost:4040

Use the tabs for different questions:

Jobs:
  Which actions ran, and how many stages/tasks did they trigger?

Stages:
  How many tasks completed? What input, output, and shuffle bytes were reported?

SQL / DataFrame:
  What physical scan, filter, exchange, and partition-filter nodes were used?

A stage showing 2/2 tasks means two Spark execution tasks completed. It does not show the six date directories.

To map rows to physical files, use a data reader. DuckDB exposes a filename virtual column for this experiment:

uv run python ../01-sql-foundations/scripts/query.py \
  ../01-sql-foundations/data/poc1.duckdb \
  "SELECT
      filename,
      order_date,
      order_id,
      customer_id,
      amount,
      status
   FROM read_parquet(
      'data/parquet-layout-p2/partitioned_by_order_date/**/*.parquet',
      hive_partitioning = true
   )
   ORDER BY filename, order_id"

That query connects all three levels:

physical Parquet filename
  → order_date directory
  → individual order row

The UI tells us how Spark processed the scan. The filesystem tells us how the output is organized. A row query tells us what the files contain.

Choosing a partition column

A useful storage partition column generally has:

• a manageable number of distinct values;
• values used frequently in filters;
• enough data per value to avoid tiny files;
• reasonable stability over the table's lifetime.

order_date is plausible because date-range queries are common. A high-cardinality key such as order_id would be dangerous for a large table:

order_id=O1001/
order_id=O1002/
order_id=O1003/
...

That could create an enormous directory and metadata burden. Conversely, never partitioning a very large dataset can force readers to inspect too much data. The decision belongs to the workload and storage design, not to a naming convention such as “Bronze” or “Gold.”

What this experiment proved

The controlled comparison established these local facts:

The source had six distinct dates.
Date partitioning created six storage directories.
Requested Spark partitions controlled unpartitioned file count.
The partitioned filter exposed PartitionFilters.
The unpartitioned filter exposed PushedFilters.
All layouts preserved the twelve source rows.

It did not establish a production benchmark or a universal optimal partition count. It established a vocabulary and a way to inspect the physical consequence of a layout decision.

Revisit checklist

• Can I distinguish a Spark execution partition from a storage partition?
• What exactly do the six order_date directories represent?
• Why did --partitions 12 produce twelve unpartitioned files?
• Why did the date-directory count remain six?
• What is the difference between a Parquet file and a partition directory?
• What does PartitionFilters prove?
• What does PushedFilters prove, and what does it not prove?
• Why can a query be logically correct but physically expensive?
• Why is order_id usually a dangerous storage partition key?
• Why should file counts and directory counts be measured separately?

The next article turns the same orders and customers into a join. It shows how Spark can copy a small lookup table through a broadcast join, or redistribute both datasets by customer_id through a shuffle join.