BigQuery architecture — Dremel, Capacitor, Colossus, slots, BI Engine

Why architecture matters here

BigQuery hides most complexity but not cost. Users write SELECT * and pay for a full scan. Poorly designed schemas explode shuffle. Materialized views + BI Engine give order-of-magnitude speedups when used correctly. Slot reservations vs on-demand shape the bill.

The architecture matters because tuning happens at multiple layers: schema (partitioning + clustering), query (avoid SELECT *), acceleration (BI Engine, MVs), and slots (reservation + autoscaling).

Understanding the pieces means you can tune each independently and get real leverage.

The architecture: every piece explained

The top strip is the compute plane. Client SQL arrives declaratively. Query engine (Dremel) plans an aggregation tree — leaves scan storage, mixers combine partial results. Slot allocator picks slots from a reservation (dedicated capacity) or on-demand pool. Shuffle service handles data exchange between stages over a petabit fabric.

The middle row is storage + acceleration. Capacitor is the columnar format — dictionary encoding, RLE, and adaptive schemes. Colossus is the distributed file system underneath. BI Engine caches hot columns in memory for millisecond response. Streaming ingest uses the Storage Write API for exactly-once inserts with schema evolution.

The lower rows are optimization + governance. Materialized views maintain precomputed aggregates that BigQuery uses automatically. Governance combines IAM + column-level security + data catalog for classification. Ops plans slots, controls cost with quotas and query size limits, optimizes queries, and audits access.

Storage and compute are separate services, not tiers

The most consequential fact about BigQuery is that a table is not attached to a cluster. Data lives in Colossus, Google's cluster file system, encoded as Capacitor files; execution happens on Dremel workers that mount nothing and own nothing. They talk over the Jupiter fabric, whose bisection bandwidth is high enough that remote reads are not the bottleneck a shared-nothing warehouse assumes.

So you never resize a cluster to hold more data, nor resize storage to run a bigger query, and two teams querying the same table contend only for slots - and only if they share a reservation. There is no warm node either: every query re-reads from Colossus unless a cache layer intercepts it. Colossus itself has its own write-up; here it is just an arbitrarily wide, durable, seekable byte store.

Dremel: one query becomes a serving tree

Dremel executes SQL as a multi-level tree of stages. The root plans the query; each stage runs in parallel across many workers, reading from storage or a prior stage's shuffle output and writing to shuffle or to the final result.

At the bottom, leaf workers scan: each is handed a subset of the table's Capacitor blocks and applies the filters and partial aggregation the planner pushed down. Intermediate levels merge partials and the root assembles the answer. It scales to trillions of rows because every level up sees an order of magnitude less data - leaves emit partial aggregate state, not rows.

Parallelism is not fixed up front: BigQuery observes what each stage emits and repartitions the next accordingly. When a query is slow, read per-stage records-in against records-out first - the stage whose output dwarfs its input is the join or UNNEST that exploded.

Capacitor: the format that decides your bill

Capacitor stores each column separately, in blocks, with per-block metadata. Within a block, values take whatever encoding fits: dictionary for low-cardinality strings, run-length for sorted or repetitive values, variants for numeric ranges. One type and a narrow distribution per column compress far harder than a row-oriented layout.

Nested and repeated fields - STRUCT and ARRAY - use the definition-and-repetition-level scheme Dremel introduced and Parquet later adopted: each leaf field is its own column stream, with two small integers per value marking its position in the nesting tree and where a repeated element begins. An unqueried array of structs therefore costs nothing to read, which is why denormalising with a repeated field often beats the join it replaces.

Two rules follow. On-demand billing charges for the bytes of the columns you named, so SELECT * costs orders of magnitude more than naming four columns; and per-block min/max metadata lets the planner skip blocks whose range cannot satisfy a filter - the mechanism clustering exploits.

The shuffle tier is what makes large joins possible

Between stages, data moves through a dedicated shuffle service rather than directly between workers: a producer writes output partitioned by the shuffle key into an in-memory store on the network fabric, and the consumer reads its assigned partitions. Because that is an addressable intermediate store, a dead worker can restart and re-read its input rather than forcing a stage-wide redo.

This is what makes large joins tractable. A hash join repartitions both inputs on the join key so matching rows share a partition, and each consumer joins one partition locally; when one side is small the planner broadcasts it instead. Which it chose is the first thing to check on a slow join.

The failure mode is skew: one dominant key produces a partition that cannot be split, so a single worker holds all of it. Shuffle spills to disk, and if the partition still does not fit you get a resources-exceeded error extra slots will not fix. Salt the hot key, filter the sentinel values (empty string, zero, unknown) behind it, or pre-aggregate first.

Slots: the unit of compute, and how you buy them

A slot is a share of CPU, memory and shuffle capacity that executes one unit of a stage's work. Stages are split into work units and dispatched onto whatever slots the job is entitled to, so more slots run more units concurrently, up to the parallelism the stage actually has.

On-demand billing charges for bytes scanned and draws from a shared pool under a per-project slot ceiling: you manage no capacity and cannot make a query faster by buying more. Capacity pricing buys slots directly - a reservation with a baseline and an optional autoscale ceiling, with projects assigned to it - and bills slot-seconds, so an inefficient query costs latency rather than money.

