Row Groups and Predicate Pushdown, plus Parquet vs ORC vs Avro
Inside a Parquet file: row groups, the footer, and the min/max stats that let a filter skip whole row groups without reading them. Then when a row format (Avro) is the right call.
Inside a Parquet file: row groups and the footer
A Parquet file is not one undifferentiated blob. It is split into row groups, each holding a horizontal slice of the rows (a common default row-group size is 128 MB in the Parquet Java library, though the Apache docs recommend 512 MB to 1 GB, and engines tune it). Inside a row group, each column's values for those rows are stored together as a column chunk, and each chunk is split into pages. At the very end of the file sits the footer: the schema, and for every row group and every column, statistics including the minimum, maximum, and null count.
A reader opens the footer first, because the footer is the map. That map is what makes the next trick possible.
- Open footerschema + per-row-group stats
- Check min/maxdoes this row group overlap the filter?
- Skip non-overlappingread none of its bytes
- Read matching chunksonly the needed columns
What columnar buys you (part 3: predicate pushdown)
Because the footer records each row group's min and max for a column, a filter can skip entire row groups without reading their data. If a query says WHERE order_id BETWEEN 150000 AND 250000 and a row group's stored range is 1 to 100000, its maximum (100000) is below the filter's floor, so the reader knows nothing in it can match and skips all of its bytes. This is predicate pushdown, and combined with partition pruning (the next module) it is how a query over a huge table reads only a sliver.
Pushdown works best when the data is sorted or clustered on the filter column, so each row group covers a tight, non-overlapping range. If every row group spans the whole range of values, no group can be skipped and the min/max stats buy you nothing.
Parquet, ORC, and Avro
Parquet is not the only format, and interviews like to check that you know when a row format is right.
| format | layout | best for |
|---|---|---|
| Parquet | columnar | analytics scans, the lake default |
| ORC | columnar | analytics in the Hive/Trino world, strong compression |
| Avro | row-oriented | streaming/Kafka, row-by-row writes, schema evolution |
Parquet and ORC are both columnar and both great for analytics (ORC is strong in the Hive and Trino world). Avro is row-oriented: it stores whole records together, which is the wrong shape for scanning a few columns but the right shape for streaming ingestion, row-by-row writes, and schema evolution. A very common pipeline ingests events as Avro (cheap to append record by record from Kafka) and then compacts them into Parquet for the analysts to scan.
This is also where schema evolution matters: schemas change over time as new fields are added. Adding a column is a backward-compatible change, because old files simply have no data for it and a reader returns NULL, which is why a columnar-plus-catalog table tolerates a new or missing column. Renaming or dropping a column is riskier and needs more care, and Avro's built-in schema handling is one reason it is favored for evolving streaming data.
Measuring pushdown with SQL
A row group's min and max are just columns you can filter on. The exercises query a row_group_stats table and compute how many row groups (and bytes) a range filter must read versus the whole file, which is exactly what predicate pushdown does under the hood.
Common mistake: expecting predicate pushdown to help on unsorted data. If every row group spans the whole range of values, no group can be skipped and the min/max stats buy you nothing, which is why clustering on the filter column matters.
Interview nuance: "how does a query skip data it does not need" is answered at two levels: partition pruning skips whole directories by the partition key, and predicate pushdown skips row groups inside a file by their min/max stats. Adding "and column projection skips columns you did not select" gives the complete picture of why a columnar lake query is cheap.
On a real platform this differs. Real engines read these min/max stats from the Parquet footer (and per-page indexes) and decide row-group skipping automatically; you never write the skip logic. The
row_group_statstable here exposes those stats as rows so you can compute the skip yourself and see what the engine sees.
CREATE TABLE row_group_stats (
rg_id INTEGER,
row_count INTEGER,
min_order_id INTEGER, -- the file is sorted by order_id, so each row group
max_order_id INTEGER, -- covers a tight, non-overlapping range
compressed_bytes INTEGER
);
INSERT INTO row_group_stats (rg_id, row_count, min_order_id, max_order_id, compressed_bytes) VALUES
(0, 100000, 1, 100000, 52000000),
(1, 100000, 100001, 200000, 53000000),
(2, 100000, 200001, 300000, 51000000),
(3, 100000, 300001, 400000, 54000000),
(4, 60000, 400001, 460000, 31000000);-- For WHERE order_id BETWEEN 250000 AND 350000, which row groups does pushdown read?
SELECT rg_id, min_order_id, max_order_id,
ROUND(compressed_bytes / 1000000.0, 0) AS mb,
CASE WHEN max_order_id >= 250000 AND min_order_id <= 350000 THEN 'read' ELSE 'skip' END AS pushdown
FROM row_group_stats
ORDER BY rg_id;Apply
Your turn
The task this lesson builds to.
Write a query that returns how many row groups a WHERE order_id BETWEEN 150000 AND 250000 filter must read and their total compressed MB, as (row_groups_read, mb_read), over row_group_stats(rg_id, row_count, min_order_id, max_order_id, compressed_bytes).
A row group must be read when its stored [min_order_id, max_order_id] range overlaps [150000, 250000]. Treat 1 MB as 1,000,000 bytes and round mb_read to 2 decimals.
3 hints and 1 automated check are waiting in the workspace.
Practice
Make it stick
A second problem on the same idea, plus 4 bonus drills.
Write a query that returns, for the same order_id BETWEEN 150000 AND 250000 filter, the compressed MB a pushdown-aware reader reads, the whole file's MB, and the percentage of bytes skipped, as (mb_read, mb_total, pct_skipped), over the same row_group_stats table.
Read a row group only when its range overlaps the filter. Treat 1 MB as 1,000,000 bytes and round every column to 2 decimals.
3 hints and 1 automated check are waiting in the workspace.