Lesson 02 Parquet & Columnar Storage
Theme:

Parquet & Columnar Storage

Lesson 02 · Data Engineering with Python · ~12 min read

1. Why CSV is the wrong answer

The Lesson 01 pipeline ends by writing a CSV. CSV is the simplest thing that works: every spreadsheet application can open it, every programming language can write it, every human can read it in a text editor. But those same properties that make CSV human-friendly make it machine-hostile at any real scale.

The first problem is that CSV has no type system. Every field is a string — the number 45212 and the word PushEvent are both just sequences of characters. Every reader has to guess whether a column contains integers, timestamps, or ISO dates. When a column contains values like 1e5 or 2024-01-01, different readers (Excel, pandas, DuckDB) will make different guesses, leading to silent type coercions and downstream bugs.

The second problem is the absence of column-level metadata. CSV has no way to express that column 3 is a UTC timestamp with millisecond precision, or that column 7 is a nullable foreign key. If you add a column between two existing columns, every downstream script that addressed columns by position silently breaks. Renaming a column breaks every script that addressed it by name. Schema evolution — the process of changing a table's structure over time — is essentially unmanaged in pure CSV.

The third problem, and the most expensive one at scale, is that CSV is row-oriented. Data is laid out on disk one full row at a time. If your query only needs the type column from a 50-column table, you still have to read all 50 columns for every row, parse 49 columns you do not need, and discard them. This means every query is a full table scan, burning I/O and CPU proportional to the total number of bytes in the file rather than the number of bytes in the columns you actually touched.

Finally, CSV has no statistics. There is no index, no bloom filter, no per-column min/max. If you want to count rows where type = 'PushEvent', the only option is to read every row and test the predicate. There is nothing to skip.

2. Row-oriented vs columnar storage

To understand why Parquet solves these problems, you need to understand the difference between row-oriented and columnar storage at the physical level.

In a row-oriented layout, the bytes for each row are stored contiguously on disk. If you picture a table as a spreadsheet, a row-oriented file stores all the cells in row 1, then all the cells in row 2, and so on. This is ideal for transactional workloads (OLTP): when you insert a new user record or update an order status, you write one contiguous block of bytes. Reading a full row by primary key is a single seek.

In a columnar layout, the bytes for each column are stored contiguously. All of column 1 first, then all of column 2, and so on. For analytics (OLAP), this is dramatically better. A query that aggregates one column out of fifty reads 2 % of the file bytes instead of 100 %. The OS page cache is used far more efficiently.

CSV — row-oriented c1 c2 c3 → row 1, row 2, row 3 … all mixed types together Parquet — columnar c1 c2 c3 → column 1, column 2, column 3 — homogeneous runs Same data — different physical byte order

Why does same-column-same-type compress better? Compression algorithms look for repeated patterns. A column of GitHub event types contains roughly 20 unique values repeated millions of times — dictionary encoding replaces each string with a 1-byte integer, cutting size by 10×. A column of timestamps for a single hour has very low entropy within a sorted row group (consecutive timestamps differ by milliseconds), so delta encoding gives another 3-4× improvement. Mixed-type rows have high entropy: the bytes for a string column, an integer column, and a float column side by side look almost random to any compressor.

For the type column specifically — which has only about 20 unique values — dictionary encoding is particularly effective. Parquet builds a per-column dictionary of unique values, then stores each cell as an index into that dictionary. The entire dictionary fits in a few hundred bytes; the column data itself is a dense array of 1-byte integers.

3. What Parquet adds beyond columnar layout

Columnar layout alone would be a big improvement over CSV, but Parquet goes further.

Per-column statistics. Every row group in a Parquet file stores the minimum value, maximum value, and null count for each column. A "row group" is a horizontal slice of the table — typically 128 MB of uncompressed data. These statistics are stored in the file footer and are read before any data pages. This enables predicate pushdown: if you query for rows where created_at > '2024-01-01T20:00:00', the reader checks the max(created_at) for each row group and skips any row group whose maximum is less than or equal to your threshold. For a sorted table this can skip 90%+ of the file.

Predicate pushdown in action. When you run SELECT count(*) FROM events WHERE type = 'PushEvent', the Parquet reader checks the column statistics for each row group. In a well-sorted dataset some row groups may contain no PushEvent rows at all (their min and max for type are both WatchEvent, say). Those row groups are skipped entirely, without reading a single data byte. Only the row groups where type statistics overlap with 'PushEvent' are decoded. This is the difference between a 0.05-second count and a 2.5-second count on the same data.

