CSV to Parquet: store a log columnar and read only the bytes you need

The data in this project is synthetic — generated locally by data/make_data.py, with no external source. That is deliberate: the lesson is a property of the storage format, so a seeded generator lets the numbers (6× smaller, 2 of 20 row groups, 14 of 60 partitions) be exact and reproducible, and real access logs carry PII that couldn’t be shared anyway. No signup, no downloads, no GPU.

Before you start

New to columnar storage? Start here.

A CSV is a row store: every row is written out in full, one after another, as text. To answer “total bytes of 5xx errors,” a tool must read every byte of every row — including the long user-agent string and the client IP you didn’t ask for — and parse it from text.

Parquet is a columnar store: it keeps each column together, compressed, in binary. That changes what a query has to touch:

Column pruning — need only status and bytes? Read just those two columns; the rest stays on disk.
Predicate pushdown — each block of rows (a row group) stores the min and max of every column. A filter like ts BETWEEN 8am AND 9am skips any row group whose range can’t match, without reading it.
Partitioning — split the data into directories by a column (date=2025-02-01/, date=2025-02-02/, …). A query for one date range opens only those directories.

A one-line intuition: a CSV query reads everything and then filters; a Parquet query pushes the filter down so it reads almost nothing. Same rows, a fraction of the I/O.

What you’ll learn: convert a CSV log to Parquet and measure the size drop, then use pruning, pushdown, and partitioning to answer a query while reading only the partitions it needs. Section 3 lists exactly what you’ll be able to do.

New to Python too? See Start Here — it starts from zero.

1. The Brief

You’re handed a web app’s access log and asked a question every on-call engineer asks: which endpoints threw 5xx errors last week, and how much traffic did they burn? The naive answer loads the whole log and filters. You’ll do it the way a data platform does — store the log columnar, partition it by date, and push the filter down so the query reads 14 days out of 60 instead of the entire dataset.

Columnar formats (Parquet, ORC) and this read-only-what-you-need discipline are the backbone of every modern analytics stack — data lakes, SELECT-on-S3 engines, warehouse external tables. The skill is the same whether the query runs on your laptop or across a petabyte: shape the storage so the engine can skip what the query doesn’t touch.

2. Difficulty & time

Difficulty 3 / 10. Two libraries (pandas to load, pyarrow to store and query), one concept (read only what the query needs), and a fully specified path in Part A — no algorithm, no modelling. Calibrated against the ledger (G10): a peer of silent-row-loss, idempotent-loads, and duplicate-invoices (all 3) — single-mechanic builds where the lesson is a specific data-handling judgment (here: push the filter down; don’t full-scan), measured directly off the files. It sits below maverick-spend (4), which layers a conditional policy rule and a second detection type into one screen. It stays above the 1–2 band because pushdown genuinely can silently not happen — you think you filtered, but you read everything — and Part B’s grader is built to catch exactly that.

Time is separate from difficulty: Part A is about an hour; Part B is 2–3 hours of your own work. The scripts run in about a second — the time is comprehension.

3. What you’ll be able to do after

Convert CSV to Parquet and read the on-disk size drop and per-column cost straight off the file’s own metadata.
Prune columns — read only the columns a query needs, and explain why that’s impossible in a row store.
Use predicate pushdown — filter with row-group statistics so blocks that can’t match are never read.
Partition a dataset and prune partitions — answer a windowed query touching only the partitions in the window, and prove you did (not just that the answer is right).

The finished result

By the end of Part A you’ll have turned a 27 MB text log into a columnar file a fraction of the size, and watched a query skip almost all of it:

CSV log ................ ~27 MB
same data as Parquet ... 4.55 MB   (about 6x smaller)
reading only `status` .. ~69 KB touched, not the whole 4.5 MB
one-hour time filter ... reads 2 of 20 row groups; the other 18 are skipped, never read

Same rows, a fraction of the reading. In Part B you push a date filter down to touch 14 of 60 day-partitions instead of all 60 — that skip-what-you-don’t-need discipline is the whole point.

4. Prereqs & time box

You can write a function and use a pandas DataFrame (read_csv, groupby). No pyarrow experience needed — Part A introduces every call. A rough idea of what an HTTP status code is helps but isn’t required.

Where to write and run code

Run this project on your computer. It uses pyarrow (the Parquet library); the in-browser ▶ Run option isn’t confirmed for pyarrow yet, so this is a local-only project for now.

The one-time setup — installing Python, downloading the code from the course site (no GitHub, no git), opening a terminal, creating a virtual environment, installing the requirements, and running Part A step by step — is covered once in Start Here. Come back here once python -c "import pyarrow" runs without error.

