CSV, JSON Lines or Parquet?

Most tabular data ends up in one of three formats. They are not competing implementations of the same idea — they solve different problems, and picking the wrong one shows up as either a slow pipeline or a corrupted export.

CSV

Text, one row per line, values separated by a delimiter. Universally readable, editable by hand, and openable in a spreadsheet by anyone.

It has no schema and no types — every value is text, and any interpretation is the reader's guess. It cannot represent nesting. And it is not one format but a family of dialects that disagree about delimiters, quoting and encoding, which is where most of the pain comes from.

Use it when a human will open the file, or when the receiving system only accepts it.

JSON Lines

One JSON object per line. It keeps JSON's types and nesting while staying streamable — you can process a hundred-gigabyte file a line at a time, and appending is just writing another line.

• Types survive: numbers stay numbers, null is distinct from empty.
• Nesting is native, so no flattening conventions to invent.
• Records are independent, so a corrupt line does not destroy the file.
• Every line is valid JSON, so debugging is grep and your eyes.

The cost is size — repeating every key on every line is verbose — and no schema enforcement, so nothing stops row 400,000 having a different shape.

Parquet

Columnar and binary. Values from the same column are stored together, which means each column compresses against itself and a query reading three columns of two hundred never touches the rest.

• Typically five to ten times smaller than the equivalent CSV.
• A real schema, embedded in the file.
• Column pruning and predicate pushdown, so analytical queries read a fraction of the bytes.
• Statistics per row group let a reader skip blocks that cannot match.

In exchange it is unreadable without tooling, awkward to append to, and poorly suited to row-at-a-time access. Reading one record means reading a row group.

The short version: CSV for humans, JSON Lines for streams and pipelines, Parquet for analytics at volume.

The questions that decide it

• Will a person open this file directly? CSV, and accept the limitations.
• Is the data nested, or do types matter? Not CSV.
• Is it larger than memory, or arriving continuously? JSON Lines or Parquet.
• Will it be queried repeatedly by column? Parquet, and the difference will be large.
• Is it an interchange with a system you do not control? Whatever they accept, and validate it on the way out.

Converting between them

Going from JSON to CSV is lossy: nesting must be flattened and types collapse to text. Going the other way is a guess, because CSV never recorded what the types were. If a pipeline round-trips through CSV, it loses information at each pass — which is a good argument for making CSV the last step rather than a middle one.