Home / Learn / Data & AI / Data at Scale / Lesson 3.3
All lessons

Module 3: The Google Cloud data stack · Lesson 3.3 · about 30 min

BigQuery: the warehouse with no servers

How BigQuery stores data and runs queries, why on-demand cost follows bytes scanned, partitioning and clustering, nested data, and habits that keep bills small.

Key points, meanings and formulas on one page: choose "Save as PDF" in the print box.

Module 3 · about 30 minutes · read first: 3.1, 3.2. You’ll be able to explain columnar storage, slots and why BigQuery charges by bytes scanned, build partitioned and clustered tables that scan less, and query nested data with UNNEST.

BigQuery reads only the columns and partitions your query touches, and on demand you pay for exactly those bytes, so writing cheaper SQL means sending the engine to fewer pages.

1. What makes BigQuery different

BigQuery is a fully managed, serverless warehouse. There is no cluster to size: you write SQL, and Google’s engine (descended from its internal Dremel system) spreads the query over many machines for a few seconds. Under the hood:

  • Columnar storage: each column is stored separately, so reading one column never touches the others.
  • Storage and compute are separate, joined by a very fast network, so each grows on its own.
  • Slots are units of compute. A query is broken into stages, and each stage runs on many slots in parallel.

Picture a giant library with instant helpers. You hand your question to the desk. Hundreds of helpers run to the shelves, each reads only the pages (columns) and sections (partitions) needed, and the answer comes back in seconds. You are charged for how many pages were read, not how long the helpers stood around. So the trick is asking questions that send them to fewer pages.

2. Pricing in plain words

  • Storage: per GB per month. Data not changed for 90 days drops to a cheaper long-term rate automatically.
  • Compute, on demand: per TiB scanned by each query, with a free amount each month. A query reading 50 GB costs a small fraction of the rate; one reading a 40 TB table every hour can run to thousands a month.
  • Compute, capacity (editions): reserve slots and pay for slot-hours instead of bytes. Large companies use this for predictable bills.

Check today’s prices on Google’s pricing page. The shape of the bill matters more than the exact number, and it is always the same: bytes scanned × rate.

3. Partitioning and clustering

CREATE TABLE retail.sales
PARTITION BY sale_date
CLUSTER BY store_id, sku
OPTIONS (require_partition_filter = TRUE)
AS SELECT * FROM retail.sales_raw;
  • Partitioning splits the table by a date or timestamp column (or ingestion time, or an integer range). Filters on it skip whole partitions, like going straight to one drawer. require_partition_filter stops anyone from accidentally scanning all of history.
  • Clustering sorts the data inside each partition by up to four columns. Filters on them skip blocks, like a drawer whose folders are in alphabetical order. Order matters: put the most-filtered column first.

Watch the same sales question asked four ways on a table with two years of sales, 30 million rows a day and about 20 columns:

SELECT * over all history. Every column of every day: about 3.5 TB read. This is what a careless "let me just look" query costs.
Only the 2 columns you need. SELECT store_id, revenue reads 2 of 20 columns: about 350 GB. Ten times less, from one change.
Add a filter on the partition column. WHERE sale_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) reads 7 of 730 daily partitions: about 3.4 GB.
Add a filter on the cluster column. AND store_id = 214 lets BigQuery skip most blocks inside each day: well under 1 GB. The exact saving depends on block sizes, so check the bytes in the job details.

Prove it yourself: before you run a query, the editor shows the bytes it will process (a free dry run). Compare the numbers with and without your filter.

4. SQL you will use often

-- One row per item from a nested order table
SELECT o.order_id, item.sku, item.qty
FROM retail.orders AS o
CROSS JOIN UNNEST(o.items) AS item;

-- Approximate distinct count: much cheaper on billions of rows
SELECT campaign_id, APPROX_COUNT_DISTINCT(user_id) AS reach
FROM ads.impressions
WHERE event_date BETWEEN '2026-10-01' AND '2026-10-31'
GROUP BY campaign_id;

-- Keep the latest version of each row
SELECT * FROM retail.customers_cdc
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) = 1;

