JSON Lines Versus CSV for Event Data and Log Exports

Event data, meaning page views, clicks, log lines and webhook payloads, ends up in files long before it ends up in a warehouse. The two formats most tools will take are CSV and JSON Lines. Both are line oriented, both stream, and both compress well. They are not interchangeable, and picking the wrong one is the kind of mistake that costs a week later, when a new field arrives or a spreadsheet user opens the file. I took one dataset of 200,000 events and wrote it in both formats, so the size and speed claims here come from the same records.

Size on disk, raw and compressed

Raw, CSV is 2.7 times smaller. That is the repeated key names: every JSON line spells out "event", "user" and "plan" again, and the CSV writes them once in the header. Compression removes this redundancy almost entirely, because repeated strings are what compressors are best at. After gzip the CSV is 1.24 times smaller, and after zstd 1.46 times. If the file is stored or sent compressed, which it should be for anything this size, the format choice buys you much less than the raw sizes suggest. If it is read by a human or by a tool that cannot decompress, the raw gap matters.

Reading speed

Two loaders, same data:

DuckDB read the CSV a little faster and with less memory, which is what you expect from a format with a fixed layout. Python's json.loads on each line took 0.43 s against 0.36 s for csv.DictReader, and the dictionaries are different: the JSON parse returns nested objects and lists, the CSV parse returns strings that you still have to convert. These differences are small enough to be irrelevant for most jobs. Speed is not a reason to choose either format below a few gigabytes. One result is worth a note: both DuckDB sums were 2494844.56 to the cent, but the last digits of the floating point total differ (...0024 for JSON, ...0042 for CSV), which is the order of addition and not a data difference.

What flattening costs

CSV needs a fixed set of columns, so the nested records had to be flattened first, and each choice lost something.

Arrays. The tags array became a text column, sale|eco. The consumer has to know to split on |, and a tag that contains the separator breaks it. A JSON array stays an array. DuckDB's own CSV output of an array is [a, b], which is not valid JSON and not what you want to parse, so you pick the separator and document it.

Nested objects. user.plan became a plan column and props.page became page. That is fine until a second object has a field with the same name, and then you need a prefix scheme. Nothing in CSV records that user_id and plan were once siblings.

Types. In the CSV, timestamps were written as 2026-10-02 01:01:00+00, not the original 2026-10-02T01:01:00Z, because DuckDB inferred a timestamp while reading and formatted it back. The JSON Lines file kept the string exactly as written. Whenever a typed engine sits between the two formats, check the output (the same effect appears in converting a JSON array to JSON Lines and back).

Null versus empty. I wrote {"a":null}, {"a":""} and {"a":"x"} through DuckDB's CSV writer. It wrote an unquoted empty for null, and "" for the empty string, so the distinction survives in the file. Many readers do not preserve it: pandas, Excel and csv.DictReader read both as empty or as an empty string unless configured. In JSON, null and "" are different values and stay different. If a missing amount and a zero amount mean different things to your business, that matters.

Fields that only some records have

Of the 200,000 records, 20,000 had props.experiment and 180,000 did not. In JSON Lines the key is present in the records that have it and absent from the rest. In CSV it is a column that is empty for 90% of rows, and a reader cannot tell "not set" from "set to an empty string" without a convention. New fields are a bigger problem. Adding a field to a JSON Lines producer changes nothing for the readers that ignore it. Adding a CSV column shifts every positional reader, and a reader that selects by header breaks when a column is renamed. Events change shape over time (a new feature adds a property), which is the strongest argument for JSON Lines as an event format. A quick way to see how ragged a file is: jq -c '.props | keys | join(",")' events.ndjson | sort | uniq -c, which printed the two shapes and their counts (180,000 and 20,000) in under a second.

Quoting and awkward text

A message that contains a comma, a quote and a newline, hello, "world" then line2, was written by DuckDB as a quoted CSV field with doubled quotes and a real newline inside it:

id,msg,tags
1,"hello, ""world""
line2","[a, b]"

That is valid RFC 4180 CSV and it breaks anything that reads line by line, including wc -l, head, grep and every naive splitter. The JSON Lines version of the same record keeps the newline as the two characters \n inside a string, so the file is still one record per line. For logs and event streams, where free text is common, this is the property that makes JSON Lines tools like grep and split safe (JSON Lines for data pipelines and log files). CSV quoting is well defined (what RFC 4180 says about CSV), but not every producer follows it, and the consumer inherits the risk.

Which one to pick

Use CSV when:

• every record has the same flat columns, with scalar values,

• the next reader is a spreadsheet, a BI tool or a bulk loader such as COPY that expects columns,

• the file is small enough that a human will open it.

Use JSON Lines when:

• records nest or hold arrays,

• records vary in shape or will gain fields,

• values can contain newlines, tabs or arbitrary text,

• the consumer is code (a pipeline, a log tool, a document store).

Many pipelines use both: JSON Lines as the landing format, because it is forgiving, and CSV or Parquet as the delivery format after the shape has been decided. Converting is a one-line job in either direction with DuckDB, jq -r '[...]|@csv' or mlr (turning JSON arrays into CSV with jq).

When the choice does not matter

The file is small. Under a few megabytes, the difference in size and speed is below what anyone notices. Pick the one your next tool reads without conversion.

A binary format is coming. If the data will be loaded into Parquet or a warehouse table within the hour, the interchange format only needs to survive one hop. Choose the one with fewer surprises, usually JSON Lines for nested data and CSV for flat.

The consumer decides. An import wizard that takes only CSV settles the question. Write the CSV, keep a JSON Lines copy as the source of truth.

The CSV rules are in RFC 4180, the line format is described at jsonlines.org, and DuckDB's readers for both are documented under loading JSON. To inspect a record before deciding, use the JSON formatter.