Case Study 3.1: BigQuery, From Web Crawl to Petabyte Analytics

Concept. BigQuery scales to trillions of rows by storing data in columnar format and decoupling storage from compute, so queries spin up thousands of workers on demand and only read the columns they touch.

Intuition. When Spotify queries a 100-billion-row Listens table for "average rating per genre," BigQuery reads only the rating and genre columns, skipping user_id, listen_time, and song_id. Then it parallelizes the scan across thousands of workers that exist only for the seconds the query runs.

Case Study 3.1 Reading Time: 8 mins

BigQuery answers a query over trillions of rows: columnar storage reads only the rating and genre columns (green) and skips user_id, song_id, listen_time (grey); thousands of on-demand leaf workers each scan a column slice and their results bubble up a tree to a root that aggregates the answer.

Figure 1. How BigQuery answers a query over trillions of rows. Storage is columnar: a query for average rating per genre reads only the rating and genre column files (green) and skips user_id, song_id, and listen_time (grey). Thousands of leaf workers, spun up only while the query runs, each scan a slice of those columns, and their partial results bubble up a tree to a root that aggregates the answer. Storage and compute are decoupled, so what took hours on row storage finishes in seconds.

The Origin: An Engineer's Foosball Problem

In 2005, Andrey Gubarev, a Google engineer, faced an annoyance. He needed insights from Google's web datasets, but the existing infrastructure was sluggish. Queries dragged on for hours, eating into his foosball time. Frustrated, he dug into the bottlenecks. That investigation laid the groundwork for Dremel, the system that would later ship as BigQuery and change large-scale data analysis.

The Bottleneck: Row Storage on Web Documents

Google's web document storage held data points like:

  • URL

  • Title

  • Date

  • Keywords

  • Body (HTML). The heavyweight, often megabytes per row.

In a row-based system, querying trends by Date required processing the entire row, including the bulky HTML body, even though only one tiny field was needed. Fetching a few bytes (Date) involved dragging along megabytes (Body) per record. At Google scale, that was hours of wasted I/O.

The Solution: Columnar Storage via Dremel

Dremel flipped the layout. Store all URL values together, all Date values together, all Body values together, each column on its own pages.

  1. Targeted reads. Query by Date? Only the Date column is touched. The HTML Body is never read.

  2. Better compression. A column of dates compresses far better than a row of mixed types, because uniform data has uniform patterns.

  3. Tree-based parallel execution. A query is split across thousands of leaf workers; each scans a slice of one column; results bubble up the tree.

The result: queries that took hours on row storage finished in seconds.

Real Numbers: A Spotify-Scale Query

-- Analytics across 5 billion music listens
SELECT genre, AVG(rating) AS avg_rating
FROM spotify.listens_1tb       -- 1 TB table, 20 columns
GROUP BY genre
ORDER BY avg_rating DESC;

The query touches only 2 of 20 columns (rating, genre). On row storage every byte is read; on BigQuery only the relevant columns are scanned, then compressed:

Approach Bytes read Time Cost
Row storage (full table scan, all 20 columns) 1 TB 10+ minutes baseline
BigQuery (2 columns, compressed, distributed) ~50 GB ~15 seconds 20× cheaper
Speedup 20× less I/O 40× faster

Why It Works: Three Pillars

Columnar layout. Only rating and genre files are read; the other 18 columns are skipped.

Compression. Dictionary encoding on genre (a few dozen genres) plus run-length encoding on rating (values 1 to 5) shrink the read by another order of magnitude.

Distribution. The scan is sharded across ~100 workers, each processing ~500 MB. Workers exist only for the seconds the query runs.

Together these three turn "scan a petabyte" into "scan a few GB on hundreds of machines in parallel."

The Paper

BigQuery is Google's implementation of Dremel, first described in their 2010 VLDB paper. Dremel introduced interactive analysis of web-scale datasets using nested columnar storage and tree-based query execution.

Modern Heirs

The columnar-plus-compression-plus-distribution pattern now powers most of the analytics ecosystem:

  • Google BigQuery. Petabyte-scale data warehouse.

  • Amazon Redshift. Cloud data warehouse.

  • Snowflake. Modern cloud analytics platform.

  • Apache Parquet. Open-source columnar file format.

  • ClickHouse. Real-time analytics database.

Takeaway: BigQuery turns "scan a petabyte" into "scan a few columns on thousands of on-demand workers" by combining columnar storage, heavy compression, and tree-based distribution, with storage decoupled from compute. It is the workload-driven opposite of PostgreSQL's row layout: pick the storage shape that matches how you query.

Next

Hybrid Storage → When you need both OLTP-speed inserts and OLAP-speed analytics, you mix the two layouts instead of picking one.