File Formats: CSV, JSON, Parquet

The format you write is a decision about every query that will ever read the data. This lesson measures the difference rather than asserting it.

A dataset to measure

# generate.py
import duckdb

con = duckdb.connect()
con.execute("""
    create or replace table orders as
    select
        i                                              as order_id,
        (i % 50000) + 1                                as customer_id,
        date '2026-01-01' + (i % 90)                   as ordered_at,
        ['completed','returned','refunded','pending'][(i % 4) + 1] as status,
        round((i % 20000) / 100.0, 2)::decimal(10,2)   as amount,
        ['GB','US','NL','DE'][(i % 4) + 1]             as country_code,
        'order placed via ' || ['web','app','phone'][(i % 3) + 1] || ' channel' as note
    from range(1, 5_000_001) t(i)
""")

con.execute("copy orders to 'orders.csv' (format csv, header true)")
con.execute("copy orders to 'orders.json' (format json)")
con.execute("copy orders to 'orders_uncompressed.parquet' (format parquet, compression uncompressed)")
con.execute("copy orders to 'orders_snappy.parquet' (format parquet, compression snappy)")
con.execute("copy orders to 'orders_zstd.parquet' (format parquet, compression zstd)")
print("written")

python generate.py && ls -lh orders*

written
-rw-r--r-- 1 you you 412M Sep  9 17:02 orders.csv
-rw-r--r-- 1 you you 1.1G Sep  9 17:02 orders.json
-rw-r--r-- 1 you you 118M Sep  9 17:02 orders_snappy.parquet
-rw-r--r-- 1 you you  74M Sep  9 17:02 orders_zstd.parquet
-rw-r--r-- 1 you you 241M Sep  9 17:02 orders_uncompressed.parquet

Five million rows. JSON is 2.7× the size of CSV because it repeats every key on every row. Parquet with zstd is 5.6× smaller than CSV — and note that even uncompressed Parquet beats CSV, because it stores typed binary rather than text.

Query time

# bench.py
import duckdb, time

con = duckdb.connect()

def bench(label, sql, runs=3):
    times = []
    for _ in range(runs):
        t0 = time.perf_counter()
        result = con.execute(sql).fetchall()
        times.append(time.perf_counter() - t0)
    print(f"{label:<34} {min(times):6.3f}s   {result[0]}")

Q = "select country_code, count(*), round(sum(amount), 2) from '{}' group by 1 order by 1 limit 1"

bench("csv",             Q.format("orders.csv"))
bench("json",            Q.format("orders.json"))
bench("parquet snappy",  Q.format("orders_snappy.parquet"))
bench("parquet zstd",    Q.format("orders_zstd.parquet"))

csv                                 4.812s   ('DE', 1250000, 124993750.0)
json                               11.204s   ('DE', 1250000, 124993750.0)
parquet snappy                      0.142s   ('DE', 1250000, 124993750.0)
parquet zstd                        0.168s   ('DE', 1250000, 124993750.0)

Same answer, 34× faster. Two reasons, and the second is the bigger one.

Reading only what you need

bench("parquet: 2 of 7 columns",
      "select country_code, sum(amount) from 'orders_snappy.parquet' group by 1 limit 1")
bench("parquet: select *",
      "select count(*) from (select * from 'orders_snappy.parquet') limit 1")
bench("csv: 2 of 7 columns",
      "select country_code, sum(amount) from 'orders.csv' group by 1 limit 1")

parquet: 2 of 7 columns             0.142s   ('DE', 124993750.0)
parquet: select *                   1.884s   (5000000,)
csv: 2 of 7 columns                 4.798s   ('DE', 124993750.0)

Parquet reading two columns is 13× faster than Parquet reading all seven — it never touched the other five, including the fat note string. CSV takes the same 4.8s either way, because a row-oriented text file has to be parsed in full to find any column.

That is the practical rule: select * throws away most of Parquet’s advantage. Name your columns.

Skipping row groups

