File formats for big data: CSV, JSON, Avro, Parquet and ORC

Four minutes or four seconds?

What is a file format?

Words you’ll meet in this lesson

The five formats in one line each

File formats at three levels

CSV, JSON, Avro, Parquet and ORC compared

Side by side

What happens inside each format

1. Text formats repeat a lot

   rows = [("O1", "Pune", 220), ("O2", "Pune", 150), ("O3", "Thane", 20)]
   csv_text = "id,city,amount\n" + "".join(f"{i},{c},{a}\n" for i, c, a in rows)
   json_text = "".join(f'{{"id": "{i}", "city": "{c}", "amount": {a}}}\n' for i, c, a in rows)
   print("CSV bytes :", len(csv_text))
   print("JSON bytes:", len(json_text))

   CSV bytes : 51 JSON bytes: 132
   JSON repeats every field name on every row, so it is much bigger than CSV for the same data. Both store numbers as characters, so 220 takes three bytes of text instead of a compact binary number.

2. Row layout vs column layout

   rows = [("O1", "Pune", 220), ("O2", "Pune", 150), ("O3", "Thane", 20)]
   print("Row layout   :", [v for r in rows for v in r])
   columns = list(zip(*rows))
   print("Column layout:", columns)

   Row layout : ['O1', 'Pune', 220, 'O2', 'Pune', 150, 'O3', 'Thane', 20] Column layout: [('O1', 'O2', 'O3'), ('Pune', 'Pune', 'Thane'), (220, 150, 20)]
   A row format keeps each order together. A columnar format keeps each column together. To total the amounts, the columnar file reads one short run of numbers instead of every value of every row.

3. Dictionary encoding

   city = ["Pune", "Pune", "Thane", "Pune", "Mumbai", "Pune"]
   dictionary = sorted(set(city))
   codes = [dictionary.index(c) for c in city]
   print("dictionary:", dictionary)
   print("codes     :", codes)

   dictionary: ['Mumbai', 'Pune', 'Thane'] codes : [1, 1, 2, 1, 0, 1]
   A column has few distinct cities, so Parquet stores each name once and replaces values with small numbers. This only works well because similar values sit together in one column.

4. Run-length encoding loves sorted data

   city_sorted = ["Mumbai", "Pune", "Pune", "Pune", "Pune", "Thane"]
   runs = []
   for c in city_sorted:
       if runs and runs[-1][0] == c:
           runs[-1][1] += 1
       else:
           runs.append([c, 1])
   print(runs)

   [['Mumbai', 1], ['Pune', 4], ['Thane', 1]]
   When the column is sorted, repeated values become runs: “Pune × 4” instead of four copies. Sorting large tables by common filters (city, date) can shrink them dramatically.

5. Statistics let engines skip data

   row_groups = [("2026-10-01", "2026-10-10"), ("2026-10-11", "2026-10-20"), ("2026-10-21", "2026-10-31")]
   want = "2026-10-19"
   for i, (lo, hi) in enumerate(row_groups):
       print(f"row group {i}: min {lo} max {hi} ->", "READ" if lo <= want <= hi else "skip")

   row group 0: min 2026-10-01 max 2026-10-10 -> skip row group 1: min 2026-10-11 max 2026-10-20 -> READ row group 2: min 2026-10-21 max 2026-10-31 -> skip
   The Parquet footer stores the min and max of each column for every row group. A query for 19 October reads only the one row group whose range contains that date. This is predicate pushdown.

6. The schema travels with the file

   Avro, Parquet and ORC store the column names and types inside the file. Spark, Hive, BigQuery or pandas can open it and know immediately that amount is an integer and day is a date.

   No guessing types from text, no broken loads because someone typed “N/A” in a number column. A stored schema is one of the biggest practical benefits of the binary formats.

One query: what Kabir sees, and what the disk reads

Step through the six frames. Left: Kabir running the same query on two files. Right: how much of each file is actually read.

Real-time scenarios: formats at work

Three live scenarios: a size race between six formats using real numbers, Kafka messages evolving safely with Avro and a Schema Registry, and an advanced look at how sort order lets Parquet skip data.

The size race: one table, six files

These are real numbers from the program in “Run it on your PC” below: 2 lakh random orders written in six formats. Watch the file sizes appear one by one.

Kafka + Avro: changing a message without breaking anyone

TiffinGo’s order app sends Avro messages through Kafka to several consumers. Watch a safe change go through and an unsafe one get stopped by the Schema Registry.

Inside Parquet: why sorting makes queries skip data

Parquet can skip row groups using min/max statistics, but only if the data is laid out well. Watch the same query on an unsorted and a sorted file.

Which format, where

Row storage vs column storage

The same small table is stored row by row and column by column. The program counts how many values each layout must read to answer “total amount for Pune”. Then try the challenges: text vs binary size, dictionary encoding, compression of sorted data, min/max skipping and Avro-style schema evolution.

