Storage: Warehouses, Lakes, Lakehouses & File Formats

Where and how data is stored for analytics. Learn row vs columnar storage, file formats (CSV, JSON, Avro, Parquet, ORC), compression and encoding (dictionary, run-length, delta), partitioning, clustering and pruning, the small-files problem, data warehouses (BigQuery, Snowflake, Redshift), data lakes on object storage, open table formats (Iceberg, Delta Lake, Hudi) with ACID transactions and time travel, lakehouse architecture, caching and materialised views, and storage cost optimisation — with Python comparing row and column scans, run-length encoding and partition pruning.

Analytical queries usually read a few columns across millions or billions of rows. Storing data by column, compressing it and organising it into partitions lets engines read a tiny fraction of the data — making queries faster and cheaper.

Modern platforms combine cheap object storage with open table formats that add database-like reliability: the lakehouse.

An analogy

A row store is like a filing cabinet of complete forms — great for finding one person's full record. A column store is like having separate binders per question — to average everyone's age you pull out just the 'age' binder instead of every form.

File formats

Common formats:

Warehouses vs lakes

Cloud data warehouses (BigQuery, Snowflake, Redshift, Synapse) provide managed columnar storage, SQL, concurrency, security and separation of storage and compute. Data lakes store any files cheaply in object storage (S3, GCS, ADLS) — flexible but historically lacking transactions and governance ('data swamps'). Lakehouses add open table formats on lakes to get warehouse-like reliability and performance while keeping open files usable by many engines (Spark, Trino, Flink, DuckDB).

Layout optimisation

Partition by columns commonly filtered (date, region) — but not by high-cardinality keys (creates millions of tiny partitions). Cluster/sort within partitions (Z-ordering, clustering keys) so min/max statistics skip files. Compact small files regularly. Use materialised views and aggregates for repeated heavy queries; caching and result reuse cut costs.

Open table formats

Apache Iceberg, Delta Lake and Apache Hudi keep metadata (manifests, transaction logs) over Parquet files to provide ACID transactions, schema and partition evolution, time travel (query old snapshots), MERGE/upserts, and concurrent writers. Catalogs (Glue, Unity, Polaris, Nessie) manage tables and access. Governance: row/column-level security, encryption, retention and deletion for privacy compliance (right to be forgotten).

Formulas

Worked example: Query cost

Given: Query scans 2 TB at $6.25/TB

Find: Cost.

Answer: $12.50 — partitioning to 50 GB would cost $0.31

Worked example: Small files

Given: 10 GB stored as 100 000 files

Find: Average file size.

Answer: 100 KB — compact to ~128 MB+

Worked example: Dictionary encoding

Given: 1 M rows of a 20-byte string with 100 distinct values

Find: Encoded size (2-byte codes).

Answer: ≈ 2 MB vs 20 MB

Row vs column scans, RLE and pruning

# Row vs columnar scans, run-length encoding and partition pruning
rows, cols, bytes_per_value = 10_000_000, 50, 8
query_cols = 3
row_scan = rows * cols * bytes_per_value / 1e9
col_scan = rows * query_cols * bytes_per_value / 1e9
print(f"row store scans {row_scan:.1f} GB, column store scans {col_scan:.2f} GB ({row_scan/col_scan:.0f}x less)")
# Run-length encoding of a sorted low-cardinality column
city = ["Delhi"] * 4 + ["Mumbai"] * 3 + ["Pune"] * 5
rle, prev, count = [], None, 0
for v in city:
    if v == prev: count += 1
    else:
        if prev is not None: rle.append((prev, count))
        prev, count = v, 1
rle.append((prev, count))
print("RLE:", rle, f"-> {len(city)} values stored as {len(rle)} runs")
# Partition pruning: data partitioned by day, query for 7 of 365 days
print(f"partition pruning reads {7/365:.1%} of the table")
# row store scans 4.0 GB, column store scans 0.24 GB (17x less)
# RLE: [('Delhi', 4), ('Mumbai', 3), ('Pune', 5)] -> 12 values stored as 3 runs
# partition pruning reads 1.9% of the table

Reading 3 of 50 columns cuts I/O 17×; sorted, repetitive columns compress dramatically with run-length and dictionary encoding; and partitioning by date means a 'last 7 days' query touches under 2% of the files. Combined, a query may read 1000× less than a naive scan — which is how warehouses answer billion-row questions in seconds (and why on-demand costs depend on bytes scanned).

Common mistakes

Applications

Key takeaways

Going further