Quantifying the Parquet and Partitioning Lever

Level 10: Distributed Compute & Data Operationsmedium26 mincolumn pruning arithmeticpartition pruning arithmeticCTAS conversionsmall-files problemtarget file sizesrequest-cost arithmetic

Compute, rather than assert, the cost gap between scanning raw CSV and a partitioned Parquet layout, then price the small-files problem that erodes it in dollars per month.

The most repeated answer in the room

Ask any data engineering interviewer for the exam answer they are tired of hearing and it is "convert it to Parquet and partition it". The reason they still ask is that most candidates can say it and almost none can size it. Saying it is worth nothing. Multiplying it out in front of the interviewer is worth a lot, because the same arithmetic is what you would put in the ticket.

Two independent prunings do the work, and they multiply.

Column pruning. Parquet stores each column's values together, so reading three columns of a twenty-column table reads only those three columns' bytes. A wide free-text column can be a third of the file on its own, which is why the share of bytes you skip is usually much larger than the share of columns you skip.
Partition pruning. Hive-style directories (dt=2026-02-20/) let the engine match your WHERE clause against the path and never open the other directories. One day out of twenty is one twentieth of the bytes.

Row-oriented CSV can do neither. There are no column boundaries to seek to and no partition directories to skip, so every read is a full read.

Table

The two prunings are independent, so they multiply. That product is the whole cost lever.
layout	partition pruning	column pruning	bytes for 1 day, 3 of 20 columns
raw CSV, unpartitioned	none, one flat prefix	none, rows are interleaved	the whole table
Parquet, unpartitioned	none	yes, only the 3 columns' bytes	3 columns of every day
Parquet, partitioned by dt	yes, 1 directory of 20	yes	3 columns of 1 day
Parquet + partitioned + compacted	yes	yes	same bytes, far fewer requests

The two prunings are independent, so they multiply. That product is the whole cost lever.

The published case studies land around a 96.7 percent cost reduction for a raw-CSV to partitioned-Parquet conversion, and the standard way to perform the conversion is CTAS: CREATE TABLE events_parquet WITH (format = 'PARQUET', partitioned_by = ARRAY['dt']) AS SELECT ... FROM raw_events_csv. One statement, and from then on the same logical question costs a fraction.

Then it erodes

A converted table does not stay fast on its own. Whatever writes into it decides the file layout, and streaming sinks and small hourly jobs write many small objects. Now every query pays a per-file cost: one GET request per file, plus planning time to list and open them all. The industry target is 128 MB to 1 GB per file, and the fix is compaction, rewriting the partition's many small files into a few big ones.

Level 7 already had you produce a compaction candidate list from file counts and average sizes. This lesson deliberately does not repeat that. It prices the same condition instead, because "partition 2026-02-24 has 4,500 files averaging 8 MB" is an engineering observation and "the small files in this partition cost us $2 a month in requests alone, before planning time" is the version that survives a FinOps review and gets scheduled.

There is a third lever, and it gets the same treatment. Level 6 taught the codecs: Snappy is fast at a modest ratio, Gzip is smaller and slower, Zstd lands near Gzip's size at near Snappy's speed. That was a storage fact, measured as a shrink ratio. On a bytes-scanned meter it is also a price, because whatever fraction of the bytes the codec leaves behind is a straight multiplier on every query against that table: leave 35 percent of the bytes and you take 65 percent off the cost of every full scan. The hard drill below is exactly that conversion, from a codec's size ratio to dollars.

Common mistake: quoting the savings percentage without the denominator. "Ninety-nine percent cheaper" means nothing until you say cheaper than what, on which query. State the baseline scan, the pruned scan, and the query shape you measured, or the number is unfalsifiable.

Interview nuance: if you are asked to optimize a slow, expensive table, give the levers in the order they pay: file format first (column pruning applies to every query), then partitioning on the column people actually filter by, then file size. Naming the file-size target out loud, 128 MB to 1 GB, is the detail that says you have operated a lake and not only read about one.

On a real platform this differs. Here you compute the scan from catalog metadata in SQLite. On AWS, Athena reports the actual bytes scanned per query and Glue holds the partition and column statistics; you would sanity-check your estimate against the real reading. BigQuery prunes by clustering and partitioning and shows a dry-run byte estimate before you run; Snowflake prunes on micro-partition min/max metadata instead of directories. The arithmetic of "share of columns times share of partitions" survives all three.

Sample data for this example

CREATE TABLE parquet_columns (
  column_name  TEXT,
  pct_of_bytes REAL      -- this column's share of the file's bytes; the 20 columns sum to 100
);
INSERT INTO parquet_columns (column_name, pct_of_bytes) VALUES
  ('event_id',     3.0),
  ('event_ts',     4.0),
  ('user_id',      5.0),
  ('session_id',   6.0),
  ('event_type',   2.0),
  ('page_url',    12.0),
  ('referrer_url', 9.0),
  ('user_agent',  11.0),
  ('device_type',  1.5),
  ('os_name',      1.5),
  ('browser',      2.0),
  ('country',      1.0),
  ('region',       1.5),
  ('city',         2.5),
  ('revenue_usd',  1.0),
  ('currency',     0.5),
  ('campaign_id',  3.0),
  ('ab_variant',   0.5),
  ('ingest_batch', 2.0),
  ('raw_payload', 31.0);

Worked example (SQL)

-- The column byte profile: four of twenty columns are 63% of the file, which is why
-- selecting the columns you need is the cheapest optimization in the building.
SELECT column_name,
       pct_of_bytes,
       ROUND(SUM(pct_of_bytes) OVER (ORDER BY pct_of_bytes DESC, column_name), 1) AS running_pct
FROM parquet_columns
ORDER BY pct_of_bytes DESC, column_name;

Apply

Your turn

The task this lesson builds to.

Write a query that returns the bytes scanned and the cost of reading user_id, event_type, and revenue_usd for the single day 2026-02-20 from each of the two layouts, as (layout, bytes_scanned, cost_usd, savings_pct), biggest scan first.

raw_events_csv is unpartitioned CSV and prunes nothing, so it reads its whole total_size_bytes from table_layouts. events_parquet reads only that day's size_bytes from table_partitions, times the three columns' combined pct_of_bytes share from parquet_columns; truncate that byte count with CAST(... AS INTEGER). Put the table name in layout, bill at $5.00 per TB (1 TB = 1,000,000,000,000 bytes) rounded to 4 decimals, and report savings_pct against the CSV scan rounded 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 3 bonus drills.

Write a query that returns what the small-files problem costs per month, for every events_parquet partition whose average file size is under 16 MB, as (partition_value, file_count, monthly_request_cost_usd, compacted_cost_usd, monthly_savings_usd), biggest saving first, over table_partitions.

Assume 1,200 queries a month, one GET request per file per query, and $0.0004 per 1,000 GET requests. compacted_cost_usd is that same arithmetic on the file count a 128 MB target would produce, rounded up. Treat 1 MB as 1,000,000 bytes and round every dollar column to 2 decimals.

2 hints and 1 automated check are waiting in the workspace.