A practical guide to data file formats

Format choice looks like a detail and turns into a bill. Here is the working knowledge, ordered by how often it matters.

CSV is a transport format, not a storage format#

CSV's virtues are real: universal, human-readable, streamable, editable in anything. Its problems are equally real and get worse with scale.

No types. Every value is a string. 007 becomes 7 in whatever reads it next, and your join breaks. 2024-03-04 is ambiguous between March 4th and April 3rd, and different readers disagree.

No compression. A number takes as many bytes as its digits.

No schema. The reader guesses. Different readers guess differently.

Ambiguous edge cases. Quoting, escaping, embedded newlines, byte-order marks, and the eternal question of whether a trailing comma means an empty final field.

Use CSV for handing data to a human or crossing a system boundary you do not control. Do not use it as the storage layer for anything you will read twice.

Parquet is the default for analytics#

Columnar, typed, compressed, splittable. The specific wins:

Column pruning. select revenue from wide_table reads only the revenue column's blocks. On a sixty-column table that is roughly a sixtieth of the I/O.

Compression that actually works. A column holds one type with repeated values, so dictionary and run-length encoding are extremely effective. Five to ten times smaller than the equivalent CSV is typical; twenty times is not unusual on low-cardinality data.

Predicate pushdown. Row-group statistics let a reader skip whole blocks without decompressing them — if the data is sorted or clustered on the column you filter. Randomly ordered data defeats this entirely, which is why sort order is a real design decision rather than an afterthought.

Types survive. A timestamp comes back a timestamp.

df.to_parquet("orders.parquet", compression="zstd")   # zstd over snappy: smaller, similar speed
pd.read_parquet("orders.parquet", columns=["order_id", "revenue_usd"])

That columns= argument is the whole point. Passing it is the difference between reading 40MB and 900MB.

JSON and its line-delimited cousin#

JSON is right for nested data, config, and API payloads. Newline-delimited JSON (one object per line) is the right shape for logs and raw landing zones — it is streamable and appendable, where a single top-level JSON array is neither.

Its cost is size and parse time. Land in NDJSON, convert to Parquet in staging, and never query NDJSON at volume.

Arrow is memory, not disk#

Apache Arrow is an in-memory columnar layout. Its purpose is zero-copy interchange: pandas, Polars, DuckDB and Spark can hand each other data with no serialisation step.

You mostly do not choose Arrow. You benefit from it when tools you already use speak it, and you notice its absence when a data transfer that should be instant takes ninety seconds.

Table formats: Iceberg, Delta, Hudi#

These sit on top of Parquet files and add what a pile of files lacks:

• ACID transactions. A writer failing mid-job does not leave a half-written table for readers.
• Schema evolution. Add, rename, and reorder columns without rewriting.
• Time travel. Query the table as of last Tuesday, which turns "we shipped a bug" from a crisis into a query.
• Partition evolution. Change your partitioning without rewriting history.
• Compaction. Small files get merged, which is the single most common performance problem in object-storage data lakes.

The trade is operational complexity. For a table under a few gigabytes read by one team, plain Parquet is fine. Past that, or the moment more than one writer exists, a table format earns its keep quickly.

The decision, condensed#

Two things that cost more than format choice#

Sort order. Parquet's statistics only prune if the data is clustered on your filter column. Sorting by the column people actually filter on can cut scan volume by an order of magnitude and costs one ORDER BY at write time.

File size. Ten thousand 40KB files are far slower than forty 10MB ones, even at identical total bytes — every file costs a listing, an open, and a scheduling decision. Target 128MB to 1GB per file, and compact on a schedule if your writer produces small ones.

Both of these matter more than snappy-versus-zstd, and both get less attention.

Every dataset in our library ships as CSV because it is what you can paste into anything. The first thing the setup lesson tells you to do is convert it to Parquet, and the reason is everything above.