-- Upsert yesterday's changes into a dimension
MERGE retail.dim_product t USING staging.product_changes s ON t.sku = s.sku
WHEN MATCHED THEN UPDATE SET t.price = s.price, t.updated_at = s.updated_at
WHEN NOT MATCHED THEN INSERT ROW;

Nested and repeated fields (an ARRAY of STRUCTs) keep an order and its items in one row, like a receipt with its lines printed on it, which saves a join. UNNEST unrolls the lines into rows when you need them.

5. The rest of the toolbox

  • Loading: batch loads from Cloud Storage (load jobs themselves are not charged as queries), the Storage Write API for streaming, Datastream for database changes, and the Data Transfer Service for Google Ads and other sources.
  • Materialised views keep aggregates fresh automatically; BI Engine caches data in memory for fast dashboards.
  • BigQuery ML: train models with SQL (CREATE MODEL ... OPTIONS(model_type='linear_reg')), and call Gemini models from SQL. The ML foundations course explains what those models do.
  • Security: dataset and table permissions, row-level security, column-level policy tags to hide personal data, and authorised views for sharing.

Its rivals are Snowflake, Amazon Redshift, Databricks SQL and Microsoft Fabric. The SQL skills transfer almost entirely.

Bytes scanned
The total size of the columns and partitions a query reads. The on-demand bill is this times the rate.
Partition
A slice of a table by date (or integer range) that a filter can skip entirely.
Cluster
A sort order inside each partition, so filters on those columns skip blocks.
Slot
One unit of compute; a query runs on many at once.
UNNEST
Turns an array inside a row into separate rows.
Physical meaning
A dashboard that refreshes every 15 minutes runs its query about 2,900 times a month. If each run scans a year of history instead of a week, the bill is roughly 50 times bigger for the same picture. One partition filter is often the cheapest saving a team will ever make.

“Adding LIMIT 10 makes a query cheap.” LIMIT only shortens the result you see. BigQuery still reads every byte of the columns and partitions you asked for, and bills them. To look at a few rows for free, use the table’s Preview tab; to cut cost, select fewer columns and filter on the partition column.

6. Try it

In the BigQuery sandbox, using the public dataset bigquery-public-data.thelook_ecommerce (a fictional online clothing shop):

  1. Total revenue by month and product category from order_items joined to products. Note the bytes processed.
  2. Create your own copy of order_items partitioned by DATE(created_at) and clustered by product_id:
CREATE TABLE mylab.order_items_p
PARTITION BY DATE(created_at)
CLUSTER BY product_id AS
SELECT * FROM `bigquery-public-data.thelook_ecommerce.order_items`;
  1. Run the same one-month query on both tables and compare bytes processed.
  2. Find each user’s first order date with QUALIFY.

What to check: the partitioned table’s one-month query processes a small fraction of the original’s bytes, and step 4 returns one row per user.

Cost habits: never SELECT * on big tables; filter on the partition column; set a maximum bytes billed on scheduled queries; and look at INFORMATION_SCHEMA.JOBS weekly for the most expensive queries.

New words: serverless, columnar, slot, on-demand pricing, capacity pricing, partitioning, clustering, dry run, nested and repeated fields, UNNEST, QUALIFY, MERGE, materialised view.

  • BigQuery is serverless and columnar: storage and compute are separate, and queries run on slots.
  • On demand, cost = bytes scanned × rate. LIMIT does not reduce it; fewer columns and partition filters do.
  • Partition by the date people filter on; cluster by up to four frequently filtered columns.
  • Check bytes before running (dry run) and set maximum bytes billed on scheduled queries.
  • Nested data with UNNEST, QUALIFY for “latest row”, and MERGE for upserts cover most daily SQL.

Check yourself

1On on-demand pricing, which query costs less?
2Clustering a table by store_id helps most when queries...
3What is a BigQuery slot?

Sources

Your ticks stay in this browser. Save them as a file on the course home.

Updated 2026-11-22. All company data in the examples is synthetic. Tool versions and prices change: check the official docs before you rely on them.