Advanced13 min read

BigQuery Deep Dive: Performance and Cost

How BigQuery actually runs a query, why partitioning and clustering matter more than anything else, and how to answer the cost-optimisation questions that always come up.

BigQuery is a serverless, columnar, distributed warehouse. There are no clusters to size. That shifts the whole optimisation conversation from 'how much hardware' to 'how few bytes can I touch'.

How a query actually runs

Data lives in Colossus (distributed storage) in Capacitor columnar format — not on the compute nodes
Compute runs as slots, BigQuery's unit of CPU. A query is broken into stages executed across many slots
Stages exchange data through an in-memory shuffle tier
Because storage and compute are separate, only the columns and partitions you reference are read

Tip — The one-liner that shows you get it

'BigQuery bills on bytes scanned, and it is columnar — so SELECT * on a wide table is the single most expensive mistake you can make, even with a LIMIT 10. LIMIT does not reduce bytes scanned.'

Partitioning and clustering — the two big levers

Partitioning eliminates whole date slices. Clustering sorts data inside each partition so BigQuery can skip blocks.

	Partitioning	Clustering
What it does	Physically splits the table into segments	Sorts data within each partition
Typical column	Date/timestamp (`event_date`), integer range, or ingestion time	High-cardinality filter/join keys (`cell_site_id`, `subscriber_id`)
Limit	Up to 4,000 partitions per table	Up to 4 columns, order matters
Cost effect	Prunes whole partitions — huge, predictable savings	Skips blocks — savings vary with data layout
Best for	Time-range filters, cheap partition-level reloads	Selective equality filters and joins

create table core.network_kpi_daily (
  event_date    date       not null,
  cell_site_id  string     not null,
  subscriber_id string,
  total_gb      numeric,
  drop_rate     float64
)
partition by event_date
cluster by cell_site_id, subscriber_id
options (
  require_partition_filter = true,   -- stops accidental full-table scans
  partition_expiration_days = 400
);

Watch out — require_partition_filter is your cost guardrail

It forces every query to filter on the partition column, so nobody can accidentally scan five years of data. Naming this unprompted is a strong signal that you have owned a real BigQuery bill.

Cost optimisation checklist

Select only needed columns — never SELECT * on wide tables
Always filter on the partition column and enforce it with require_partition_filter
Materialise repeated heavy aggregations into summary tables instead of recomputing per dashboard
Use incremental models so you rebuild one day, not the whole history
Set expirations on partitions and staging datasets so junk data stops accruing storage cost
Use `--dry_run` / the UI byte estimate in CI to catch expensive queries before they merge
Choose the right pricing — on-demand for spiky/unpredictable, editions/slot reservations for steady heavy load
Avoid `SELECT DISTINCT` on huge sets when APPROX_COUNT_DISTINCT is good enough

Loading data efficiently

Method	Cost	Latency	Use when
Batch load from GCS	Free	Minutes	Standard ELT — the default choice
Storage Write API	Paid	Seconds	Streaming ingestion; exactly-once with stream offsets
Legacy streaming inserts	Paid, pricier	Seconds	Legacy only — prefer Storage Write API
External / BigLake tables	Query-time	n/a	Query data in GCS without loading it

Practice questions

QA dashboard query costs far too much. How do you bring the cost down?show

Work top-down: reduce bytes scanned first, then reduce how often the scan happens.

Check the bytes scanned in the query plan — that is the bill, not the runtime
Trim to the columns actually used; wide SELECT * is usually most of the cost
Verify the partition filter is present and pruning — a filter on a non-partition column does nothing for cost
Add clustering on the frequent equality filters
Pre-aggregate into a daily summary table so the dashboard reads thousands of rows, not billions
Consider a materialized view or BI Engine for repeated dashboard traffic

Likely follow-up: How would you stop this from recurring across the team?

QWhat are slots, and what happens when you run out?show

A slot is a unit of compute. Queries are split into stages, and stages consume slots in parallel.

On-demand — you pay per byte scanned, with a large shared slot pool and no capacity guarantee
Editions / reservations — you buy a fixed slot capacity, pay for it regardless of use, and get predictable performance
When capacity is exhausted, queries queue rather than fail — the symptom is variable latency, not errors
Use reservations with assignments to stop an ad-hoc analyst query from starving production ETL

QHow do you handle updates and deletes in BigQuery?show

BigQuery is optimised for append and bulk rewrite, not row-level OLTP updates. Use MERGE for upserts and partition overwrites for reloads.

MERGE — the standard upsert, ideal for SCD Type 2 and idempotent streaming sinks
Partition overwrite — for a daily reload, replace the whole partition; simple, cheap, idempotent
Avoid many small UPDATE/DELETE statements — each rewrites storage blocks and they hit quota limits
Deletes on a partitioned table are far cheaper when they align to whole partitions

QWhat is the difference between a view, a materialized view, and a table?show

They trade freshness against cost and speed.

View — stored SQL, re-executed every time; no storage cost, no speed benefit
Materialized view — precomputed and incrementally refreshed by BigQuery; fast and automatically used to rewrite matching queries, but supports limited SQL
Table (built by dbt) — full control, any SQL, any refresh schedule; you own the freshness
For a layered dbt architecture, most core/mart models are tables or incremental tables precisely because you control when and how they refresh

← PreviousSpark Structured Streaming and Exactly-Once Next →GCP Data Services: Choosing the Right Tool