Data Lakes
Intermediate 13 min read Data Lakes

Lake Optimization

The file is your query plan

Lakes get optimized the way files do: sort/write aligned with predicates, viable file sizes, compaction to kill tiny files, stats honesty, and column projection. Small files are the universal first evil.

small filescompactionsortclusteringstatspruning
Prerequisites: Open Formats Parquet Deep Dive
Before we start

What you will learn

  • Diagnose the small-files disease and cure with compaction
  • Align sorting with predicate columns for pruning
  • Right-size files and row groups for parallelism
Introduction

What is this?

Lake query speed is a file-layout problem: few big, sorted, stats-honest Parquet files beat millions of tiny unsorted ones. Optimization = right-size files, sort by predicate columns, compact after bursts, keep statistics sane, and project columns — usually re-runnable 'bin-packing' jobs over raw output.

Motivation

Why do you need it?

  • A well-optimized lake can match a warehouse; a neglected one pays 100x scan cost.
  • Compaction and sorting are cheap, idempotent operations that pipelines can run daily.
  • Metadata/stats honesty directly translates to 'scans fewer bytes per query'.
Think about it

Mental model

Imagine

Shoeboxes vs. a closet: all socks in one issue vs one sock per drawer

Think of

File sizing

Thousands of one-sock drawers (tiny files) means opening hundreds of drawers per lookup; a few sorted shoeboxes (compacted files) lets you pull the right drawer and grab the exact color (sorted range) without opening everything.

Mechanics

How it works

  • Targets: 128MB-1GB per Parquet file, row groups aligned to scan entry points.
  • Sort/rewrite by the dominant predicate so lower/upper bound stats become true range filters.
  • Compaction merges small files into larger ones and can re-sort (OPTIMIZE / Z-order / rewrite).
  • Statistics honesty: sorted and stats-bearing files let engines skip whole files/row groups.
  • Projection discipline: narrow schemas and explicit column selection cut every scan.

The small-files-to-compacted pipeline.

Stream dumps
Compaction job
Big sorted files
Predicate pushdown
Queries
Reference

Key commands

duckdb -c "SELECT count(*), round(avg(size)/1024/1024,1) mb FROM parquet_metadata('lake/**/*.parquet');"

Count files and average size — the small-files diagnostic.

duckdb -c "COPY (SELECT * FROM read_parquet('lake/**/*.parquet') ORDER BY ts) TO 'lake/opt/out.parquet' (FORMAT PARQUET, ROW_GROUP_SIZE 122880);"

One-pass sort+coalesce into bigger, prunable files.

Hands-on

Examples

Sort + compact in one rewrite

Sort + compact in one rewrite
-- Every night: rewrite by predicate column and coalesce files
OPTIMIZE lake.orders ZORDER BY (ts);    -- delta/iceberg style, sorts + compacts
 
-- DuckDB analog, same idea:
COPY (
  SELECT * FROM read_parquet('lake/raw/orders/*.parquet')
  ORDER BY ts, customer_id
) TO 'lake/curated/orders/orders.parquet' (FORMAT PARQUET, ROW_GROUP_SIZE 122880);

Compaction AND sorting are the same job when you rewrite a partition; the sorted output's stats finally work for the filter.

Real world

In production

  • A nightly streaming-dump compaction merges 40k tiny files into 120 big sorted ones before BI opens.
  • A monthly 'rewrite orders by ts' keeps the quarter-end report scans under budget.
  • A stats campaign re-writes unsorted tables once, then predicate pushdown starts working.
Guidelines

Best practices

  • Tie compaction cadence to write volume: the busier the writes, the more often you compact.
  • Sort by the column that appears in most queries (usually a timestamp), then a cardinal discriminator.
  • Keep row groups ~128MB-equivalent; more parallelism splits files, not pages.
  • Budget storage for the duplicate copies during rewrite and set a retention to free them.
Watch out

Common mistakes

  • Letting 100k small files accumulate 'because we delete later' — the delete itself becomes the slow twin.
  • Sorting by a column nobody filters on, buying nothing.
  • Compacting without bounding the process, doubling one partition's storage indefinitely.
When things break

Debugging tips

  • Slow scan = inspect file counts/sizes first; tiny files explain themselves.
  • A filter that still reads everything = unsorted data (or a wrapped predicate); re-sort by the filter column.
  • Compare bytes read per query profile before/after compaction to prove the win.
Interviews

Interview checkpoint

Practice

Hands-on lab

Diagnose and Fix the Small-Files Epidemic
30 min · intermediate
In progress
Objectives
  • Generate thousands of tiny parquet files to simulate streaming dumps
  • Measure file count and average size with parquet_metadata
  • Compact + sort into a few big files and confirm the difference

Diagnose and Fix the Small-Files Epidemic

30 min · intermediate

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

Quick quiz

0/2
1

The first optimization most lakes need is…

2

Sorting data by the column you filter on makes Parquet row-group statistics actually exclude irrelevant groups.

Go deeper

Official documentation

Iceberg maintenance (rewrite/compact/expire)
docs
Delta optimize & vacuum
docs
DuckDB parquet performance
docs