BigQuery: Serverless OLAP at Cloud Scale

Concept. BigQuery is a serverless OLAP query engine: you submit SQL, Google's compute layer auto-provisions thousands of ephemeral "slots" against shared columnar storage, and you get an answer in seconds. Storage and compute scale and bill independently.

Intuition. When a Spotify analyst runs SELECT artist, COUNT(*) FROM listens WHERE year = 2026 GROUP BY artist ORDER BY count DESC LIMIT 100 over a 10 TB Listens table, BigQuery spins up roughly 2,000 compute slots, each scans a slice of the columnar files in Colossus, and returns the answer in about 30 seconds for about a dollar and a quarter. No cluster was provisioned, no DBAs were involved, and when the query finishes the slots vanish.

The Architectural Shift: Decoupled Storage and Compute

Traditional data warehouses (Teradata, the original Hadoop stack, even early Redshift) bundled storage and compute into one cluster. To store more data, you bought bigger nodes, and you paid for the compute on those nodes whether you queried them or not. To scan a petabyte, you bought a petabyte-sized cluster and kept it running 24/7.

BigQuery split the two. Storage lives in Colossus, Google's distributed file system, where it sits as compressed columnar files indefinitely at storage prices (around $20/TB/month). Compute is a separate fleet of ephemeral "slots" that spin up per query, do their work, and vanish. The result is two independent scaling axes:

  • Storage scales to petabytes without paying for compute.

  • Compute scales to thousands of slots per query without provisioning servers.

Decoupling makes serverless possible: there is no cluster between queries, so there is nothing to manage.

An analyst submits SQL; roughly 2,000 ephemeral pink compute slots scan in parallel at about $6.25/TiB and vanish, reading a 10 TB table held as persistent per-column files in grey Colossus storage at about $20/TB/month; the two layers scale independently

Figure 1. BigQuery is serverless OLAP: storage and compute scale and bill independently. Each query spins up roughly 2,000 ephemeral slots that run in parallel and vanish when it finishes, billed per byte scanned (about $6.25/TiB). Colossus holds the table as persistent columnar files, one column per file, billed per gigabyte at rest (about $20/TB/month). No cluster to provision: you pay for the bytes you scan, not for idle capacity.

A Worked Query: Spotify Top Artists, 100B Plays

SELECT artist, COUNT(*) AS plays
FROM spotify.listens_2026
WHERE year = 2026
GROUP BY artist
ORDER BY plays DESC
LIMIT 100;

Walk through what happens behind the API call: five stages, from planning the scan to returning the top 100.

Five stages left to right: Plan cuts the scan from 10 TB to 200 GB using the columnar layout; Schedule allocates about 2,000 ephemeral slots; Scan has each slot read a Colossus partition and hash-aggregate by artist; Shuffle and merge hash-partitions the partial counts by artist and trims to the top 100; Return lands the result in about 30 seconds for about $1.25.

Figure 2. Behind one API call, the query runs as five stages. Plan: the optimizer sees only artist and year are touched, so the columnar layout (from the Module 3 BigQuery case study) reads only those two columns and skips the rest; over 100 billion rows they compress to ~200 GB, a fraction of the full 10 TB. Schedule: ~2,000 ephemeral slots are split across the stages. Scan: each slot reads a Colossus partition and hash-aggregates by artist into partial counts. Shuffle and merge: a second wave hash-partitions those counts by artist, sums, sorts, and trims to the top 100, the same hash-partitioning as Distributed Sort & Hash. Return: the result lands in ~30 s, slots release, and you pay only for the ~200 GB scanned, about $1.25.

Why ELT Beat ETL

In the pre-BigQuery world, the standard pattern was ETL: Extract from operational systems, Transform on a separate compute layer (often Spark), Load into the warehouse. This made sense when warehouse storage was expensive and warehouse compute was inflexible. You cleaned the data before loading, because once it was in the warehouse you did not want to touch it.

Decoupled storage and compute flip the economics:

  • Load raw. Storage is cheap. Dump everything into BigQuery without pre-processing.

  • Transform inside. Use SQL to reshape data on demand. The same engine that runs your analyst queries runs your transformations.

  • Iterate freely. When the transformation logic has a bug, re-run from the raw source instead of from a stale ETL output.

This is ELT: Extract, Load, Transform. The transformation step moves from a brittle external pipeline (Spark + Airflow) into the warehouse itself. Tools like dbt are built entirely around this pattern.

Where BigQuery Sits Among Modern OLAP Engines

Every modern OLAP engine (BigQuery, Snowflake, Redshift, Spark SQL) made the same core move: decouple storage from compute. What separates them is a set of smaller design choices, and those choices track use cases more than raw capability:

  • BigQuery is serverless, so it fits ad-hoc analytics where the query load is spiky and you never want to run a cluster.

  • Snowflake gives you virtual warehouses you size yourself, which fits steady workloads that want a predictable cost ceiling.

  • Redshift fits AWS-native shops; Spark SQL fits custom, ML-heavy transforms.

The point is the pattern, not the details: pick the engine whose operational model matches how you actually query. Each makes its own tradeoffs on pricing, cluster sizing, and consistency, and you can read up on those nuances when you commit to one.

What This Says About the Modern Data Stack

The modern data stack is a chain of specialized systems, each playing the role you have now seen:

  • Kafka (next) ingests real-time events.

  • DFS (S3, Colossus) stores raw and transformed data cheaply.

  • Spark / dbt transform data in place using SQL.

  • BigQuery / Snowflake serve interactive OLAP queries against the result.

Spark and BigQuery often appear in the same architecture: Spark does the heavy ELT job overnight, writes results back into BigQuery's storage, and analysts run sub-minute queries against the result the next morning.

For the columnar storage and Dremel execution model that make BigQuery work under the hood, see the Module 3 BigQuery case study.

Next

Kafka → Storage you can keep forever and compute you can spin up on demand handle batch and interactive analytics. The last piece is what brings the data in: a real-time event substrate that makes raw events available for both streaming consumers and batch loaders.