Reservations enforce fairness, not partitioning: one job expands to fill the reservation when nothing else runs and is throttled back as others arrive, and idle capacity is lent out unless you disable sharing. Size the baseline for steady load and isolate ad-hoc analytics.

Partitioning and clustering: the two knobs on bytes scanned

Partitioning splits a table into physically separate segments by ingestion time, a DATE/TIMESTAMP column, or an integer range; a predicate on that column prunes whole partitions before a block is read. Pruning happens at planning time, so the filter must be resolvable then - a literal or query parameter prunes, a subquery or non-deterministic expression usually does not, and you pay for the whole table.

Clustering sorts rows within each partition by up to four columns and records the block ranges, which the planner uses to skip blocks that cannot match. Filters must respect the prefix order: (tenant_id, event_type) prunes well on tenant_id alone and poorly on event_type alone. Since this is best-effort skipping rather than a guaranteed split, the pre-run byte estimate is only an upper bound.

CREATE TABLE analytics.events
PARTITION BY DATE(event_ts)
CLUSTER BY tenant_id, event_type
OPTIONS (
  require_partition_filter = TRUE,
  partition_expiration_days = 400
) AS SELECT ... ;

require_partition_filter is the strongest cost guard on a large table: a query with no partition predicate fails at planning time instead of scanning years of history. New rows land unsorted, but background re-clustering is free.

Materialized views, the results cache, and BI Engine

Three caches sit between a query and a full scan, and they trigger under different conditions. The query results cache stores the result of an exact query string against unchanged tables; a hit is free and instant, but fragile by design - a write to any referenced table, a non-deterministic function such as CURRENT_TIMESTAMP(), wildcard tables and jobs with an explicit destination table all bypass it. Dashboards lose it by templating a timestamp into the SQL text.

Materialized views precompute an aggregation over a base table and are maintained incrementally as it changes. The key property is automatic rewrite: you point queries at the base table and the optimizer substitutes the view when it can, including partially - the view for the covered range, the base table for rows newer than the last refresh. Supported shapes are restricted to aggregations over one table, and max_staleness trades freshness against refresh cost.

BI Engine holds column data in memory in front of the query engine for sub-second dashboard latency; its reservations and eviction rules have their own deep-dive.

Ingestion: the legacy streaming buffer vs the Storage Write API

The legacy tabledata.insertAll API writes rows into a streaming buffer that is queryable immediately but sits outside columnar storage until a background process flushes it into Capacitor blocks. While rows are there they are invisible to table copy and DML behaves inconsistently - the classic surprise where a DELETE against very recent data appears to do nothing.

The Storage Write API replaces it with a gRPC interface that appends into named streams. The default stream gives at-least-once semantics with no stream management, which is what most pipelines want. Application-created streams give more: you append with an explicit offset, so a retried append carrying an offset the server already has is deduplicated rather than duplicated - that is how exactly-once is actually obtained. Committed streams make rows visible as appended; pending streams buffer everything and reveal it atomically only on finalize and commit, an all-or-nothing batch with no staging table. For bulk data, load jobs from Cloud Storage stay cheapest: not billed per row, written straight into Capacitor.

Time travel, snapshots, and clones

Every table keeps a time travel window - seven days by default, configurable shorter - during which you can read it as it existed at any instant inside.

SELECT *
FROM   analytics.events
FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 HOUR);

That is the recovery path for a bad UPDATE: read the old state and write it back, no backup restore involved. A table snapshot pins a point-in-time view as a separate read-only object, metadata-only at creation, with storage billed only for bytes that later diverge as the base table changes; clones are the writable version of the same idea. Because time travel also retains deleted bytes, truncating a table does not stop it costing anything until the window elapses.

Cost practices that follow from the bytes-scanned model

On-demand billing collapses to one rule: you pay for the bytes of the columns you touched in the partitions you failed to prune. Consequences that surprise people:

• LIMIT does not reduce bytes scanned - it is applied after the scan. Only projection and partition or cluster pruning reduce the bill.
• SELECT * inside a view or subquery costs as much as at top level - the projection pushed into the scan is what matters.
• A self-join to attach a per-group aggregate reads the table twice; the equivalent window function reads it once, and APPROX_COUNT_DISTINCT replaces a shuffle-heavy exact distinct with a sketch.

Set two controls on day one. maximum_bytes_billed, per job or as a project default, fails a runaway query at planning time rather than after it has scanned the warehouse; custom quotas cap per-user and per-project daily bytes. For attribution, INFORMATION_SCHEMA.JOBS exposes bytes billed and slot-milliseconds per job alongside the submitting user and job labels - group by label to find which pipeline actually costs money.

End-to-end flow

End-to-end: analyst runs a query on a 10 TB table partitioned by day and clustered by user_id. Query engine reads only the relevant partitions (30 days = 300 GB). Slot allocator picks 200 slots from the reservation. Columnar scan reads only requested columns (20 GB). Aggregation tree combines partials. BI Engine caches the hot day. Result returns in 3.4 seconds. Cost: 20 GB scanned. Materialized views for common aggregates would drop this further. Access control: analyst only sees non-PII columns via column-level ACLs.