learning-it by example

#114 Data engineering and pipelines 6 min

Partitioning Parquet: Which Layout Opens Fewer Files?

On 60,000 made-up orders, a one-month query opened 1 of 12 files by month, but 300 of 300 by customer.

1. On 60,000 made-up orders, a query for one month was run against a layout with one folder per month. How many files did it open?
2. What happened when the same orders were partitioned by customer into 300 folders and the one-month query ran?
3. A single Parquet file held 60,000 made-up orders in their original order, with the months mixed through its 12 row groups. How many row groups did a one-month query open?
4. Which column should you pick to partition on, according to the video's tip?
5. In the Parquet layout comparison on 60,000 made-up orders, what was measured?

Practice only: nothing is saved and no score counts towards anything. Each answer comes with the reason.

  1. Predict the folder names

    Copy the code folder first. This code needs the pyarrow package, pinned to 26.0.0 in requirements.txt. Read layouts.py and run.py and predict the first two file paths that python run.py names prints for by_month and for by_customer. Then run it if pyarrow is installed and compare.

    A hint

    In Hive style, each folder is named column=value, and the file inside is named by basename_template.

  2. Filter on another month

    In measure.py, WANTED is set to month equals 2025-03. Change it to 2025-07 and predict which numbers in python run.py layouts will change and which will stay the same. Then run it if pyarrow is installed. The label month 2025-03 that run.py prints is fixed text and will not change. Check the files opened for by month and for by customer, and the line saying whether every layout gave the same rows.

    A hint

    Think about which layouts decide by folder name and which decide by row group min and max.

  3. Make the row groups smaller

    In layouts.py, write_all uses row_group_size=5000 for the two single files. Change both to 2500 and predict what happens to the number of row groups in the sorted file, and how many of them the one-month query opens. Then run python run.py layouts if pyarrow is installed.

    A hint

    60,000 rows divided by the row group size gives the row group count. Rows of one month sit together in the sorted file.

  4. Partition by a different column

    This is a prediction task. In layouts.py, by_customer is written with write_hive on customer_id. Suppose you wrote a third folder layout partitioned by month and customer together. Write down how many files you expect and how many the one-month query would open, using what the video says about many small files. If you test it by changing write_all, note that write_hive builds its schema from one column, and that pyarrow's write_dataset has a default limit of 1024 partitions (max_partitions), so more partitions than that needs the limit raised.

    A hint

    The video warns that a column with many values gives many tiny files. Count 12 months times 300 customers.

These tasks are for you to try on your own machine. Nothing is checked or saved.

What this does not show

Made-up 60,000-row table, one query, one reader (pyarrow 26.0.0); files and row groups counted, nothing timed; the by-customer layout was bigger and the reason was not tested.

Up next in Data engineering and pipelines

More in Data engineering and pipelines

All Data engineering and pipelines videos

Related videos