Data file formats basics: CSV vs JSON vs Parquet vs Avro, and which one to use when

The file format you land data in decides how fast queries run, how much storage costs and how often pipelines break on a schema change. You do not need to know the internals; you need to know which to choose.

#The short version

Lakehouse table formats (Delta Lake, Iceberg, Hudi) store their data as Parquet and add a transaction log on top.

#Why columnar matters

A query like SELECT sum(amount) FROM sales WHERE year = 2026 only needs two columns. In Parquet, the engine reads just those columns and can skip whole row groups using min/max statistics. In CSV, it must read and parse every byte of every row.

Typical results: Parquet files are several times smaller than the same CSV and queries scan a fraction of the data. Exact ratios depend on the data, so measure your own.

#The trouble with CSV

• No types: is 00123 a number or a string? Is 03/04/2026 March or April?
• Delimiters and quotes inside values break naive parsers.
• No schema to detect a new or missing column.
• Encoding problems (UTF-8 vs Windows-1252) show up as broken characters.

If you must accept CSV, convert it to Parquet as the first step and enforce a schema there.

#JSON and JSON Lines

JSON Lines (one JSON object per line) is the friendly version for data pipelines: files can be split and appended. Plain JSON arrays must be read whole.

JSON is good at nested and changing structures, which is why APIs and logs use it. It is slow and large for analytics, so flatten it into Parquet or a Delta table early.

#Avro for streaming

Avro stores the schema with the data and has clear rules for evolving it (add a field with a default, and old and new readers keep working). With a schema registry, Kafka messages carry just a schema ID, keeping messages small. It is row-based, which suits writing one event at a time, and is less efficient than Parquet for analytical scans.

#Look inside files

Python with pyarrow:

import pyarrow.parquet as pq
pf = pq.ParquetFile("sales.parquet")
print(pf.schema_arrow)
print(pf.metadata)                         # row groups, sizes, compression
print(pf.metadata.row_group(0).column(0).statistics)

DuckDB, straight from the command line:

SELECT * FROM 'sales.parquet' LIMIT 5;
DESCRIBE SELECT * FROM 'sales.parquet';
SELECT * FROM parquet_metadata('sales.parquet');
SELECT count(*) FROM read_json_auto('events.jsonl');

#Convert

DuckDB, CSV to Parquet with explicit types:

COPY (
  SELECT * FROM read_csv('sales.csv', header=true,
         columns={'order_id':'BIGINT','amount':'DECIMAL(10,2)','order_date':'DATE'})
) TO 'sales.parquet' (FORMAT parquet, COMPRESSION zstd);

Spark:

df = (spark.read.option("header", True)
      .schema("order_id BIGINT, amount DECIMAL(10,2), order_date DATE")
      .csv("/landing/sales/*.csv"))
df.write.mode("overwrite").partitionBy("order_date").parquet("/bronze/sales")

pandas:

import pandas as pd
df = pd.read_json("events.jsonl", lines=True)
df.to_parquet("events.parquet", index=False)

Tip

Always pass an explicit schema when reading CSV in production. Schema inference reads a sample, guesses, and then a later file with a different value breaks the job.

#Sizing Parquet files

• Aim for files of roughly 128 MB to 1 GB. Thousands of tiny files slow every query (the "small files problem").
• Do not over-partition: a folder per day is usually fine; a folder per customer per hour usually is not.
• Compression: snappy is the common default; zstd gives smaller files at similar speed on modern engines.

#Which to choose

#Prevent it next time

• Convert to Parquet with an enforced schema at the first hop.
• Keep raw files as they arrived (for replay), but never query them directly in reports.
• Agree a file contract (format, schema, encoding, delimiter) with every external sender.