Page-level encodings. Within each column chunk, Parquet applies one or more encodings before compression. Dictionary encoding (described above) handles low-cardinality strings. Run-Length Encoding (RLE) handles repeated values — a boolean column that is mostly false is encoded as "false × 99981, true × 19" rather than 100,000 individual bytes. Delta encoding handles monotonically increasing integers or timestamps. The encoder chooses the best scheme per column automatically.

Schema embedded in the file. Parquet stores the schema — column names, data types, nullability — in the file footer. You never need an out-of-band schema registry to read a Parquet file correctly. This is the property that allows tools like DuckDB to infer the table structure from the file path alone, with no DDL required.

Row groups enable parallel reads. Because row groups are independent, multiple workers can read different row groups simultaneously. Spark, Dask, and Polars all exploit this for parallel partitioned reads. Even a single-threaded reader benefits: row groups can be selectively decompressed, so a query that touches 10 of 100 row groups only pays the decompression cost for those 10.

4. Apache Arrow and the broader ecosystem

Apache Arrow is the columnar in-memory standard. While Parquet defines how columns are stored durably on disk, Arrow defines how they are represented in RAM while a process is working with them. The two formats share the same conceptual model — type-homogeneous column buffers — but Arrow is designed for zero-copy sharing between processes, while Parquet is designed for compression and efficient I/O.

The practical implication is that reading a Parquet file into Arrow (via pyarrow) produces an in-memory table that can be handed to pandas, DuckDB, Polars, or any other Arrow-native library without serialisation. This is why the Python data ecosystem has converged so quickly: once you have an Arrow table, every major tool can consume it.

Beyond pyarrow, virtually every modern analytics system reads Parquet natively: DuckDB (in-process SQL engine), Polars (Rust-based DataFrame library), Apache Spark (distributed compute), Google BigQuery (cloud warehouse), Snowflake, Databricks Delta Lake, and AWS Athena. By choosing Parquet you are choosing a lingua franca, not a niche format — your files will be readable by any of these systems without conversion.

5. Partitioning strategy

Partitioning means splitting a logical table across multiple physical directories, typically using Hive-style naming: key=value/part-N.parquet. Most readers (pandas, DuckDB, Spark) auto-discover this layout when you point them at the root directory.

The golden rule is: partition by what your queries filter on. If 80% of your queries filter on date, partition by date. The entire benefit of partitioning is partition pruning — avoiding the read of directories whose key does not match the filter. Partitioning by a column you never filter on provides zero benefit and adds metadata overhead.

The second rule is: do not over-partition. A partitioned dataset with one row per file is catastrophically slow — each file carries roughly 1 KB of Parquet footer metadata, and opening millions of tiny files saturates the metadata capacity of object stores like S3. In production pipelines the target is 128 MB to 1 GB of uncompressed data per partition file. For the gharchive dataset, partitioning by date (24 files for a day at ~3 MB/file compressed) is appropriate at the scale of a few months; at the scale of years you would partition by year-month.

Hive-style naming (date=2024-01-01/) is important because it allows most readers to infer the partition column name and type without any catalog. Some readers (notably older versions of pandas) require you to pass columns explicitly if the partition column is not also present inside the file; modern pyarrow injects it automatically.

6. Common pitfalls

Warning: writing one Parquet file per row in a loop. Every Parquet file has a non-trivial footer — schema, column statistics, dictionary pages — that costs roughly 1 KB. Writing 1 million single-row files produces 1 GB of metadata with no data benefit. Always buffer rows into a list (or Arrow table) and write one file per batch. A batch of 50,000 rows at ~100 bytes each is still only 5 MB; the per-file overhead is negligible.

Warning: forgetting compression='snappy' (or zstd). Uncompressed Parquet still benefits from columnar layout — you can skip columns and row groups — but you lose half the size win. Snappy is the safe default: very fast to compress and decompress, decent compression ratio, available everywhere. Zstd is better if you can tolerate slightly higher CPU at write time: 30-40% smaller than Snappy at similar read latency.

Warning: assuming Parquet enforces schema consistency. Parquet enforces its own embedded schema — if you write a column as int64 it will refuse to store a string. But two different Python scripts writing to the same dataset can embed two different schemas (for instance, one writes created_at as a string and another as a timestamp[us]). When you read both partition files together, the reader has to reconcile the schemas, which may silently cast or error. Lesson 03 is dedicated to catching exactly this failure with Great Expectations.

References