orders = [
    ("O1", "Pune", "thali", 220, "10-19"),
    ("O2", "Thane", "dosa", 150, "10-19"),
    ("O3", "Pune", "chai", 20, "10-20"),
    ("O4", "Mumbai", "lassi", 90, "10-20"),
]
columns = ["order_id", "city", "dish", "amount", "day"]

# Row layout: whole records stored together
row_file = [value for row in orders for value in row]
# Column layout: each column stored together
col_file = {name: [row[i] for row in orders] for i, name in enumerate(columns)}

# Question: total amount for Pune
row_reads = len(row_file)                                   # must read every value
col_reads = len(col_file["city"]) + len(col_file["amount"])  # only 2 columns
total = sum(a for c, a in zip(col_file["city"], col_file["amount"]) if c == "Pune")

print("Values read, row layout   :", row_reads)
print("Values read, column layout:", col_reads)
print("Total for Pune: Rs", total)

• row_file = [value for row in orders for value in row]A row format (CSV, JSON, Avro) writes record after record: O1, Pune, thali, 220, …
• col_file = {name: [row[i] for row in orders] for i, name in enumerate(columns)}A columnar format (Parquet, ORC) keeps each column together: all cities, then all amounts, and so on.
• col_reads = len(col_file["city"]) + len(col_file["amount"]) # only 2 columnsThe query touches only city and amount, so a columnar engine reads 2 of the 5 columns.

Run it on your PC: race six file formats

This program creates 2 lakh random orders and writes them as CSV, gzipped CSV, JSON lines, Avro, Parquet and ORC, then compares file sizes and answers one question from CSV and from Parquet. First install two libraries: pip install pyarrow fastavro. Save the program as formats_race.py in an empty folder and run it.

"""Write the same 2 lakh orders as CSV, CSV.gz, JSON lines, Avro, Parquet and ORC,
then compare file sizes and the time to answer one question.
Needs:  pip install pyarrow fastavro"""
import csv, gzip, json, os, random, time
import fastavro
import pyarrow as pa
import pyarrow.compute as pc
import pyarrow.orc as orc
import pyarrow.parquet as pq

def inr(n):
    """Indian digit grouping: 18977156 -> 1,89,77,156"""
    s = str(n)
    head, tail = s[:-3], s[-3:]
    while len(head) > 2:
        tail = head[-2:] + "," + tail
        head = head[:-2]
    return (head + "," + tail) if head else tail


random.seed(7)
N = 200_000
cities = ["Pune", "Mumbai", "Nashik", "Nagpur", "Thane"]
dishes = ["thali", "dosa", "biryani", "pav bhaji", "lassi", "chai"]
rows = [{"order_id": i, "city": random.choice(cities), "dish": random.choice(dishes),
         "amount": random.randint(40, 900), "day": f"2026-10-{random.randint(1, 31):02d}"} for i in range(N)]
os.makedirs("formats", exist_ok=True)
cols = list(rows[0])

with open("formats/orders.csv", "w", newline="") as f:
    w = csv.DictWriter(f, fieldnames=cols); w.writeheader(); w.writerows(rows)
with gzip.open("formats/orders.csv.gz", "wt", newline="") as f:
    w = csv.DictWriter(f, fieldnames=cols); w.writeheader(); w.writerows(rows)
with open("formats/orders.jsonl", "w") as f:
    for r in rows:
        f.write(json.dumps(r) + "\n")
schema = {"name": "Order", "type": "record", "fields": [
    {"name": "order_id", "type": "long"}, {"name": "city", "type": "string"},
    {"name": "dish", "type": "string"}, {"name": "amount", "type": "int"}, {"name": "day", "type": "string"}]}
with open("formats/orders.avro", "wb") as f:
    fastavro.writer(f, fastavro.parse_schema(schema), rows, codec="deflate")
table = pa.Table.from_pylist(rows)
pq.write_table(table, "formats/orders.parquet", compression="snappy")
orc.write_table(table, "formats/orders.orc", compression="zstd")

print("File sizes for", inr(N), "orders:")
base = os.path.getsize("formats/orders.csv")
for name in ["orders.csv", "orders.jsonl", "orders.csv.gz", "orders.avro", "orders.parquet", "orders.orc"]:
    size = os.path.getsize("formats/" + name)
    print(f"  {name:15} {size / 1024 / 1024:6.2f} MB   ({size / base * 100:5.1f}% of CSV)")

# Question: total amount for Pune. A row format must read every column of every row.
t = time.perf_counter()
total = 0
with open("formats/orders.csv") as f:
    for r in csv.DictReader(f):
        if r["city"] == "Pune":
            total += int(r["amount"])
csv_s = time.perf_counter() - t

t = time.perf_counter()
part = pq.read_table("formats/orders.parquet", columns=["city", "amount"])
pq_total = pc.sum(pc.filter(part["amount"], pc.equal(part["city"], "Pune"))).as_py()
pq_s = time.perf_counter() - t

