Building a Data Lake Module 3: File Formats, Compression, and Partitioning

Step 1: Compare row and columnar formats

CSV and JSON are row-based text formats. They are easy to read and write but large and slow to scan, and CSV has no real types. Parquet and ORC are columnar: they store each column together, compress well, carry a schema, and let engines skip columns and row groups they do not need. Avro is row-based but compact with a strong schema, which suits streaming and message data. Use text formats at the edges and Parquet in the clean and curated zones.

Format   Layout    Schema   Best for
CSV      row       none     exchanging small files
JSON     row       loose    APIs, event payloads, raw landing
Avro     row       yes      streaming records, schema evolution
Parquet  columnar  yes      analytics queries (the lake default)
ORC      columnar  yes      analytics, common in Hive environments

Step 2: Convert CSV to Parquet

Converting is often the single biggest speed and cost improvement. Write Parquet with compression; snappy is a common fast default and zstd compresses more.

import pandas as pd

df = pd.DataFrame({
    "order_id": range(1, 6),
    "customer_id": [11, 12, 11, 13, 14],
    "amount": [120.5, 80.0, 45.25, 300.0, 99.9],
    "order_date": pd.to_datetime(["2026-09-01", "2026-09-01", "2026-09-02", "2026-09-02", "2026-09-03"]),
})

df.to_parquet("orders.parquet", compression="snappy", index=False)
print(pd.read_parquet("orders.parquet", columns=["order_id", "amount"]))   # reads only 2 columns

Step 3: Partition by the column you filter on

Partitioning splits a dataset into folders by column value, such as order_date=2026-09-02, so a query that filters on that column reads only the matching folders. This layout is called Hive-style partitioning, and Spark, Athena, Glue, and others understand it. Partition on columns that appear in most filters, usually a date.

df.to_parquet("orders_partitioned/", partition_cols=["order_date"], index=False)

# Resulting layout:
# orders_partitioned/order_date=2026-09-01/...parquet
# orders_partitioned/order_date=2026-09-02/...parquet
# orders_partitioned/order_date=2026-09-03/...parquet

Step 4: Avoid over-partitioning and small files

Too many partitions, such as partitioning by customer id, create huge numbers of tiny files, and listing and opening them costs more than the data. A good target is files of roughly 128 MB to 1 GB (check your engine's guidance) and partitions that each hold enough data to fill a few files. Partition by day or month rather than by second, and compact small files in a scheduled job.

Rule of thumb
- Partition columns: low cardinality, used in most WHERE clauses (date, region)
- Avoid partitioning by: user id, order id, timestamps with seconds
- Files: large and few beats small and many
- Compact small files regularly (Spark repartition/coalesce, or table format OPTIMIZE)

Step 5: Choose compression and schema evolution rules

Snappy is fast with a moderate size reduction, gzip and zstd shrink files more at some CPU cost, and gzip-compressed CSV or JSON cannot be split for parallel reads. Decide how you will handle schema changes: adding a column is usually safe, while renaming or changing a type breaks readers. Table formats such as Iceberg make these changes safe, which the next modules cover.