BigQuery

BigQuery is a serverless data warehouse. You load data, write SQL, and receive results. There is no cluster to size, no instance to keep running and no capacity to plan for a query that scans a hundred times more data than the last one.

Two design decisions explain its behaviour. Storage and compute are entirely separate, so you pay for them independently and idle storage is cheap. And it stores data in columns rather than rows, so a query reading three columns from a thousand-column table reads only those three.

For analytical workloads, the separation of storage and compute changes what is economically possible. You can keep years of history at rest cheaply and only pay meaningfully when you query it.

It is also the centre of gravity for the Data Engineer exam and appears throughout the Architect and ML Engineer exams. Understanding it well pays across several certifications.

In the default on-demand model you pay for the bytes a query reads, not for how long it takes or how complex it is. That single fact explains nearly every optimisation technique.

• SELECT * is expensive.** Because storage is columnar, selecting every column reads every column. Selecting only what you need is the cheapest optimisation available.
• A `WHERE` clause does not automatically reduce cost. Filtering on an ordinary column still requires reading that column across the whole table. Only partitioning and clustering reduce bytes read.
• `LIMIT` does not reduce cost. The data still has to be scanned to determine which rows to return.
• Storage has two tiers. Data not modified for a period automatically moves to long-term storage at a lower rate, with no change in query behaviour.
• Capacity pricing — reserving dedicated compute instead of paying per query. Predictable, and cheaper above a certain steady volume.

These are the two mechanisms that genuinely reduce how much data a query reads, and understanding the difference is reliably examined.

They are complementary rather than alternatives. Partition by date and cluster by the columns you filter on most. On a large table, requiring a partition filter is one of the most effective guardrails against accidental expensive queries.

• Dataset — a container for tables, holding location and default access settings. Its location is fixed once created.
• Table — standard tables store data in BigQuery; external tables query data left in object storage.
• View — a saved query. An authorised view lets someone query results without having access to the underlying table, which is the standard way to share a filtered subset.
• Materialised view — a precomputed, automatically refreshed result. Faster and cheaper for repeated aggregations.
• Slot — the unit of compute. Queries are allocated slots; contention for them is what makes a query wait.
• Streaming inserts — writing rows continuously rather than in batch loads, for near-real-time availability.
• Column-level and row-level security — restricting access to specific columns or filtering rows by the querying identity.
• In-warehouse machine learning — training and running models with SQL, without moving data out.

• Central analytics warehouse consolidating data from many source systems.
• Ad-hoc analysis over years of history without provisioning anything in advance.
• Near-real-time dashboards fed by streaming ingestion.
• Machine learning feature engineering over very large datasets.
• Sharing curated datasets with other teams or organisations without copying data.
• Log analytics, where logs are routed into the warehouse for querying over long retention periods.