Dataset formats compared: CSV, TSV, JSON, JSONL, Parquet, Avro, XML and YAML
Choosing the wrong tabular or serialization format can slow analytical queries by 50× or silently corrupt numeric and date types during import. Compare row-oriented text formats against binary columnar and row formats using real byte counts from our 1,000-row datasets.
1. Row-oriented text vs. binary columnar and row storage
Every data interchange format makes a fundamental architectural choice along two axes: text vs. binary encoding and row-oriented vs. column-oriented layout. Plain-text formats (CSV, TSV, JSON, JSONL, XML, and YAML) store numbers and timestamps as human-readable UTF-8 characters. While you can inspect them with head or a text editor, every reader must re-parse strings into 64-bit integers or IEEE 754 floats on load, and nothing in a plain CSV file distinguishes the string "00124" (a postal code) from the integer 124.
Binary schema-carrying formats (Apache Parquet, Apache Avro, and SQLite) embed an explicit schema header directly inside the file and store numbers in native machine representations with dictionary and run-length compression.
| Format | Layout | Embedded schema | Streamable line-by-line | Best engineering use case |
|---|---|---|---|---|
| CSV (.csv) | Row text | No (header names only) | Yes (unless multiline quotes) | Spreadsheet exports, simple database COPY imports |
| TSV (.tsv) | Row text | No (header names only) | Yes | Tab-delimited NLP corpora and fields containing commas |
| JSON (.json) | Document tree | Self-describing keys | No (must close root ]) | REST API payloads, small hierarchical configurations |
| JSONL / NDJSON | Row text | Self-describing per line | Yes (1 object per LF line) | Structured application logs, streaming LLM/ML datasets |
| Parquet (.parquet) | Binary columnar | Yes (Thrift footer) | By row group | DuckDB, Spark, Snowflake, Polars, BigQuery analytics |
| Avro (.avro) | Binary row | Yes (JSON header) | Yes (sync-marked blocks) | Kafka event streaming, write-heavy row serialization |
| XML (.xml) | Document tree | Via XSD / DTD | With SAX/StAX parser | Enterprise SOAP, banking ISO 20022, RSS/Atom feeds |
| YAML (.yaml) | Document tree | Implicit typing traps | No | Kubernetes manifests, CI workflows, human-edited configs |
2. Why JSON arrays fail at scale—and why JSONL fixes streaming
A standard JSON dataset wraps its records in a top-level array: [{"id": 1}, {"id": 2}]. To append a single new record to a 2 GB JSON array file, a writer must seek backward, overwrite the closing ], write a comma and the new object, and re-append ]. Worse, a standard DOM parser (JSON.parse()) must allocate the entire text buffer in RAM before returning the first row.
JSON Lines (.jsonl) and NDJSON (.ndjson) solve both problems by removing the outer array brackets and writing exactly one compact JSON object per newline-terminated line. You can append new records with a standard POSIX O_APPEND write, split a 10 GB file across 16 worker threads with split -l, and parse records in constant O(1) memory using a line reader.
3. Parquet vs. Avro: OLAP columnar scans vs. OLTP row streams
Engineers frequently ask whether to standardize on Parquet or Avro. The answer depends on how your queries access the data:
- Choose Parquet for analytical reads (OLAP): If a table has 40 columns and your query runs
SELECT region, SUM(total_usd) FROM sales WHERE settled = true GROUP BY region, Parquet reads only the 3 requested column chunks (`region`, `total_usd`, `settled`) and skips the other 37 columns completely. Because values within a single column have identical types and high repetition (for example, only 4 distinct region strings across 1,000 rows), Parquet's dictionary and run-length encoding compresses the 1,000-row sales dataset to just a few kilobytes. - Choose Avro for event streaming and full-row writes: Because Parquet buffers an entire row group in memory to transpose rows into columns and writes its schema metadata at the end of the file, it is poorly suited for appending single events in real time. Avro places its JSON schema at the beginning of the file (right after magic bytes
Obj\x01) and writes compact binary rows sequentially, making it ideal for Kafka topics and row-by-row CDC replication.
4. Practical edge cases to test in every data importer
- YAML's "Norway Problem" and unquoted booleans: In YAML 1.1 parsers, unquoted two-letter country code
NO(Norway) is parsed as booleanfalse, and unquoted version string3.10is parsed as float3.1. Always quote string values in YAML or test your parser with our 100-row YAML datasets. - CSV formula injection and embedded newlines: Verify that your CSV importer handles RFC 4180 multiline quoted fields (csv-quoted-fields) and escapes leading
=,+,-, or@characters when exporting to Excel.