🧱 Foundations · Chapter 5

File formats: CSV, JSON, Parquet, Avro

The same 1 crore rides can be a 9 GB CSV that takes four minutes to query, or a 900 MB Parquet file that answers in four seconds. The data didn't change. The layout did.

Study this lesson free in the app →

The basics

A file format is the agreement about how records are laid out as bytes on disk. For data engineering the four that matter are CSV, JSON, Avro and Parquet.

The problem it solves: analytics queries read a few columns from millions of rows; ingestion writes whole rows one at a time. No single layout is good at both, so we pick the format to match the job.

The mental model: a table can be stored row by row (every field of ride 1, then every field of ride 2…) or column by column (every fare, then every city…). Row layout is great for writing and reading whole records. Column layout is great for "average fare in Shillong", because it reads only the fare and city columns and skips everything else.

• CSV, JSON → row-oriented text, no enforced types
• Avro → row-oriented binary, schema stored with the file
• Parquet → columnar binary, schema + statistics in a footer

Explained

Meghalaya Cabs lands 1 crore rides a month with 20 columns each. The analyst's query: SELECT avg(fare) FROM rides WHERE city = 'Shillong'.

In CSV the engine reads all 20 columns of every row. In Parquet it reads 2 columns — and skips whole chunks where no row has city = 'Shillong'. Same answer, a tenth of the bytes.

The attendance register vs the report card

A school keeps two books.

The attendance register is one line per student per day — name, class, present, time. Easy to add a line at 9 am. That's row layout.

The marks ledger has one page per subject: every student's maths mark on the maths page. When the principal asks "what's the average maths mark?" the clerk opens one page, not 900 report cards. That's columnar layout.

Neither book is better. The register is for writing as things happen; the ledger is for answering questions about a whole subject.

The four, precisely

CSV — text, delimiter-separated. No types (is 0012 a number or a PIN code?), no nesting, quoting/newline bugs. Readable by everything.

JSON / JSON Lines — text, self-describing, supports nesting. Every record repeats every key, so it's bulky. Types are weak: no date type, one number type.

Avro — binary, row-oriented. The writer's schema (JSON) is stored once in the file header; records carry no field names. Built for schema evolution: a reader resolves the writer's schema against its own, filling defaults for new fields. Splittable via sync markers. The usual format for Kafka messages (with a Schema Registry holding the schema, and each message carrying only a schema id).

Parquet — binary, columnar. File = row groups → column chunks → pages, then a footer with the schema and min/max/null-count statistics per column chunk. Engines read the footer first, then fetch only needed columns and row groups.

Inside a Parquet file

rides_2026_03.parquet
┌──────────────────────────────────────────┐
│ "PAR1" magic                             │
│ Row group 0  (~128 MB, rows 0..1.2M)     │
│   ride_id  chunk  [pages…]               │
│   city     chunk  [pages…]               │
│   fare     chunk  [pages…]               │
│   …17 more column chunks                 │
│ Row group 1  (rows 1.2M..2.4M)           │
│   …                                      │
├──────────────────────────────────────────┤
│ FOOTER: schema                           │
│   rg0.city  min='Aizawl' max='Tura'      │
│   rg0.fare  min=40  max=2650 nulls=0     │
│   rg1.city  min='Agartala' max='Guwahati'│
│ footer length + "PAR1"                   │
└──────────────────────────────────────────┘

Query city = 'Shillong' → rg1's range ends at 'Guwahati', so rg1 is skipped without reading it.

Reading only what you need

import pyarrow.parquet as pq

# 1. Column pruning: only 2 of 20 columns are read from disk
table = pq.read_table(
    "rides_2026_03.parquet",
    columns=["city", "fare"],
    # 2. Predicate pushdown: row groups whose min/max
    #    can't contain 'Shillong' are never fetched
    filters=[("city", "=", "Shillong")],
)
print(table.column("fare").to_pandas().mean())

# 3. Look at the stats the engine used
meta = pq.ParquetFile("rides_2026_03.parquet").metadata
print(meta.num_row_groups)
print(meta.row_group(0).column(1).statistics)  # min/max for city

The same query on CSV has no footer, no stats and no columns to skip — pd.read_csv parses every byte.

At scale

Small files kill Parquet. A streaming job writing a 50 KB Parquet file every 10 seconds makes 8,640 files a day per partition. Each file means a footer read and an object-store GET. Target files of 128 MB–1 GB; compact the small ones.

Row-group size is a dial. Bigger row groups = better compression and fewer footer entries; smaller = finer skipping and less memory per reader.

Statistics only help if data is sorted/clustered. If city is randomly scattered, every row group has min='Agartala' max='Tura' and nothing gets skipped. Sort by the column you filter on before writing.

The typical lake flow: land raw as JSON/Avro (whatever the source speaks, cheap to append), convert to Parquet in the curated layer where it will be queried a thousand times.

Gotchas

• gzip'd CSV is not splittable — one 20 GB .csv.gz is read by one core. Use splittable codecs or many files
• CSV type guessing — PIN codes and phone numbers lose leading zeros; NA becomes null
• JSON's single number type — large ride ids above 2^53 lose precision in JavaScript-based readers
• Updating one row in Parquet — impossible in place; files are immutable, you rewrite the file (table formats manage this for you)
• Assuming Avro in Kafka embeds the schema — with a Schema Registry each message carries a small schema id, not the schema

Write rows, read columns.

Avro/JSON for landing and messages (row, append-friendly). Parquet for anything queried (columnar, footer stats, skip what you don't need). CSV only at the edge with humans.

Words to know

In two minutes

• Row formats (CSV, JSON, Avro) store each record contiguously — good for writing and reading whole records.
• Columnar formats (Parquet, ORC) store each column contiguously — good for analytics that read few columns of many rows.
• CSV and JSON are text with weak or no types; Avro and Parquet are typed binary formats with a schema.
• Avro keeps the writer schema in the header and supports evolution via reader/writer schema resolution; it's the default for Kafka.
• Parquet = row groups → column chunks → pages, plus a footer with schema and per-chunk min/max stats.
• Column pruning + predicate pushdown let engines read a fraction of the file — but only if data is sorted on the filter column.
• Land raw in the source's format, curate into Parquet; avoid small files and in-place updates.

Test yourself

There are 9 questions, 3 puzzles and 5 recall cards for this lesson in the app — the recall cards come back on later days, just before you would forget.