File Formats: Parquet, ORC & Avro

By the end of this lesson you will be able to explain the difference between row-based and column-based files, look inside a real Parquet file, describe how Spark skips data using file statistics, and pick the right format for each stage of a pipeline.

The file format you choose can make a query ten times faster or slower, and storage several times cheaper or dearer, without changing a single line of your Spark logic. That makes it one of the cheapest wins in data engineering.

Row-based and column-based storage

A table can be written to disk in two basic ways.

• Row-based: all the values of row 1, then all of row 2, and so on. CSV, JSON and Avro work like this.
• Column-based (columnar): all the values of column 1, then all of column 2. Parquet and ORC work like this.

Think of a class register. A row-based file is like one card per student with all their marks. A columnar file is like one sheet per subject with every student's marks. To find the class average in Maths, the subject sheets are far quicker: you read one sheet, not every card.

Analytics queries usually read a few columns from many rows ("total revenue per city"), so columnar formats win. Writing one event at a time, or reading whole records, favours row formats.

rows = [
    ("Aarav", "Pune", 1299),
    ("Priya", "Kochi", 499),
    ("Kabir", "Pune", 2450),
]
row_layout = [value for row in rows for value in row]
col_layout = [value for col in zip(*rows) for value in col]
print("row-based   :", row_layout)
print("column-based:", col_layout)

# to sum the amounts, a columnar reader touches one contiguous block
amount_block = col_layout[6:9]
print("amount block:", amount_block, "sum =", sum(amount_block))

row-based   : ['Aarav', 'Pune', 1299, 'Priya', 'Kochi', 499, 'Kabir', 'Pune', 2450]
column-based: ['Aarav', 'Priya', 'Kabir', 'Pune', 'Kochi', 'Pune', 1299, 499, 2450]
amount block: [1299, 499, 2450] sum = 4248

Storing a column together has a second benefit: similar values sit next to each other, so they compress very well. A city column with only a few different names shrinks to almost nothing.

Inside a Parquet file

A Parquet file is organised in layers:

1. The file is split into row groups (blocks of rows, often around 128 MB).
2. Inside each row group, each column is stored as a column chunk.
3. A footer at the end holds the schema and statistics for every column chunk: minimum, maximum and null count.

Those statistics let Spark skip data. If a query asks for amount > 5000 and a row group's maximum amount is 3,000, Spark never reads that row group. This is predicate pushdown into the file, and you saw it in plans as PushedFilters.

Step 1: write a Parquet file and read its footer

PyArrow is the Python library that pandas uses for Parquet. It runs here in the browser:

import os
import pandas as pd
import pyarrow.parquet as pq

orders = pd.DataFrame({
    "order_id": range(1, 10_001),
    "city": ["Pune", "Kochi", "Jaipur", "Lucknow"] * 2_500,
    "amount": [(i * 37) % 5000 + 100 for i in range(10_000)],
})
orders = orders.sort_values("amount", ignore_index=True)   # sorting makes statistics more useful

orders.to_csv("orders.csv", index=False)
orders.to_parquet("orders.parquet", row_group_size=2_500)
print("CSV size    :", os.path.getsize("orders.csv"), "bytes")
print("Parquet size:", os.path.getsize("orders.parquet"), "bytes")

meta = pq.ParquetFile("orders.parquet").metadata
print("row groups  :", meta.num_row_groups)
for i in range(meta.num_row_groups):
    stats = meta.row_group(i).column(2).statistics      # column 2 = amount
    print(f"  group {i}: amount min {stats.min:>4}, max {stats.max:>4}")
print("compression :", meta.row_group(0).column(0).compression)

CSV size    : 162115 bytes
Parquet size: 93316 bytes
row groups  : 4
  group 0: amount min  100, max 1349
  group 1: amount min 1350, max 2599
  group 2: amount min 2600, max 3849
  group 3: amount min 3850, max 5099
compression : SNAPPY

