Skip to content
Tutorials Center
My learning

Topics

Lesson 5 of 37 ยท Architecture and Setup

BigQuery Architecture: Dremel, Colossus and Slots

See how BigQuery separates storage from compute, how Dremel splits a query into stages across slots, what Capacitor columnar storage is, and how this explains cost and speed.

BigQuery feels like magic: a query scanning a terabyte returns in seconds on a machine you never saw. It is not magic. A handful of design decisions make it work, and knowing them lets you predict why one query is cheap and another is not. This lesson gives you the architecture at the level a data analyst or engineer needs.

Storage and compute are separate

In a classic database the same server stores data and runs queries. If you need more of one, you buy more of both. BigQuery splits them:

flowchart LR
  Q["Your SQL"] --> C["Compute: Dremel query engine (slots)"]
  C <-->|"Jupiter network"| S["Storage: Colossus file system (Capacitor columnar files)"]

A very fast internal network (Jupiter) connects the two layers, so compute can read stored data at huge bandwidth.

Because they are independent, you can store petabytes without paying for idle compute, and a query can borrow thousands of workers for a few seconds and then release them.

Storage: Colossus and Capacitor

Tables are stored in Google's distributed file system Colossus in a columnar format called Capacitor. Three consequences:

  1. Column-by-column reads. A query on country and revenue reads those two columns only.
  2. Heavy compression. Values in a column look alike, so run-length and dictionary encoding shrink them. Compression is automatic, and you are billed for the logical (uncompressed) size by default, though a physical billing model is available.
  3. Replication and durability. Data is replicated across zones automatically. You never configure backups to get durability.

Row store versus column store

Row store Reads whole rows
Column store Reads only needed columns
Compression Similar values pack tightly

BigQuery bills by columns scanned.

1 / 2

Compute: Dremel and slots

The query engine is Dremel. When you submit SQL:

  1. The query is parsed and planned into a tree of stages.
  2. Each stage is split into many small units of work run in parallel on slots.
  3. Between stages, data is exchanged through a distributed in-memory shuffle service.
  4. The final stage writes the result to a temporary (anonymous) table, and the console shows it.

A slot is a unit of compute (CPU plus memory) used to run part of a query. On-demand pricing gives a project up to a shared pool, typically up to about 2,000 slots at a time. You can instead buy capacity (reserved slots) for predictable cost, which you will meet in the cost chapter.

A simple aggregation has three logical stages:

flowchart LR
  A["Stage 1: scan and filter (many slots)"] --> B["Stage 2: partial aggregate then shuffle by key"]
  B --> C["Stage 3: final aggregate and sort"]

The execution details tab in the console shows these stages with timings.

The same shape, in miniature

You can see the logical stages of an aggregate on our stand-in. EXPLAIN QUERY PLAN is SQLite's version of looking at the plan (BigQuery's own plan is in the Execution details tab, not in SQL):

SQL Stand-in: a query plan ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

And the work a distributed engine does can be pictured with partial results. Here we aggregate each half of the data separately, then combine the pieces, which is exactly how many workers cooperate:

SQL Stand-in: partial aggregates, then combine ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

This is why COUNT, SUM, MIN, MAX scale so well: partial results can be merged. It is also why COUNT(DISTINCT x) is costlier (the engine must combine the actual sets of values) and why BigQuery provides APPROX_COUNT_DISTINCT.

What this means for you

Fact about the architecture Practical rule
Reads are by column List the columns you need; avoid SELECT *
Billing by bytes scanned Partition and cluster big tables to skip data
Massive parallelism, but a shuffle between stages Heavy joins and ORDER BY on huge data are the slow parts
Slots are shared across many users A query's speed can vary with load, unless you reserve capacity
Storage is cheap and separate It is fine to keep raw data and derived tables side by side
No indexes Speed comes from scanning less, not from index lookups

Another consequence: BigQuery is built around append and rewrite. Updating one row is allowed (DML) but has overhead and quotas, so for frequent changes you prefer batch loads or the Storage Write API.

Time travel and fail-safe

BigQuery keeps previous versions of a table for the time travel window (2 to 7 days, configurable). You can query a table as it was an hour ago:

SQL
SELECT *
FROM `my-project.shop.orders`
FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);

After time travel expires, a further 7-day fail-safe lets Google support recover data in emergencies. It is not a user feature.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What is Dremel?

    Dremel is the compute layer. Capacitor is the storage format and Colossus the file system.
  2. 2. Why can BigQuery scale storage and compute independently?

    Separation of storage and compute is a core design choice enabled by the Jupiter network.
  3. 3. What is a slot?

    Queries are split into pieces run in parallel by many slots.
  4. 4. Which aggregate is generally most expensive to merge across workers?

    Exact distinct counts need the actual sets of values combined, which APPROX_COUNT_DISTINCT avoids.
  5. 5. Does BigQuery speed up queries mainly with indexes?

    There are no user-managed indexes for normal tables; partitioning, clustering and column pruning cut the scan.

Summary

  • Storage (Colossus, Capacitor columnar files) and compute (Dremel on slots) are separate.
  • Queries become stages run in parallel, with shuffles between stages.
  • Billing and speed both follow bytes scanned, so scanning less is the main optimisation.
  • Mergeable aggregates scale; exact distinct counts and giant sorts cost more.
  • Time travel lets you query earlier table versions for a few days.

Next: Setting Up: Projects, IAM and the bq CLI