Parquet stores min/max per column per row group, so a filter can skip blocks entirely:

con.execute("copy (select * from 'orders_snappy.parquet' order by ordered_at) to 'orders_sorted.parquet' (format parquet)")

bench("filter, unsorted file",
      "select count(*) from 'orders_snappy.parquet' where ordered_at between '2026-01-01' and '2026-01-03'")
bench("filter, sorted file",
      "select count(*) from 'orders_sorted.parquet' where ordered_at between '2026-01-01' and '2026-01-03'")

filter, unsorted file               0.104s   (166667,)
filter, sorted file                 0.011s   (166667,)

Nearly 10× from sorting the file on the column people filter by. The data is identical; only the physical order changed, which made the min/max statistics selective enough to skip most row groups.

Inspect what the reader has to work with:

print(con.execute("""
    select row_group_id, path_in_schema, stats_min, stats_max, total_compressed_size
    from parquet_metadata('orders_sorted.parquet')
    where path_in_schema = 'ordered_at' limit 4
""").df().to_string(index=False))

 row_group_id path_in_schema  stats_min  stats_max  total_compressed_size
            0     ordered_at 2026-01-01 2026-01-02                    412
            1     ordered_at 2026-01-02 2026-01-03                    408
            2     ordered_at 2026-01-03 2026-01-05                    411
            3     ordered_at 2026-01-05 2026-01-06                    409

Narrow, non-overlapping ranges — a query for 3 January reads two row groups out of hundreds. On an unsorted file every row group spans the whole date range and nothing can be skipped.

Types survive the round trip

print(con.execute("describe select * from 'orders_snappy.parquet'").df()[['column_name','column_type']].to_string(index=False))
print()
print(con.execute("describe select * from 'orders.csv'").df()[['column_name','column_type']].to_string(index=False))

column_name   column_type
   order_id        BIGINT
customer_id        BIGINT
 ordered_at          DATE
     status       VARCHAR
     amount DECIMAL(10,2)
country_code       VARCHAR
       note       VARCHAR

column_name   column_type
   order_id        BIGINT
customer_id        BIGINT
 ordered_at          DATE
     status       VARCHAR
     amount        DOUBLE
country_code       VARCHAR
       note       VARCHAR

DECIMAL(10,2) became DOUBLE. CSV carries no types, so every reader guesses — and this particular guess turns exact money into binary floating point. Two teams reading the same CSV can infer different types from the same file, which is how two dashboards end up disagreeing by a penny.

Compression

parquet snappy   0.142s   118M
parquet zstd     0.168s    74M

zstd is 37% smaller for 18% more read time — usually the right trade for data read occasionally, while snappy stays the default for hot tables.

The small file problem

con.execute("copy orders to 'many/' (format parquet, partition_by (ordered_at, country_code))")

find many -name '*.parquet' | wc -l && du -sh many

360
131M

bench("360 small files", "select country_code, sum(amount) from 'many/**/*.parquet' group by 1 limit 1")
bench("1 large file",    "select country_code, sum(amount) from 'orders_snappy.parquet' group by 1 limit 1")

360 small files                     0.688s   ('DE', 124993750.0)
1 large file                        0.142s   ('DE', 124993750.0)

360 files is 5× slower than one, on identical data. Scale that to a streaming job writing a file a minute — 43,200 files a month — and the per-file overhead dominates entirely. Compact on a schedule; aim for 128 MB to 1 GB per file.

Choosing

The default that serves most pipelines: accept whatever arrives, land it unchanged, and convert to Parquet as the first transformation step. Everything downstream then reads a typed, compressed, column-prunable file.

Practice

-rw-r--r-- 1 you you 412M orders.csv
-rw-r--r-- 1 you you  74M orders_zstd.parquet

parquet: 2 of 7 columns   0.142s
parquet: select *         1.884s

filter, unsorted file   0.104s
filter, sorted file     0.011s

360 small files   0.688s
1 large file      0.142s

Next: idempotency — making a pipeline safe to run twice.