Time box

Part A — Guided Build: 60–90 minutes. Hard cap.
Part B — Your Turn: 2–4 hours. Hard cap.

5. Setup & data

The data is synthetic and generated locally — see the banner above and SOURCES.md for full provenance. One command creates both parts’ data:

python data/make_data.py

That writes into data/ (git-ignored; regenerate anytime — the CSV is byte-for-byte identical because the seed is fixed, and the Parquet dataset is rewritten cleanly each run):

Part A — access_log_a.csv (200,000 rows, one day, ~27 MB). Columns ts, ip, method, endpoint, status, bytes, user_agent.
Part B — logs_b/date=YYYY-MM-DD/…parquet (235,957 rows across 60 daily partitions). Same columns, already stored columnar and partitioned by date.

6. Part A — Guided Build

You’ll convert the one-day CSV and measure what columnar storage buys. Run the whole thing, or (better) one # %% cell at a time — see Start Here (this project runs on your computer, not in the browser):

python part_a/convert_and_query.py

The full, commented script is in part_a/convert_and_query.py. The spine:

Step 1 — The raw CSV. Rows, columns, size on disk.

Checkpoint: 200,000 rows, ~27 MB.

Step 2 — Convert to Parquet; read size and per-column cost off the file.

Checkpoint: 4.55 MB — about 6× smaller. The low-cardinality columns (user_agent, endpoint, status) collapse to ~2% each via dictionary encoding; reading only status touches ~69 KB of the 4.5 MB file.

Step 3 — Predicate pushdown via row-group statistics. Filter to a one-hour window.

Checkpoint: the log has 20 row groups; a one-hour window overlaps only 2 of them — the other 18 are skipped by their min/max stats and never read.

Step 4 — Partition on disk. Write one directory per status; read only the error partition.

Checkpoint: 7 partitions; a status == 500 read touches 1 of them. Writes part_a/access_log_a.parquet (and a part_a/by_status/ dataset).

Why this and not that

Why Parquet is so much smaller here. The bulky columns — user agent, endpoint — are the same handful of strings repeated 200k times. A columnar store keeps one dictionary of the distinct values and integer codes per row; the CSV re-writes the full string every row. Size follows cardinality, not row count.
Why row-group stats need sorted data. Pushdown skips a row group only when its min/max can’t match the filter. Because the log is time-sorted, a time filter cleanly rules out 18 of 20 groups. On unsorted data every group’s range spans everything and nothing gets skipped — the same query would read the whole file.
Why partition on the column you filter by. Partitioning is pushdown at the directory level: the engine prunes by folder name before opening a file. Partition on the column your queries filter on (here, date in Part B) and a windowed query never even lists the other folders.

Functions you’ll meet (and where to read more)

Function	What it does here	Docs
`pq.write_table(tbl, path, row_group_size=…)`	Write Parquet with a chosen row-group size	write_table
`pq.ParquetFile(path).metadata`	Read size and per-column/row-group statistics	ParquetFile
`pq.read_table(path, columns=…, filters=…)`	Column pruning + predicate pushdown on one file	read_table
`pq.write_to_dataset(tbl, root_path, partition_cols=…)`	Write a partitioned dataset	write_to_dataset
`ds.dataset(path).get_fragments(filter=…)`	List the partition files a filter actually touches	dataset

If something looks off

Your Part B answer is correct but the pushdown test fails (partitions_read is 60, not 14). You loaded the whole dataset and filtered in pandas afterwards. Push the date filter into the dataset scan so it only opens the 14 partitions in the window — reading 14 and reading 60 give the same answer, but only one is real pushdown (and the point of the exercise).
ModuleNotFoundError: No module named 'pyarrow'. The install step was skipped — run pip install -r requirements.txt inside your activated virtual environment (see Start Here).
A checkpoint number doesn’t match. The data is seeded, so the sizes and counts are exact. Re-run python data/make_data.py (you may have skipped or edited it), then re-run Part A.

7. Part B — Your Turn

Part B is the optional “your turn” half — a guided fill-in-the-blank, not a blank page. You get a working starter (part_b/starter.py) that already handles the easy version; your job is to finish the # TODO it marks out. More open than Part A’s step-by-step, but you are never staring at an empty file. If you finished Part A and want to prove the skill, this is where it happens. New to Python? It’s completely fine to stop after Part A and come back later.

Same core skill, a partitioned dataset instead of one file, and a query with a read budget. Over data/logs_b/ (60 daily partitions), compute total bytes per endpoint for status >= 500 in the window 2025-02-01 … 2025-02-14 — and read only the partitions that window touches. The starter (part_b/starter.py) computes the right answer but reads all 60 partitions, so it fails the pushdown check.

