BigQuery Concepts
A serverless analytical engine: columnar storage you do not manage, compute allocated per query rather than provisioned, and exactly two physical knobs — partitioning and clustering — carrying all the layout weight.
Who needs this, what one row is, and why the obvious build breaks
Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.
There is no cluster to size, no node to add and no index to create. What is left that decides how much a query reads and how long it takes?
Analysts and dashboards issuing SQL with no idea how much data sits behind it, and transformation jobs rebuilding models on a schedule. Both need the same thing from the platform designer: that the tables they query are laid out so the engine can eliminate most of the data before reading any of it, because on this architecture eliminating data is the only optimisation available to them.
The unit of physical organisation is a partition — a horizontal slice of a table selected by one column — and, inside it, the sort order imposed by clustering. Below that the engine manages storage in units it does not expose, which is the deliberate trade: you give up file-level control and get a system that never needs you to size anything.
Load the table, write SQL, ignore the physical layer entirely. This works remarkably well and is the point of the architecture — a table with no partitioning at all is queryable on day one at any size, with no cluster to provision and no tuning session. For small tables and exploratory work it remains the right answer forever.
The events table passes a few years of history and every dashboard query still scans the whole thing, because no partition column was ever declared and there is nothing else for the planner to eliminate on (Partition Pruning).
- The events table passes a few years of history and every dashboard query still scans the whole thing, because no partition column was ever declared and there is nothing else for the planner to eliminate on (Partition Pruning).
- A partition column exists but the dashboard filters on
DATE(event_timestamp) = @drather than on the partition column directly, so the planner cannot statically resolve which partitions survive and reads all of them. The query is correct and the elimination is gone (Predicate Pushdown). - Someone partitions by
customer_id. The partition count explodes, per-partition metadata dominates, each partition holds very little, and query planning becomes the expensive part of a query that reads almost nothing (Partition Cardinality). - A transformation is written as
SELECT *into a staging model. Every column of a very wide table is read on every run even though the model uses six of them, and the waste is invisible because nothing failed (Projection Pushdown). - Clustering is declared on four columns and the dashboards filter on the third. Clustering prunes on a prefix of the sort order, so a filter that skips the leading columns eliminates far less than the declaration suggested (Clustering and Sort Order).
- A join between two very large tables on a skewed key spends its life in one stage while a handful of workers process most of the rows. There is no cluster to enlarge — the fix is in the data, not the capacity (Data Skew, Salting a Skewed Key).
What is actually happening
- The architecture is a disaggregated one: a storage layer holding columnar, compressed data; a stateless execution layer whose workers are allocated to a query rather than owned by you; and a fast network with a dedicated shuffle tier between them (Separating Storage from Compute).
- Because compute is per-query, the resource question inverts. You are not asking "is my cluster big enough" — you are asking "how much data does this query force the engine to read and move", because that is the only quantity you control (Scan Cost).
- Storage is columnar, so a query reads only the columns it names. This is why projection is not a style preference here: an unused column in the
SELECTlist is data physically read off storage and shipped across the network (Row vs Column Storage, Columnar Execution). - Execution is a DAG of stages separated by shuffles, much like any distributed engine. Aggregations and joins that require repartitioning write intermediate results into the shuffle tier and read them back; that is where skew and stragglers appear (The Shuffle, Straggler Tasks).
- Partitioning slices a table by one column — typically a date or timestamp, or an integer range — and lets the planner discard whole partitions from a query using the filter. It is the coarse elimination and it is the one that matters most (Partitioning).
- Clustering sorts data within each partition by up to a handful of columns and lets the engine skip blocks whose value ranges cannot match. It is best-effort and prefix-ordered: it helps in proportion to how selective a filter on the leading clustering columns is, and does nothing for a filter that only touches a trailing one (Clustering and Sort Order). New data arriving continuously is not perfectly sorted the moment it lands; the service reorganises in the background. So clustering is a property that degrades with writes and is restored asynchronously, which is the same shape as compaction on any other system (File Compaction).
A query with no cluster behind it
The defining property of this architecture is that compute is a property of the query, not of your account. There is no cluster sitting idle between queries, no node count to choose, and no moment where you decide how much hardware your analytics deserve. A query arrives, the service plans it, workers are allocated to its stages, and they are gone when it finishes.
That removes an entire category of work — capacity planning, resizing, cluster babysitting — and removes the corresponding category of levers. When a query is slow or expensive, "give it more machines" is not an option you have. What remains is how much data the query is forced to read and how much it has to move between stages, and both of those were decided by the table layout long before the query was written (Physical Data Layout).
The shuffle tier is worth noticing in the diagram. Stages that repartition data — joins on a non-aligned key, aggregations across many workers — write intermediate results into it and read them back. That is where skew hurts, and it hurts in the same way it hurts on any distributed engine, which is why the compute module's lessons apply here without translation (Stages and Tasks).
How compute is metered and named — the unit of parallelism, the editions, reservation and on-demand modes, and the concurrency behaviour of each — has been restructured more than once and is the kind of detail that dates a document within a year. Nothing in this lesson depends on those names; verify current documentation for anything about metering, quotas or limits before designing around it.
Partitioning and clustering are the entire layout surface
Two decisions carry all the physical weight. Partitioning slices a table by one column, and the planner discards whole partitions using the query's filter — coarse, cheap and by far the largest effect available. Clustering sorts rows within each partition by a small ordered list of columns, letting the engine skip blocks whose value ranges cannot match — finer, best-effort, and prefix-ordered like any sort key (Partitioning, Clustering and Sort Order).
The layout below shows one day-partitioned table under a realistic dashboard predicate. Notice what actually happens: the date filter eliminates almost every partition, and inside the surviving partition the clustering on country then product_id lets whole blocks be skipped. Notice also the last row — a query that filters only on product_id, skipping the leading clustering column, gets very little from clustering and nothing from partitioning.
The failure this device is designed to make visible is the one that produces no error: a predicate the planner cannot resolve against the partition column. WHERE DATE(event_ts) = @d is semantically identical to WHERE event_date = @d and physically catastrophic, because the planner cannot know which partitions a function's output belongs to until it has read them (Predicate Pushdown).
- event_date=2026-08-23a full day · 1 file · skipped
- event_date=2026-08-24a full day · 1 file · skipped
- event_date=2026-08-25 / blocks where country < DEpart of the day · 1 file · skipped
- event_date=2026-08-25 / blocks where country = DEa slice of the day · 1 file · read
- event_date=2026-08-25 / blocks where country > DEpart of the day · 1 file · skipped
- recently written, not yet reorganisedthe newest arrivals · 1 file · read
Only the partition column supports elimination before reading; everything else is block-level skipping inside the surviving partitions. A predicate wrapped in a function — DATE(event_ts) instead of event_date — removes the first row of this table entirely and reads every partition.
1-- Reads every partition: the planner cannot resolve DATE(event_ts)2-- to a set of partitions without first reading event_ts.3SELECT country, SUM(amount_minor) AS revenue_minor4FROM analytics.fct_orders5WHERE DATE(event_ts) BETWEEN '2026-08-01' AND '2026-08-25'6GROUP BY country;7 8-- Reads twenty-five partitions: the predicate is on the partition9-- column itself, with literals the planner can evaluate at plan time.10SELECT country, SUM(amount_minor) AS revenue_minor11FROM analytics.fct_orders12WHERE event_date BETWEEN DATE '2026-08-01' AND DATE '2026-08-25'13GROUP BY country;14 15-- Clustering only helps on a prefix of its column list.16-- Clustered by (country, product_id):17-- country = 'DE' -> good block skipping18-- country = 'DE' AND product_id=42 -> better19-- product_id = 42 alone -> little help; country is unconstrained20-- so nearly every block's range can matchThe two queries return identical results. The difference is entirely in what the planner can prove before reading, which is the only thing this architecture lets you influence.
What the model rewards, and what it quietly punishes
When compute is allocated per query and storage is columnar, the platform rewards exactly two behaviours: reading fewer rows, and reading fewer columns. Everything else — clever SQL, more capacity, better hardware — is either unavailable or secondary. That is a narrow optimisation surface and it is genuinely easier to reason about than cluster tuning, provided you know it is the surface you are on.
The punishment side is quieter. A lost partition filter, a SELECT * in a transformation, or forty dashboard tiles independently scanning the same fact table all produce correct results and successful runs. Nothing in the platform objects. The only symptom is a scan volume that nobody is looking at, which is why the bytes-scanned budget in this lesson's quality field is the single most valuable check to add (Scan Cost).
The bars below are relative and unitless. They rank the drivers this architecture exposes; they are not a measurement and there is no ratio between them that would be true for your data.
The largest lever by a wide margin, and the one most easily lost to a predicate the planner cannot resolve statically. Governed by the partition column choice and by how queries are written against it.
Columnar storage reads exactly the columns you list, so a wide table plus a star projection multiplies read volume by the ratio of columns present to columns used. Fully under the query author's control.
Many dashboards independently scanning the same fact table for the same rollup. Removed by materialising a shared serving model rather than by any engine setting.
Driven by join keys and grain. A join at the wrong grain repartitions far more than the result needs, and skew concentrates that movement into a few workers.
Recently written rows are not yet in clustering order, so the newest partition — the one dashboards care about — eliminates the least. Restored asynchronously by background reorganisation.
History nobody queries plus snapshot retention. Independent of query behaviour and reduced only by an explicit lifecycle decision.
Relative weights, not measurements. The ordering is what transfers between platforms; the magnitudes do not, and this domain never publishes a price.
Weights are relative to the largest driver here and carry no units. The ranking is the teaching: on this architecture, elimination and projection dominate everything a query author can otherwise do.
| Trigger | Symptom | Cause | Response |
|---|---|---|---|
| A model filters with a function applied to the partition column. | Scan volume for one model is orders of magnitude larger than its output, and nothing fails. | The planner cannot map a computed expression to partitions, so it keeps all of them. | Store and filter on the partition column directly; keep the derived expression for display only (Partition Pruning). |
A staging model is written as SELECT * FROM raw_events. | Every downstream run reads every column of a very wide table, permanently. | Columnar storage reads what is projected; a star projects everything, including columns added upstream later. | Name columns explicitly in every model, and let a contract test fail when an expected column disappears (Contract Enforcement). |
| A table is partitioned on a high-cardinality identifier. | Query planning becomes slow and metadata dominates for queries that read very little data. | Partition count grew into the millions; per-partition bookkeeping is not free (Partition Cardinality). | Partition on a coarse time column and use clustering for the identifier — that is exactly the division of labour the two knobs exist for. |
| A join on a heavily skewed key. | One stage dominates the query while most workers finish immediately. | Repartitioning by a key whose distribution is dominated by a few values concentrates rows on a few workers (Data Skew). | Salt the hot key, broadcast the small side, or pre-aggregate before joining. There is no capacity lever to reach for (Salting a Skewed Key, Broadcast Joins). |
| Forty dashboard tiles each aggregate the fact table directly. | Scan volume grows with the number of dashboards rather than with the data. | No serving layer exists between the fact table and the BI tool. | Materialise the shared rollups as models with their own freshness contract (Data Marts, Model Layering). |
Anything numeric about this platform — the metering unit, concurrency limits, per-project quotas, the retention window for snapshot-style recovery, which loading paths exist and how each is billed — changes with product releases and differs by edition and region. This lesson deliberately states none of it. Verify current documentation before making any design depend on a limit or a billing behaviour.
How to build it
Most important first.
- Partition every large table on the column that nearly every query filters on, which in analytics is almost always an event date or an ingestion date. One column, chosen for the predicate people actually write, not for the one that feels natural (The Partitioning Decision). Make the partition filter statically resolvable. Filter on the partition column itself with a literal or a parameter, not on a function of it and not through a subquery the planner has to run first (Partition Pruning).
- Cluster on the columns that follow the partition filter in real queries, ordered from most to least selective, and stop at a small number. Clustering columns after the second or third rarely earn their place (Clustering and Sort Order).
- Name your columns.
SELECT *in a transformation is a standing instruction to read every column of a wide table forever, and it also makes the model silently absorb upstream schema changes (Breaking Schema Changes). - Materialise the aggregates that many dashboards share instead of letting forty tiles each scan the fact table. Repeated identical work is the largest avoidable driver on a work-billed architecture (Data Marts, Compute Waste).
- Treat skew as a data problem. There is no capacity lever here, so a hot join key has to be handled in the query — salting, broadcasting the small side, or pre-aggregating before the join (Broadcast Joins, Salting a Skewed Key).
- Prefer batch loads over continuous row-by-row inserts unless a consumer genuinely needs continuous freshness, and know which one you are using — the two paths have different visibility and different reorganisation behaviour (Streaming Ingestion).
What this actually promises
Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.
- A query sees a consistent snapshot of each table it reads; you do not observe a table half-way through a load (Atomic Publish).
- Single-statement writes to a table are atomic. Multi-table atomicity and the isolation level between concurrent statements are narrower than in an OLTP database, and a pipeline that assumes otherwise is assuming (Transactions and ACID).
- Ordering is not a property of a table here at all. Rows have no inherent order and any ordering a consumer needs must be expressed in the query (Event Time).
- Nothing guarantees pruning. Whether a query eliminates partitions depends on the predicate being statically resolvable, and the engine does not raise an error when it cannot — it simply reads everything (Partition Pruning).
- Nothing guarantees data is complete or correct. The engine faithfully aggregates whatever the pipeline loaded (Data Quality).
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
- The cheapest platform-level check here is a bytes-scanned budget per model and per dashboard: record what each query reads, and alert when a query's scan volume jumps without a corresponding jump in data volume. That single signal catches a lost partition filter, a
SELECT *creeping into a model, and a clustering key that stopped matching the workload. - It misses correctness entirely. A query can read exactly the right bytes and compute the wrong metric, and it misses the opposite failure too — a query that reads very little because an over-selective filter is silently dropping rows (Missing Rows).
- Pair it with a row-count and reconciliation check on the model itself, because scan volume is a cost and layout signal, not a data signal (Reconciliation).
- The architecture removes one traditional source of staleness — there is no cluster to be busy, so a query does not wait behind a batch job for capacity. Freshness is set by the load path, not by the engine (Batch vs Streaming Ingestion).
- A continuous insert path makes rows readable much sooner than a scheduled batch load, and pays for it in reorganisation work afterwards and in a layout that is temporarily less well sorted (File Size and the Small-Files Problem).
- Clustering freshness is its own axis: recently written data is less well organised than older data until the background reorganisation catches up, so a query over the newest partition can eliminate less than the same query over last month.
- Adding a column is cheap and does not rewrite the table; dropping or retyping is the expensive direction and is where a wide, long-retained table makes you regret an early decision (Schema Evolution).
- Changing the partition column is not an alteration, it is a rewrite of the table into a new one and a swap of everything that references it. Choose it as if it were permanent, because practically it is (The Partitioning Decision).
- Clustering keys can be changed and the reorganisation happens in the background, which makes clustering the reversible half of the layout decision and partitioning the irreversible half.
- Semantics change without any schema change and this architecture will not notice: a
revenuecolumn that quietly moved from gross to net loads, types and aggregates perfectly (Semantic Changes).
- Rebuilding a partition is the natural unit of repair: write the corrected data for one partition and replace that partition, leaving every other partition untouched (Planning a Backfill).
- Make the rebuild idempotent by writing to a staging table and swapping, rather than deleting and inserting in place, so a failed run leaves the previous state readable (Atomic Publish, Idempotent Data Pipelines).
- Snapshot-style recovery of a recent table state exists and is bounded by a retention window that is configuration rather than a law of the platform. A recovery plan that relies on it has an expiry date and should say what it is (Backfills).
- The durable recovery position remains external: retained raw data plus deterministic SQL means the whole warehouse can be rebuilt, here or elsewhere (Keeping Raw History: The Recovery Position and the Liability).
What can go wrong
- Pruning silently defeated by a predicate the planner cannot resolve statically, so a query that looks selective reads the entire table.
- Over-partitioning on a high-cardinality column, converting a scan problem into a metadata and planning problem (Partition Cardinality).
- A wide table plus
SELECT *in a transformation, multiplying every downstream model's read volume permanently. - Skew in a join or a
GROUP BYthat no amount of capacity fixes, because capacity is not the constraint (Data Skew). - The mitigation failing too: a bytes-scanned alert with a threshold set once, which stops firing as the table grows into it and now reports nothing (Alert Fatigue: The Page Nobody Reads).
- Continuous inserts creating a persistently under-organised newest partition, so the queries that matter most — today's — are the ones that eliminate least.
- "Serverless means there is nothing to tune." It means there is no *capacity* to tune. Layout and query shape carry all the weight they ever did, and they are now the only levers you have.
- "The query has a WHERE clause on the date, so it prunes." Only if the planner can resolve the predicate against the partition column statically. Wrapping the column in a function is a very common way to lose elimination while keeping correctness (Partition Pruning).
- "Clustering is an index." It is a sort order with block-level statistics. It prunes on a prefix, it is best-effort, and it degrades as data is written until background reorganisation restores it (Clustering and Sort Order).
- "
SELECT *is fine, the engine only reads what it needs." Columnar storage means the engine reads exactly the columns you named — and you named all of them (Projection Pushdown). - "We are hitting a limit, we need more capacity." On this architecture the usual cause is skew or a missing filter. Capacity is not the constraint and adding it is not the fix (Data Skew).
Operating it
- Bytes processed per query, aggregated per model, per dashboard and per user. It is the single most informative number this architecture exposes (Scan Cost, Cost Attribution).
- Partitions eliminated versus partitions scanned per query. A ratio near one means the layout is not doing anything for that workload (Partition Pruning).
- Shuffle volume per stage for the heavy transformations — the signal that a join is repartitioning far more than it needs to (The Shuffle).
- Slot-time or worker-time distribution across the stages of a query, which is where skew shows up as one stage dominating the total (Straggler Tasks, Tail Latency: Why p50 Being Fine Does Not Help).
- At 10x data, an unpartitioned table stops being viable and the partition decision becomes the whole conversation. Nothing else in the architecture needs to change.
- At 100x, the partition column choice and the shape of the biggest joins decide everything. Elimination is the only lever, so a workload whose predicates do not align with the partition column has no remedy short of a rewrite (Physical Data Layout).
- Consumer count scales scanned bytes almost linearly unless shared aggregates are materialised, because every new dashboard is a new independent scan of the same fact table (Data Marts).
- Bytes read from columnar storage, which is decided by partition elimination and by which columns you name — the two things fully under your control (Projection Pushdown).
- Bytes moved through shuffle during joins and aggregations, decided by model grain and join keys (The Shuffle).
- Bytes retained, including long-tailed history nobody queries and snapshot retention that exists whether or not it is used (Storage Lifecycle).
- Repeated work: identical aggregates computed independently by many dashboards, and full rebuilds of models that could be incremental (Incremental Processing, Compute Waste).
- Background reorganisation, which is real work the platform does on your behalf and is driven by how you write rather than by how you query (File Compaction).
- Giving up file-level control buys a platform that never asks you to size anything and costs you the ability to fix a layout problem with anything other than partitioning, clustering and the query text.
- Partitioning by date is nearly always right and is still a commitment: it optimises the predicate most queries carry and does nothing for the workload that filters on something else.
- Materialising shared aggregates cuts repeated scanning and adds another model to build, test, backfill and keep fresh — a real ongoing obligation, not a free saving (Model Layering).
Partition explorer
Change an input and watch which number moves — and which one does not. Everything here comes from a model in this repository, not from a measurement.
Where this applies
Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.
- WAREHOUSE-SPECIFICThe partition-and-cluster surface and the absence of any capacity lever are specific to this architecture: on a shared-nothing warehouse a distribution key and node count are your main tools instead, and on a lakehouse you control file size and compaction directly, so advice does not transfer in either direction.
- CLOUD-SPECIFICThis engine exists on one provider and its behaviour is coupled to that provider's storage and network; the same architectural pattern on another cloud is realised by a different product with different pruning, different loading paths and different concurrency behaviour.
- GENERALThe underlying primitives — columnar storage, coarse elimination by partition, fine elimination by sort order and block statistics, shuffle-separated execution stages — are common to every analytical engine, and are the part of this lesson worth memorising.
Where the depth lives
This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.
- — Distributed Systems owns why a shuffle is the expensive part of a distributed query and what a coordinator must do when a worker disappears mid-stage.
- — DevOps / Production Engineering owns how SQL models are versioned, reviewed and deployed against this warehouse, and how a bad model change is rolled back.