print(f"\nTotal for Pune from CSV    : Rs {inr(total)}")
print(f"Total for Pune from Parquet: Rs {inr(pq_total)}  (read only 2 of 5 columns)")
print(f"Parquet was about {csv_s / pq_s:.0f}x faster on this machine")
meta = pq.ParquetFile("formats/orders.parquet").metadata
print(f"Parquet footer: {meta.num_row_groups} row group(s), {meta.num_columns} columns, schema stored inside the file")

# In a terminal:
#   pip install pyarrow fastavro
#   python formats_race.py

Dictionary encoding, one line at a time

A city column is encoded the way Parquet does it: each new city is added to the dictionary once, and every value becomes a small code. Watch the dictionary stop growing while the codes list keeps growing.

Worked examples: real problems, solved and traced in memory

Four format decisions, from choosing a format to estimating what min/max statistics save. Each runs live and is traced.

monthly report for finance, Kafka order events, partner webhook, lake orders table

monthly report for finance  -> CSV      (opens anywhere)
Kafka order events          -> Avro     (compact, typed, schema registry)
partner webhook             -> JSON     (every language reads it)
lake orders table           -> Parquet  (columnar, compressed, skippable)

3 sample records, scale to 1 crore

CSV : 12.7 bytes/record -> 0.12 GB
JSON: 50.7 bytes/record -> 0.47 GB
JSON is 4.0x bigger

10,000 cities in random order

random order: 63999 -> 5483 bytes, ratio 12x
sorted      : 63999 -> 151 bytes, ratio 424x

12 row groups × 128 MB, query 2026-10-17 to 2026-10-19

Row groups read: [9]
MB read with skipping: 128
MB read without      : 1536

Check yourself

Quick revision

Interview and viva questions

Practice problems

Format and encoding exercises. The last two are business tasks about storage cost and query cost.

(O1, Pune, 220), (O2, Thane, 150)

{'id': ['O1', 'O2'], 'city': ['Pune', 'Thane'], 'amount': [220, 150]}

rows = [("O1", "Pune", 220), ("O2", "Thane", 150)]
names = ["id", "city", "amount"]
cols = {n: [r[i] for r in rows] for i, n in enumerate(names)}
print(cols)

O1, Pune, 220

12 44

import json
csv_line = "O1,Pune,220\n"
json_line = json.dumps({"id": "O1", "city": "Pune", "amount": 220}) + "\n"
print(len(csv_line), len(json_line))

fastest, smallest, balanced

fastest -> snappy
smallest -> gzip
balanced -> zstd

codec = {"fastest": "snappy", "smallest": "gzip", "balanced": "zstd"}
for need in ["fastest", "smallest", "balanced"]:
    print(need, "->", codec[need])

A A A B B C A A

[('A', 3), ('B', 2), ('C', 1), ('A', 2)]

col = ["A", "A", "A", "B", "B", "C", "A", "A"]
runs = []
for v in col:
    if runs and runs[-1][0] == v:
        runs[-1] = (v, runs[-1][1] + 1)
    else:
        runs.append((v, 1))
print(runs)

dictionary [Mumbai, Pune, Thane], codes [1,1,2,0,1]

['Pune', 'Pune', 'Thane', 'Mumbai', 'Pune']

dictionary = ["Mumbai", "Pune", "Thane"]
codes = [1, 1, 2, 0, 1]
print([dictionary[c] for c in codes])

(100,450), (480,700), (20,90), (590,990)

[1, 3]

stats = [(100, 450), (480, 700), (20, 90), (590, 990)]
lo, hi = 500, 600
print([i for i, (a, b) in enumerate(stats) if a <= hi and b >= lo])

old: id, amount, note(default) · new: id, amount, coupon(default)

removed: ['note'] added: ['coupon']
compatible

old = {"id": None, "amount": None, "note": ""}      # field -> default (None = required)
new = {"id": None, "amount": None, "coupon": "NONE"}
removed = [f for f in old if f not in new]
added = [f for f in new if f not in old]
ok = all(old[f] is not None for f in removed) and all(new[f] is not None for f in added)
print("removed:", removed, "added:", added)
print("compatible" if ok else "INCOMPATIBLE")

80 TB, 25%, Rs 1,800/TB-month

Parquet size: 20.0 TB
Yearly saving: Rs 1296000

csv_tb = 80
parquet_tb = csv_tb * 0.25
saving = (csv_tb - parquet_tb) * 1800 * 12
print("Parquet size:", parquet_tb, "TB")
print("Yearly saving: Rs", int(saving))

600 GB vs 4 GB, 300/day, Rs 400/TB

CSV      Rs 2,109,375 per month
Parquet  Rs 14,062 per month

runs = 300 * 30
for name, gb in [("CSV", 600), ("Parquet", 4)]:
    cost = gb / 1024 * 400 * runs
    print(f"{name:8} Rs {cost:,.0f} per month")

Real-world scenario questions (asked in company interviews)

Format decisions you will face in real data teams. Decide your answer first, then open the model answer.