Turning a 10TB Scan Into 100GB

Concepts covered: Columnar vs Row Formats

Each technique so far cut bytes on its own. Applied to a single table, the cuts multiply. Here is page_views as it first landed: 450 days of uncompressed, row-oriented CSV, 10TB in all. It has 40 columns, and its rows average 290 bytes. The quarterly report counts sessions per country and page type over the last 90 days, excluding bots.

SELECT
  country,
  page_type,
  approx_distinct(session_id) AS sessions
FROM page_views
WHERE view_date >= DATE '2026-07-25'
  AND is_bot = false
GROUP BY
  country,
  page_type

3 factors, multiplied

Each factor comes from an earlier section. Partitioning removes the days the report does not ask for, and it works only because the WHERE clause compares view_date directly, the pushdown habit. Columnar layout removes 36 of the 40 columns. Encodings and zstd shrink what remains 4x, helped by the country sort that lengthens runs. The order of the steps changes nothing, because each factor divides the bytes that are left.

• 100x

Remove one piece, keep the rest

A table without partitions can still skip row groups by the min and max of a date column if its files were written in date order. To find out which ones to skip, though, the engine first reads the footer of every file across all 450 days.

The cheapest piece to lose is the one that costs nothing to keep. A single function around a timestamp turns the 100GB report back into a scan of more than 500GB, while the files themselves stay exactly as they were. Columnar layout and compression pay off whatever the filter says, so they are defaults; partitioning is a per-table decision that pays only when filters reach it.

On the final page_views layout, a query reads page_url and load_ms, which average 87 of each row's 290 bytes and also compress 4x. It filters view_date directly over the last 9 days. About how many bytes does it scan?

• About 750GB
• About 60GB
• About 150GB
• About 15GB

Going below 100GB

The quarterly report reads every country, so the country sort helped only compression. A Germany-only version adds country = 'DE', and row-group min and max on country can now prune, but only a whole row group at a time. Each hourly file of about 230MB holds 2 row groups, so German rows share row groups with other countries and the scan falls at best to about half, near 50GB. A daily job that rewrites each day sorted by country makes about 43 row groups per day; if German views are 6% of traffic, they fill about 3 of them, and the scan approaches 6GB.

A teammate proposes adding country as a second partition level under view_date, keeping the hourly load, so the Germany report prunes better. What happens to the quarterly report?

• Roughly the same bytes, now spread across up to 432,000 small files
• About 200 times fewer bytes, since each country becomes its own partition
• No change at all, because the report has no filter on country
• The query fails, because a partition column cannot appear in GROUP BY

About this lesson

This section is part of the Storage Layers: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.