The transfer: on one file you pushed a filter down to row groups; here you push a date filter down to partitions, so the dataset prunes directories before opening anything. Getting the answer by loading everything and filtering in pandas is the trap the grader is built to fail.

Acceptance criteria (checkable)

Your solution writes into part_b/out/:

answer.csv — columns endpoint, bytes (total bytes per endpoint in the filtered window).
report.json — with keys endpoints, partitions_read, achieved_metric (you can leave achieved_metric null; the grader scores you).

Then:

python part_b/starter.py
pytest part_b/test_solution.py -q

If pytest reports everything as skipped, it just means the output files don’t exist yet — run python part_b/starter.py first (once your solution fills in the TODOs), then re-run.

You’re done when both tests pass:

Accuracy — your per-endpoint totals reach F1 ≥ 0.90 against the truth (the reference reaches 1.00).
Pushdown — partitions_read equals 14, the number of daily partitions in the window. A full scan reports 60 and fails, even though its answer is correct.

Constraints — what must be true of your solution, not how to get there

Read only the partitions in the date window — don’t load all 60 and filter afterwards.
Report partitions_read as the number of partition files your query actually opened.
Apply both filters: the date window and status >= 500.
Sum bytes per endpoint; the totals must match exactly (integers).

Hints

Hint 1 — let the dataset prune for you

pyarrow.dataset prunes partitions when you pass a filter expression. Build one with pyarrow.compute.field("date") and hand it to the dataset — don’t materialise everything first.

Hint 2 — count what you touched

dset.get_fragments(filter=window) returns exactly the partition files the window selects. len(list(...)) of that is your partitions_read — it should be 14, not 60.

Hint 3 — one combined filter (still not the code)

to_table(filter=window & (pc.field("status") >= 500), columns=["endpoint", "bytes"]) reads only the windowed partitions and only the two columns you need, then groupby("endpoint")["bytes"].sum().

8. Self-check

You don’t need the answer key. You’re done when all of these hold:

pytest reports both tests green — accuracy F1 ≥ 0.90 (aim for 1.00) and partitions_read == 14.
If your answer is right but the pushdown test fails, you loaded the whole dataset — move the date filter into the dataset scan.
Re-running from python data/make_data.py reproduces the same numbers — the data is seeded.

And the tell-tale: reading 14 partitions and reading 60 give the same answer. If yours only works when you read all 60, you haven’t pushed the filter down — you’ve filtered after the fact.

9. Stretch

Sort within partitions. Re-sort each day’s rows by endpoint before writing, then filter by endpoint and watch row-group pushdown kick in inside a partition — measure how many row groups you skip.
Compression trade-off. Rewrite the dataset with zstd vs snappy vs uncompressed and measure size against read time. Decide which you’d ship and why.
(Genuinely hard) Two-level partitioning. Partition by date then status (date=…/status=…/). Show that the 5xx query now prunes on both levels — and discuss when the extra directories start to hurt (the small-files problem).

10. Ship it

Put this in a portfolio repo and it counts. In that repo’s README:

State the finding in one sentence an SRE gets: “5xx errors over these two weeks burned X bytes across these endpoints — and the query read 14 of 60 partitions to find it.”
Show the size drop (CSV vs Parquet) and the partitions-read count next to the answer, so the reader sees the I/O saved, not just the result.
Include the pushdown query and one paragraph on why loading-then-filtering gives the same answer for far more I/O. That paragraph is the part that signals you understand columnar storage, not just pandas.

Keep make_data.py and the tests in the repo so anyone can reproduce your numbers from zero.

11. Sources

Rendered from SOURCES.md.

Field	Value
Dataset	Synthetic web-server access logs: one-day CSV (Part A, 200k rows) + 60-day partitioned Parquet dataset (Part B, ~236k rows)
Type	Synthetic
Generated by	`data/make_data.py` (this repo), seeds `20260722` (Part A) and `20260723` (Part B)
Source URL	Not applicable — generated, not fetched
License	Not applicable — synthetic, no third-party rights
Access / generation date	2026-07-18
Redistribution	Permitted — synthetic; output regenerated from the seeded script, never committed
robots.txt checked	Not applicable — no scraping
ToS URL	Not applicable — no external source

Synthetic data teaches the mechanic; it does not prove a finding about the real world. The traffic and error patterns were generated on purpose — the point is the storage format’s behaviour, not a claim about any real service’s logs.

Next up

Finished this one? Continue the Data Engineering track:

Data contracts: catch the bad rows before they reach your table → · Browse all courses