Data Warehousing
Intermediate 13 min read Data Warehousing

BigQuery

Serverless column storage with the bill coming via slots

BigQuery is Google's serverless columnar warehouse: slot-based pricing, Dremel tree execution for massive scans, partitioned and clustered tables, and TIME-BASED snapshots.

BigQueryserverlessslotspartitioningclusteringDremel
Prerequisites: Warehouse Architecture
Before we start

What you will learn

  • Explain slot-based pricing and on-demand vs reservations
  • Design partitioned + clustered tables for scan reduction
  • Use snapshots and time travel responsibly
Introduction

What is this?

BigQuery separates a columnar store from a serverless compute engine (Dremel tree), priced by bytes scanned or reserved slots. Partitioning by date and clustering by attributes cut both cost and latency, because every query 'pays' for data it scans.

Motivation

Why do you need it?

  • Cost is a function of bytes scanned — modeling and partitioning decisions directly drive the bill.
  • Serverless scale removes cluster management but not performance engineering.
  • Understanding slots and tree execution explains why 'big queries' behave differently than in OLTP.
Think about it

Mental model

Imagine

A buffet billed by the plate (bytes), not by the head

Think of

BigQuery on-demand pricing

Ordering a huge platter of columns you'll never eat costs real money. A well-partitioned menu (date) and clustered ordering (attribute) let you take only the plates that answer the question.

Mechanics

How it works

  • Data lives in columnar (Capacitor) format; partitioning splits logical segments by a date/partition column.
  • Clustering orders rows within partitions so range filters prune blocks without scanning all of them.
  • Queries run on slots allocated ad-hoc (on-demand) or reserved; slot consumption = active queries × machine-seconds.
  • BigQuery DML replaces rows, with time travel (7-day) and snapshots (project-level retention) for recovery.
  • Caching reuses results for identical queries within 24h, counting scans only when cached data is reused or expired.

Partitioned + clustered table prune path.

Table partitioned
Partition chosen
Cluster blocks
Pruned blocks
Result
Reference

Key commands

SELECT SUM(total_bytes_billed) FROM `region-...`.INFORMATION_SCHEMA.JOBS_BY_USER;

Trace bytes billed per day to catch scan-bloat.

ALTER TABLE shop.orders SET OPTIONS (partition_expiration_days = 90);

Auto-drop old partitions to bound storage.

CREATE SNAPSHOT TABLE shop.orders_snap CLONE shop.orders FOR SYSTEM_TIME AS OF TIMESTAMP(...);

A controlled snapshot for recovery.

Hands-on

Examples

Making money off a date filter

Making money off a date filter
-- partitioned + clustered table
CREATE OR REPLACE TABLE shop.orders
PARTITION BY d
CLUSTER BY customer_id AS SELECT * FROM source;
 
-- Both of these now prune to a slice, not the whole table:
SELECT sum(amount) FROM shop.orders WHERE d = '2026-01-05';
SELECT customer_id, count(*) FROM shop.orders
WHERE d BETWEEN '2026-01-01' AND '2026-01-31'
GROUP BY customer_id;

Partition eliminates whole slices; clustering trims blocks even within a chosen partition.

Real world

In production

  • An events team partitions fact_event by event_date and clusters by user_id; ad-hoc funnels scan only the needed days.
  • A cost controller watches INFORMATION_SCHEMA billing to catch a dashboard that SELECT *'d a 2TB table.
  • Standardizing 'date or snapshot' on every fact keeps TIME TRAVEL practical for incident recovery.
Guidelines

Best practices

  • Partition by the dominant time predicate; cluster by the next most common high-cardinality dimension.
  • Select only needed columns; 1TB scanned for 4 columns is the most common expensive habit.
  • Use session/batch reservations for large, predictable jobs instead of on-demand pricing swings.
  • Wrap periodic snapshot exports for retention beyond the 7-day time-travel window.
Watch out

Common mistakes

  • SELECT * from a giant fact via a BI tool, blowing the monthly bill on one dashboard.
  • Partitioning by a high-cardinality non-date value (200k partitions cap, cardinality overhead).
  • Forgetting clustering means blocks, not guarantees — a perfectly partitioned table still scans whole clusters without the right clustering.
When things break

Debugging tips

  • Cheap-ish but 'slow' queries often mean slot contention; check reservations utilization.
  • A query that scanned huge bytes despite a date filter = partitioning/clustering not applied (verify table DDL).
  • Use dry-run (query validation with bytes estimate) before executing scary SELECTs.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2
1

BigQuery on-demand pricing is primarily based on…

2

Clustering sorts rows within partitions so range filters can prune blocks.

Go deeper

Official documentation

BigQuery pricing
docs
Partitioning & clustering
docs
BigQuery architecture
docs