File formats, compression, and partitioning

Lesson 4 of 5 in Transformation Engines.

Layout is the highest-leverage decision in a data lake, and the reason is arithmetic rather than taste. AWS's own worked example: a 3 TB table of four equally sized columns stored as uncompressed text costs $15 to query on Athena at $5 per TB scanned. Gzipped at 3:1 it costs $5 — Athena still reads the whole file, but the file is a third of the size. As Parquet at the same compression ratio, a query referencing one of the four columns scans 0.25 TB and costs $1.25: 3x from compression and 4x from reading one column.

That is a twelvefold difference on identical data with identical queries, and it comes from two distinct mechanisms people often conflate.

Projection pushdown — column pruning — reads only the column chunks a query names. A row-oriented format cannot do it at all, because reaching one field means traversing the row.

Predicate pushdown uses the min/max statistics stored per row group or stripe to skip blocks that cannot contain a matching row. A filter on price > 1000 skips every row group whose recorded maximum is 900.

The second one has a condition attached that almost nobody configures, and it is worth knowing because it is free: predicate pushdown depends on data locality within the files. If the filtered column's values are scattered randomly, every row group's min/max spans the whole range and not one block can be skipped. Sort on the commonly filtered column while writing and the same predicate skips most of them. Same format, same codec, same bytes on disk, dramatically faster queries.

Splittability is a separate axis from cost, and the one that produces the "we compressed it and the job got slower" incident.

Spark, Athena and Hive parallelise by handing each task a byte range of a file, which only works if a reader can start decoding at an arbitrary offset. Gzip cannot. A 20 GB .csv.gz object is read by exactly one task, on one core, however large the cluster. The job is not broken; it is serial.

What is splittable: bzip2 (slow but splittable), LZO when indexed, and — importantly — Snappy or gzip inside Parquet or ORC, because the container splits at row-group or stripe boundaries even though each block's codec does not. That resolves the apparent paradox that "gzip is not splittable" and "gzip-compressed Parquet is fine" are both true.

Two practical rules follow. Many moderate gzip files parallelise fine, because the unit of parallelism becomes the file — 200 × 100 MB objects are far better than one 20 GB object holding the same bytes. And prefer a columnar format with Snappy for anything analytical.

The row-oriented format worth knowing properly is Avro, and it is not a worse Parquet — it is for a different job. Avro serialises whole records compactly with the schema available to the reader, and defines explicit compatibility rules for evolving that schema. So Avro for write-heavy and streaming paths, Parquet or ORC for read-heavy analytical ones. Writing one record to Avro costs one record's work; writing it to Parquet means building column chunks and statistics, which is why streaming sinks buffer before converting. You will often see both in one pipeline: Avro or JSON on the stream and in the raw zone, Parquet in the curated zone.

Interactive sorting exercise: Each card is a requirement. Sort it into the layout decision it actually determines — the point of the exercise is that "use Parquet" is the answer to only one of these four questions.

Key terms: Apache Parquet, Apache ORC, Apache Avro, Splittable compression, Predicate pushdown, Partition pruning, Over-partitioning