File formats: CSV at the edges, Parquet inside — row groups, statistics, Arrow, and compression tradeoffs
The format ladder with mechanisms: CSV re-parses and re-guesses types on every read; Parquet adds a typed schema, row groups with stats, column pruning and compression; Arrow is the zero-copy in-memory standard. Plus partitioning, the small-files failure, and schema evolution.
One daily export, three consuming teams, three different tables. The warehouse dropped a CSV every morning; finance, ops, and data science each read it with their own read_csv. Finance got zip codes as integers — 02134 became 2134, and a regional report quietly merged two states. Data science got customer ids as float64 because one row had an empty cell, so ids above 2^53 rounded and a dedup job started finding “duplicates” that were really two distinct customers one unit apart. Ops pinned dtypes by hand — but their list drifted from the export when a column was added, and their loader silently filled the new column into the wrong dtype. The reconciliation meeting became weekly. Nobody had written a bug; the format had. CSV stores characters, not types — every reader re-derives the schema from scratch, and three readers means three schemas. The fix was boring: the same export as Parquet, where the schema travels inside the file. The dtype arguments did not get resolved; they became impossible to have.
CSV and JSON: text at the edges
Why does the format choice matter before you touch any query engine? Because the failure class is not a runtime error you can catch — it is a silent type guess that ships three different tables to three teams from one file.
CSV is characters separated by commas — no types, no schema, no contract. Every read re-parses the bytes and re-guesses what they mean: is 02134 an int or a zip code, is an empty field NaN or an empty string, is 2026-06-01 a date or text? That guess runs per reader, per day, which makes the failure class systemic rather than incidental: leading zeros vanish, one missing cell turns an id column into float64, a locale flips the decimal separator. The mitigations — explicit dtype= maps, parse_dates, pinned column lists — are a schema maintained by hand, outside the file, by every consumer independently. JSON and NDJSON climb half a rung: they can nest, and NDJSON streams line-by-line nicely, but they remain text — verbose (field names repeated per row), still type-ambiguous at the edges (no integer-vs-float distinction in the standard, no native dates).
Parquet: the file that carries its own pruning
Parquet is columnar and typed, and its layout is the mechanism. A file ends with a footer holding the schema and metadata for every row group — a horizontal slice of rows, typically tens to a hundred-plus MB, often around a million rows per group depending on the writer. Inside a row group, each column is a column chunk, split into pages (~1 MB), the unit of encoding and compression: dictionary encoding for repetitive values, run-length encoding for long stretches, then a codec on top. Two query-time consequences fall straight out of the layout. Column pruning: a reader that needs 3 of 40 columns seeks to exactly those column chunks and never touches the rest. Predicate pushdown: the footer stores per-chunk min/max statistics (plus null counts, optionally bloom filters), so a filter like one day of timestamps skips every row group whose range cannot match — if the data was written sorted by that column, the day lives in a handful of contiguous groups and the read touches a few percent of the file.
Compression is per column chunk, and the choice is a real tradeoff: snappy decompresses at GB/s and barely taxes CPU but leaves files ~20-30% larger; zstd compresses noticeably smaller at modest extra CPU. Storage-heavy lakes default to zstd; scan-heavy hot paths sometimes keep snappy. The honest scale, same 10 M-row, ~20-column table on one box: CSV ~1.2 GB, gzipped CSV ~300-400 MB, Parquet-zstd ~150-250 MB; reading it: pandas read_csv in the tens of seconds, full-file Parquet in a few seconds, and a 2-column projection sub-second. Format choice is an order of magnitude on both axes before any engine tuning.
A query filters a 10 M-row Parquet table to a single day of ts values, and the table was written sorted by ts. Why does the read touch only a few percent of the file?
Arrow: the in-memory standard that makes formats interoperate
Arrow is not a file format competing with Parquet — it is the columnar in-memory layout that pandas (Arrow-backed dtypes), polars, and DuckDB all share. Same buffer layout in every engine means handing a table across is a pointer exchange, not a serialize-deserialize round trip: polars to DuckDB to pandas without copying the data even once. When Arrow does go to disk it is the IPC format (Feather is its file flavor) — essentially the memory layout written out, near-zero parse cost on read, but lightly compressed and not stat-pruned like Parquet. The division of labor: Parquet for storage (smallest bytes, prunable), Arrow for interchange and memory (zero parse, zero copy).
Partitioning and the two ways to ruin it
On disk, datasets get split hive-style into key=value directories — dt=2026-06-01/ — and engines prune whole directories before opening a single file: a one-day query over a year of data lists one directory of 365. The failure mode is over-partitioning: add a high-cardinality second key (say 50 k customer ids) and a year becomes ~18 million files averaging tens of KB. Now listing dominates, every file costs an open-plus-footer-read, and row-group statistics never get a chance because no file is big enough to have meaningful row groups. The working heuristics: partition only on low-cardinality columns you actually filter by, aim for files of ~100 MB-1 GB, and sort within files by the next most-filtered column so statistics pruning takes over where partitioning stops. Schema evolution follows the same grain of honesty: adding nullable columns is cheap (old files simply yield nulls), renames are breaks in disguise (column identity is the name), and type changes generally mean rewriting history — plan names and types as if they were an API, because they are.
A team partitions an events lake by dt AND customer_id (50 k customers). Queries got slower than before partitioning. What happened?
The rule
CSV at the edges — humans, spreadsheets, legacy systems that speak nothing else — ingested once, validated, and converted. Parquet inside the pipeline, where the schema travels with the data, readers prune instead of parse, and three teams reading the same file get the same table by construction.
- 01Walk the internals of a Parquet file — footer, row groups, column chunks, pages — and explain how a filtered, projected read uses each level.
- 02Give the format rule with its honest numbers, and the partitioning plus schema-evolution discipline that keeps a lake fast.
Formats are contracts, and CSV is the absence of one: characters whose meaning every reader re-derives, which is how one export produced three tables — vanished leading zeros, an id column floated by a single empty cell, hand-pinned dtypes drifting from the source. JSON and NDJSON add nesting but stay verbose, type-ambiguous text. Parquet changes the physics: a footer carries the typed schema and per-row-group min/max statistics; data lives in row groups of column chunks of ~1 MB pages, dictionary- and run-length-encoded under snappy (fast) or zstd (small). Column pruning and predicate pushdown therefore come from the file layout itself — 3 of 40 columns is a seek pattern, one day of sorted timestamps is a handful of row groups, and the same table drops from ~1.2 GB of CSV to ~200 MB of Parquet-zstd while reads fall from tens of seconds to sub-second when projected. Arrow is the in-memory half of the story: one columnar layout shared by pandas, polars, and DuckDB, making interchange a pointer hand-off, with IPC/Feather as its near-zero-parse disk form. Partition hive-style on low-cardinality filter keys, keep files around 100 MB-1 GB, sort within files, evolve schemas additively — and keep CSV where it belongs, at the edges, with Parquet inside. Now when you open a pipeline and see read_csv mid-flow — that is the first thing to replace; the schema arguments underneath it are a hand-maintained contract that belongs in the file itself.
Practice
Start at the top. Tasks go easiest → hardest: recall a fact, apply it to a case, then a senior-level stretch. Open one, attempt it, then reveal.
Something unclear?
Ask a question about this lesson. Questions are anonymous and go straight to the author to make the lesson better.