Data Lakes
Intermediate 14 min read Data Lakes

Open Formats: Parquet, Avro, ORC

The file is the contract

Open columnar formats (Parquet, ORC) and row formats (Avro) give lakes query speed and interoperability. Writing side/predicate side decisions — compression, sorting, stats — control how fast any engine can read your files.

ParquetAvroORCcolumnarstatscompressionschema evolution
Prerequisites: Object Storage
Before we start

What you will learn

  • Contrast columnar (Parquet/ORC) vs row (Avro) layout
  • Explain row-groups, pages, and metadata statistics
  • Care about sorting/compression as a read-performance decision
Introduction

What is this?

Open formats standardize how data is laid out and read across engines (Spark, Trino, DuckDB, Iceberg). Parquet and ORC store data column-wise in row groups, embedding statistics and compression — columnar engines scan only needed column strips. Avro stores schema'd rows, better for streaming and schemas that evolve by field.

Motivation

Why do you need it?

  • REQUIRED slug: de-foundations links here as the reference for open lake formats.
  • A columnar file's pruning + projection are the lake's answer to warehouse speed.
  • The format is written once and read by every tool — get the file shape right to unlock all of them.
Think about it

Mental model

Imagine

Filing a spreadsheet by columns into labeled drawers

Think of

Columnar storage (Parquet)

To answer 'average of column C', you open only C's drawer — not every row's folder. A row-store (Avro) is a binder of full records: reading is simpler, scanning one field still costs all the pages.

Mechanics

How it works

  • Columnar: each column of a row group is a compact 'column chunk' of pages; predicates use min/max stats.
  • Projection: only the requested column strips are decompressed and read.
  • Compression (snappy/zstd) trades CPU for I/O; zstd compresses better, snappy is faster at write.
  • Nested types (lists, structs) and repeated columns encode via repetition/definition levels.
  • Schema evolution: adding a column is backward compatible for reading old files; projection fills in.

Reading one column from one row group.

File metadata
Row group
Column chunk
Dictionary/bitpack pages
Values
Reference

Key commands

python3 -c "import pyarrow as pa; import pyarrow.parquet as pq; print(pq.read_metadata('f.parquet').num_row_groups)"

Inspect row-group layout and stats of a Parquet file.

duckdb -c "SELECT * FROM parquet_schema('f.parquet');"

See the embedded schema (columns, types, logical types).

duckdb -c "COPY t TO 'out.parquet' (FORMAT PARQUET, COMPRESSION zstd);"

Write Parquet with zstd compression.

Hands-on

Examples

Sorted data = better stats = cheaper scans

Sorted data = better stats = cheaper scans
-- In DuckDB (or Spark) sorting before writing matters:
COPY (
  SELECT * FROM raw_events
  ORDER BY ts          -- sorted by predicate column
) TO 'lake/curated/events.parquet' (FORMAT PARQUET);
 
-- Now readers use min/max stats of run lengths:
SELECT count(*) FROM read_parquet('lake/curated/events.parquet')
WHERE ts >= '2026-03-01';   -- stats skip row groups that can't match

Sorted rows make per-page min/max statistics meaningful — predicate pushdown removes whole row groups.

Real world

In production

  • An events pipeline writes daily, ts-sorted Parquet; BI and ML read the same files with no conversion.
  • A streaming pipeline lands Avro first (fast, schema'd rows) and a batch job converts to Parquet for queries.
  • An engine-agnostic team publishes tables as Parquet so Snowflake, Trino, and DuckDB all read identically.
Guidelines

Best practices

  • Sort/write by the column you'll filter on so stats pay off.
  • Choose zstd for most lakes, snappy when write CPU is the constraint.
  • Keep row groups ~128MB-1GB and avoid micro-files polluted by tiny groups.
  • Evolve schemas by adding columns, not renaming/reordering; readers behave predictably.
Watch out

Common mistakes

  • Writing millions of 1KB Parquet files: listing cost and row-group overhead crush readers.
  • Expecting predicate pruning on unsorted data; random order means stats never exclude anything.
  • Mixing Avro rowwise-GETs into columnar query paths and paying decompress of whole rows.
When things break

Debugging tips

  • A 'scan is reading everything' pattern: check row-group stats and whether data is aligned with the predicate.
  • Schema mismatch errors usually mean 'old file + new schema' — inspect the file's embedded schema.
  • Slow cold reads often point at too many tiny files/row groups for the engine's parallelism.
Interviews

Interview checkpoint

Practice

Hands-on lab

Write, Inspect, and Read Parquet
35 min · intermediate
In progress
Objectives
  • Write a sorted Parquet file with DuckDB
  • Inspect row groups and embedded statistics
  • Demonstrate predicate pushdown skipping work

Write, Inspect, and Read Parquet

35 min · intermediate

0/5 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/2
1

Which format layout is best for columnar analytics scans?

2

Parquet can embed min/max statistics per row group that engines use for predicate pushdown.

Go deeper

Official documentation

Apache Parquet spec
docs
Apache Avro spec
docs
ORC specification
docs