The Parquet file is a little over half the CSV size, even on this tiny table with an order_id column that barely compresses. On real data with many repeated values the saving is usually much larger. Each row group also knows its own amount range. A filter such as amount > 4000 needs only group 3; the other three can be skipped using the footer alone. Sorting the data by a commonly filtered column before writing made the ranges tidy. Without sorting, every group would contain amounts from 100 to 5,099 and none could be skipped.

Step 2: read only what you need

import pandas as pd
import pyarrow.parquet as pq

pd.DataFrame({
    "order_id": range(1, 10_001),
    "city": ["Pune", "Kochi", "Jaipur", "Lucknow"] * 2_500,
    "amount": sorted((i * 37) % 5000 + 100 for i in range(10_000)),
}).to_parquet("orders.parquet", row_group_size=2_500)

table = pq.read_table("orders.parquet", columns=["city", "amount"],
                      filters=[("amount", ">", 4000)])
print(table.num_rows, "rows,", table.num_columns, "columns read")
print(table.to_pandas().groupby("city")["amount"].sum())

2198 rows, 2 columns read
city
Jaipur     2502500
Kochi      2497950
Lucknow    2502500
Pune       2497950
Name: amount, dtype: int64

columns= reads just two column chunks per row group, and filters= uses the statistics to skip row groups and then removes non-matching rows. Spark does the same automatically when you write spark.read.parquet(path).filter(F.col("amount") > 4000).select("city", "amount").

The formats compared

A common pipeline uses several: raw events land as JSON or Avro (easy to write), then a Spark job converts them into partitioned Parquet (fast to read), as you will do in the project.

Formats in Spark

df.write.mode("overwrite").option("compression", "zstd").parquet("lake/orders_parquet")
df.write.mode("overwrite").orc("lake/orders_orc")
df.write.mode("overwrite").format("avro").save("lake/orders_avro")

Parquet and ORC are built in. Spark's Parquet files use Snappy compression by default; zstd usually compresses smaller for a little more CPU. Avro support is an external module: start Spark with the spark-avro package matching your Spark and Scala version, for example --packages org.apache.spark:spark-avro_2.13:4.3.0.

Common mistakes

• Keeping analytics data in CSV. Convert to Parquet once and every later query is faster.
• Writing many tiny Parquet files. Each file has a footer and metadata overhead; aim for large files.
• Expecting statistics to skip data on unsorted columns. Sort or cluster by common filter columns.
• Using Avro for dashboards that read three columns out of fifty.

Key points

• Row formats (CSV, JSON, Avro) store records together; columnar formats (Parquet, ORC) store columns together.
• Columnar files read only the needed columns and compress very well.
• Parquet row groups carry min/max statistics, so filters can skip whole groups.
• Sorting by a filter column before writing makes those statistics much more effective.
• Land raw data as JSON or Avro, store analytics data as partitioned Parquet.

import pandas as pd
import pyarrow.parquet as pq

orders = pd.DataFrame({
    "order_id": range(1, 10_001),
    "city": ["Pune", "Kochi", "Jaipur", "Lucknow"] * 2_500,
    "amount": [(i * 37) % 5000 + 100 for i in range(10_000)],
})
orders.to_parquet("unsorted.parquet", row_group_size=2_500)
meta = pq.ParquetFile("unsorted.parquet").metadata
for i in range(meta.num_row_groups):
    s = meta.row_group(i).column(2).statistics
    print("amount", i, s.min, s.max)

orders.sort_values("city").to_parquet("by_city.parquet", row_group_size=2_500)
meta = pq.ParquetFile("by_city.parquet").metadata
for i in range(meta.num_row_groups):
    s = meta.row_group(i).column(1).statistics
    print("city", i, s.min, s.max)

amount 0 100 5099
amount 1 101 5098
amount 2 100 5099
amount 3 101 5098
city 0 Jaipur Jaipur
city 1 Kochi Kochi
city 2 Lucknow Lucknow
city 3 Pune Pune