BigQuery Partitioning + Clustering: Cost & Performance Tuning Playbook

bigquery partitioning is the single highest-leverage table-design decision a senior data engineer makes on Google Cloud — the lever that turns a 5 TB scan into a 50 GB scan, that turns a $25 query into a $0.25 query, and that decides whether a junior analyst's missing WHERE event_date = … lights the on-call pager at 3 a.m. Partition the right column the right way and the query planner prunes 99% of the table before the first byte is read. Pick the wrong column, or skip clustering, or forget require_partition_filter, and you are paying full-scan prices every time a dashboard refreshes.

This guide is the senior-DE playbook you wished existed the first time an interviewer asked you to design a 10 TB events table on BigQuery, or to explain why their bigquery cost optimization initiative stalled, or to debug why a partitioned table still costs $40 per query. It walks through every partition flavour (DATE, TIMESTAMP, integer-range, ingestion-time _partitiontime), how bigquery clustering re-organises blocks within a partition, the way bigquery partition by and bigquery cluster by clauses compose in DDL, why partition pruning bigquery only fires when the planner can resolve the filter at plan time, how require_partition_filter makes accidental full scans physically impossible, and the 5-step tune-a-table runbook that turns query-log evidence into a partition + cluster recipe. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse on the optimization problems →, and stack the warehouse axis with the ETL practice set → and the aggregation library →.

---

On this page

• Why partitioning + clustering is the BigQuery cost lever
• Partition flavours — time, integer-range, ingestion-time
• Clustering — within-partition organisation
• require_partition_filter + cost guardrails
• The 5-step tune-a-table runbook
• Cheat sheet — partition + cluster recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why partitioning + clustering is the BigQuery cost lever

BigQuery bills by data scanned — every byte you do not read is money you do not spend, and partitioning is the only mechanism that prunes scan ranges before the planner reads a single block

The one-sentence invariant: BigQuery charges per byte of data scanned (or per slot-second on flat-rate / autoscaler reservations), partitioning prunes which physical partitions the planner reads, and clustering prunes which blocks inside each partition the planner reads. Every cost conversation, every performance complaint, every "the query is slow" Slack thread on a BigQuery team reduces to two questions: how many bytes did the planner have to scan, and how many of those bytes were actually relevant to the query. Partitioning answers the first; clustering answers the second.

The four "must-answer" design axes.

• Partition column. Almost always a DATE or TIMESTAMP column drawn from the query patterns — event_date, created_at, _PARTITIONTIME. Pick the one that appears in the WHERE clause of 80%+ of queries against the table. The wrong choice (partitioning by country_code when nobody filters by country) is worse than no partitioning at all.
• Partition type. Time-unit (DAY / HOUR / MONTH / YEAR), integer-range (RANGE_BUCKET(id, GENERATE_ARRAY(…))), or ingestion-time (_PARTITIONTIME populated automatically by the streaming or load job). The right choice depends on cardinality, predicate shape, and whether the upstream system can backfill arbitrary partition values.
• Cluster columns. Up to four, ordered by selectivity from most selective to least selective. A cluster column reorganises blocks within a partition so that WHERE cluster_col = 'X' reads only the blocks where cluster_col = 'X' appears.
• require_partition_filter. TRUE means every query against the table must include a partition filter or the planner throws an error. Turning this on is the cheapest, highest-leverage safety net BigQuery offers and most teams turn it on too late, after the first surprise $5,000 query.

The 2026 reality — what changed in the last two years.

• BigQuery now supports up to 10,000 partitions per table (was 4,000 before 2022). For DAY partitioning, that's ~27 years of daily history — enough that monthly partitioning is almost always wrong; pick DAY by default.
• Clustering is fully automatic for new writes — BigQuery runs background re-clustering for free, so you do not pay slot-seconds to re-sort blocks after large loads. LAST_REFRESH_TIME in INFORMATION_SCHEMA exposes when each table was last re-clustered.
• _PARTITIONTIME and _PARTITIONDATE remain available for ingestion-time partitioning but Google now recommends column-based DATE/TIMESTAMP partitioning for any new table — it's more predictable and survives backfills.
• Integer-range partitioning (RANGE_BUCKET) is the right answer when the dominant filter is user_id or customer_id and the IDs are dense. It is dramatically underused.
• BigQuery editions (Standard / Enterprise / Enterprise Plus) push capacity reservations as the cost model, but the bytes-scanned mental model is still the right way to reason — slot-seconds correlate strongly with bytes processed once you account for the join shape.

What interviewers actually probe.

• Do you say "BigQuery prunes partitions at plan time and blocks within partitions at scan time" without prompting? — senior signal.
• Do you describe clustering as "up to 4 columns, ordered by selectivity, free background re-clustering"? — required answer.
• Do you mention require_partition_filter as the only safe default for any table over 100 GB? — senior signal.
• Do you push back on "just partition by month" with "DAY partitioning is the default unless you have measured cardinality reasons not to"? — senior signal.

Worked example — same query, partitioned vs unpartitioned

Detailed explanation. The classic interview question: "Here is a 5 TB events table. Here is a query. Why does it cost $25 today and how do you make it cost less?" The answer requires you to walk through the bytes-scanned model and connect partitioning to the scan reduction. We will compare the same COUNT(*) query against an unpartitioned and a DAY-partitioned table and show the planner's "bytes processed" estimate dropping by two orders of magnitude.

Question. Given a 5 TB events table (one row per page-view, columns: event_date DATE, user_id STRING, page_id STRING, payload STRING), compare the bytes scanned and dollar cost of SELECT COUNT(*) FROM events WHERE event_date = '2026-06-10' when the table is unpartitioned vs when it is PARTITION BY event_date.

Input.

Variant	Rows	Total bytes	Partition column	event_date = '2026-06-10' rows	Daily partition bytes
Unpartitioned	5 B	5 TB	none	5 M	n/a
Partitioned by event_date	5 B	5 TB	event_date	5 M	~5 GB

Code.

-- Unpartitioned variant
CREATE TABLE analytics.events_unpartitioned AS
SELECT event_date, user_id, page_id, payload
FROM analytics.events_source;

SELECT COUNT(*)
FROM analytics.events_unpartitioned
WHERE event_date = '2026-06-10';
-- Bytes scanned: ~5 TB (whole table)

-- Partitioned variant
CREATE TABLE analytics.events_partitioned
PARTITION BY event_date
AS
SELECT event_date, user_id, page_id, payload
FROM analytics.events_source;

SELECT COUNT(*)
FROM analytics.events_partitioned
WHERE event_date = '2026-06-10';
-- Bytes scanned: ~5 GB (one daily partition)

Step-by-step explanation.

1. The unpartitioned table is one giant blob of columnar storage. The planner has no way to skip bytes; even though only one day matters, every block from every day is read, decompressed, and filtered in memory.
2. BigQuery's on-demand pricing is roughly $6.25 per TB scanned (2026 list price; varies by region and edition). 5 TB → ~$31 per query. Repeat that query 100 times a day across dashboards and the table costs ~$3,100 a day to query.
3. The partitioned variant stores rows in physically separate partitions, one per event_date value. The query planner sees the constant filter event_date = '2026-06-10', resolves it to a single partition at plan time, and only opens that partition's storage.
4. The daily partition is ~5 GB (5 TB / ~1,000 days assuming even distribution). 5 GB → ~$0.03 per query — a 1,000× cost reduction on the dominant query against the table.
5. Equally important: the partitioned variant is faster. Wall-clock time drops from ~30 seconds to ~1 second because the slot pool spends fewer slot-seconds reading and decompressing irrelevant data.

Output.

Variant	Bytes scanned	On-demand cost	Wall-clock (typical)
Unpartitioned	5 TB	~$31.25	~30 s
Partitioned by DAY	5 GB	~$0.03	~1 s

Rule of thumb. Any table that grows monotonically over time (events, logs, transactions, snapshots) gets DAY partitioning on the time column by default. Skip this only with a measured reason — and the measurement has to come from the query log, not from a hunch about "we might want to filter by region instead."

Worked example — partition column wrong vs right

Detailed explanation. Partitioning is not magic; partitioning on a column that the queries do not filter on is dead weight. A table partitioned by country_code when 90% of queries filter on event_date and 0% filter on country_code is paying the partition overhead (one storage segment per country, metadata bloat, slower writes) and getting zero pruning. The lesson: always pick the partition column from the query log, never from the schema documentation.

Question. Given a query log where 95% of queries filter on event_date and 5% filter on country_code, justify partitioning by event_date and not country_code. Show the resulting bytes-scanned distribution on both partition choices.

Input.

Query family	Frequency	Filters
Daily dashboard	60%	event_date = …
Backfill / ad-hoc analytics	35%	event_date BETWEEN …
Country breakdown	5%	country_code IN (…)

Code.

-- Right answer — partition by event_date
CREATE TABLE analytics.events_by_date
PARTITION BY event_date
CLUSTER BY country_code, user_id
AS SELECT * FROM analytics.events_source;

-- Wrong answer — partition by country_code (low-cardinality string)
-- BigQuery rejects this for non-DATE/TIMESTAMP/integer columns,
-- but a junior engineer might try INTEGER country_id instead:
CREATE TABLE analytics.events_by_country
PARTITION BY RANGE_BUCKET(country_id, GENERATE_ARRAY(0, 250, 1))
AS SELECT *, FARM_FINGERPRINT(country_code) AS country_id FROM analytics.events_source;

Step-by-step explanation.

1. The dominant query family (95% combined) filters on event_date. Partitioning on event_date lets the planner prune to a single day or a small range of days in every one of those queries.
2. The minority country query (5%) only filters on country_code. If we partition by country, the dominant queries cannot prune partitions at all — they hit every country partition, scanning the full table.
3. The trick: cluster by country_code (after partitioning by event_date). Within each daily partition, BigQuery reorders blocks so that the country-filtered query reads only the blocks where the requested countries appear. That gives the 5% query family fast access without sacrificing the 95%.
4. The wrong answer (partition by country) also runs into BigQuery's constraint that partition columns must be DATE, TIMESTAMP, DATETIME, or INTEGER. Forcing a hash to make country_id integer is a code smell; the proper answer is cluster, not partition.
5. The query log is the only data that matters here. A schema-doc-driven partition choice is hand-waving; a query-log-driven choice is engineering.

Output.

Strategy	Daily dashboard scan	Country query scan
Partition by event_date, cluster by country_code	5 GB (one day)	50 GB (full month if no date filter, else clustered)
Partition by country_code only	250 GB (one country, all dates)	50 GB (one country)

Rule of thumb. The partition column wins the 95% case. The cluster column wins the 5% case. Picking partition for the rare query is the most common BigQuery cost regression and the easiest one to fix in an interview answer.

Worked example — bytes-scanned dry-run before every change

Detailed explanation. A senior data engineer never ships a partitioning change without measuring. BigQuery exposes a dry-run mode that returns the bytes the query would scan, without running it. Wrap every "I think this will help" with a dry-run delta — before and after — and you have proof, not opinion.

Question. Show how to use bq query --dry_run (or dryRun: true via the API) to verify the bytes-scanned drop after partitioning a table. Include a small wrapper script that diffs the two estimates.

Input.

Step	Tool	Input
1	bq query --dry_run	SQL targeting unpartitioned table
2	bq query --dry_run	Same SQL targeting partitioned table
3	shell math	Diff the two totalBytesProcessed values

Code.

# Step 1 — dry-run against unpartitioned variant
BYTES_BEFORE=$(bq query --dry_run --use_legacy_sql=false \
  --format=json \
  "SELECT COUNT(*) FROM analytics.events_unpartitioned WHERE event_date = '2026-06-10'" \
  | jq -r '.statistics.totalBytesProcessed')

# Step 2 — dry-run against partitioned variant
BYTES_AFTER=$(bq query --dry_run --use_legacy_sql=false \
  --format=json \
  "SELECT COUNT(*) FROM analytics.events_partitioned WHERE event_date = '2026-06-10'" \
  | jq -r '.statistics.totalBytesProcessed')

# Step 3 — diff
echo "Bytes before: $(numfmt --to=iec $BYTES_BEFORE)"
echo "Bytes after:  $(numfmt --to=iec $BYTES_AFTER)"
python3 -c "print(f'Reduction: {(1 - $BYTES_AFTER / $BYTES_BEFORE) * 100:.2f}%')"

Step-by-step explanation.

1. --dry_run asks the BigQuery planner to estimate bytes scanned without executing. The estimate is exact for fully-pruned queries (the planner knows which partitions it would read) and approximate for queries with runtime filters (e.g. subqueries that resolve at execution time).
2. The first call estimates the unpartitioned baseline. Store the byte count.
3. The second call estimates the partitioned variant. Store that byte count.
4. The shell math computes the percentage reduction. Drive every partition-or-cluster change with this delta: if the dry-run says < 90% reduction on the dominant query, the partition choice is wrong.
5. CI-friendly: drop the wrapper into a pre-merge check that blocks any new query with bytes-scanned estimate above a threshold (say 1 GB), or that fails to include a partition filter.

Output.

Run	totalBytesProcessed	Notes
Before	5,497,558,138,880	5 TB
After	5,368,709,120	5 GB
Reduction	—	99.90%

Rule of thumb. Dry-run is free. There is no excuse to ship a partition change without one. Every PR that touches table DDL should include a "before / after / delta" comment block citing dry-run output for the top-N queries against the table.

Senior interview question on partition + cluster choice

A senior interviewer often opens with: "Imagine a 10 TB events table on BigQuery. Walk me through how you'd choose the partition column, the partition type, the cluster columns, and whether to enforce require_partition_filter. What evidence drives each decision?"

Solution Using the query-log evidence framework

Partition + cluster — evidence-driven framework
===============================================

Step 1 — Pull the last 30 days of queries against the table
  bq query --use_legacy_sql=false """
    SELECT query, total_bytes_processed
    FROM region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT
    WHERE referenced_tables LIKE '%events%'
      AND creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  """

Step 2 — Tally WHERE-clause filter columns
  Most frequent filter column = partition column candidate
  Next 2–3 frequent columns    = cluster column candidates

Step 3 — Pick partition column
  - DATE / TIMESTAMP in 80%+ of queries → time partition (DAY default)
  - Integer ID in 80%+ of queries        → integer-range partition
  - Otherwise: cluster, do not partition

Step 4 — Pick cluster columns
  - Up to 4, ordered MOST selective first
  - Selectivity ≈ NDV(column) / row_count
  - Drop any column with selectivity < 0.001 — it won't help

Step 5 — Enforce require_partition_filter = TRUE
  - On any table > 100 GB
  - Combined with default partition expiration

Step-by-step trace.

Step	Evidence input	Decision	Why
1	INFORMATION_SCHEMA query log	30-day query log of events	Need a quantitative basis
2	filter-column tally	event_date 92%, user_id 41%, country_code 18%, page_id 6%	Reveals which columns to prune on
3	partition column	event_date (DAY partitioning)	Dominant filter is time-based
4	cluster columns	user_id, country_code, page_id	Highest-selectivity secondary filters
5	require_partition_filter	TRUE	Table is 10 TB — protect against full scans

Output:

Decision	Choice
Partition	DAY on event_date
Cluster	user_id, country_code, page_id
require_partition_filter	TRUE
Default partition expiration	730 days

Why this works — concept by concept:

• Query-log evidence over schema documentation — partition + cluster choices are consequences of the workload, not consequences of the entity-relationship diagram. The same events table at a different company may want a different partition column.
• DAY partitioning as the default — BigQuery's 10,000-partition ceiling makes DAY partitioning safe for ~27 years of daily history. Monthly partitioning is almost always wrong on event tables because it leaves daily filters unprunable.
• Cluster column ordering — clustering uses the first N columns of the cluster key as a sort key. A filter that uses only column 2 still prunes some blocks, but less than a filter using columns 1 + 2 together. Most-selective first means the most common ad-hoc filter prunes maximally.
• require_partition_filter as the production safety net — the constraint is enforced at plan time. Any query missing the filter throws Cannot query over table without a filter over the partition column. Junior analysts learn the lesson without nuking the budget.
• Cost — partition pruning is O(1) at plan time (the planner just consults the partition metadata); clustering pruning is O(log B) where B is the number of blocks in a partition (the block index is a tree). Both are dramatically cheaper than scanning bytes.

SQL
Topic — SQL
SQL partition + filter practice problems

Practice →

Optimization Topic — optimization Query cost optimization drills

Practice →

---

2. Partition flavours — time, integer-range, ingestion-time

bigquery partition by ships three first-class flavours — DATE/TIMESTAMP, integer-range, and ingestion-time _PARTITIONTIME — and picking the right one is a function of the dominant filter shape

The mental model in one line: BigQuery supports three partition flavours — time-unit (DATE / TIMESTAMP / DATETIME), integer-range (RANGE_BUCKET(int_col, GENERATE_ARRAY(…))), and ingestion-time (_PARTITIONTIME) — and the right one is the one that matches the column the dominant queries filter on. Every other detail (granularity, expiration, retention) is a parameter of the chosen flavour.

Time-unit partitioning — DATE / TIMESTAMP / DATETIME.

• DAY granularity is the default. PARTITION BY DATE(event_ts) creates one storage segment per calendar day in the column. Most event / log / transaction tables get this.
• HOUR granularity for tables with intra-day filter patterns or very high daily row counts (e.g. ad-impression streams with 100M+ rows/day). Hourly buckets give faster ad-hoc 1-hour queries but multiply the partition count by 24 — watch the 10,000 limit.
• MONTH and YEAR are almost always wrong on event tables. They limit pruning granularity to 30 or 365 days at a time. The only time MONTH wins is when the dominant query is reporting (monthly revenue) and the table is small enough that daily pruning would be overkill.
• PARTITION BY DATE_TRUNC(event_ts, MONTH) is BigQuery's only way to do anything other than the default granularity on a column — the function wraps the timestamp into the bucket.

Integer-range partitioning — RANGE_BUCKET.

• PARTITION BY RANGE_BUCKET(customer_id, GENERATE_ARRAY(0, 100000, 100)) creates 1,000 partitions, each holding 100 consecutive customer_id values. Useful when the dominant filter is WHERE customer_id BETWEEN x AND y or WHERE customer_id IN (list).
• Bucket width matters. Too narrow → too many partitions (over 10,000 limit). Too wide → poor pruning on small ranges. Aim for ~1,000–5,000 buckets covering the realistic ID range.
• Sparse IDs (e.g. hashed strings cast to int) kill integer-range partitioning because most buckets are empty and the metadata overhead is wasted. Only use this flavour when IDs are dense.
• One per table — you cannot partition on time and integer in the same table. If both filters are common, pick the more frequent one and put the other on the cluster key.

Ingestion-time partitioning — _PARTITIONTIME / _PARTITIONDATE.

• PARTITION BY _PARTITIONTIME uses the time the row was loaded, not a column value. BigQuery exposes the pseudo-column automatically; you do not store it explicitly.
• Wins: zero schema impact, automatic with streaming inserts and load jobs, perfect for log tables where the load time is the relevant time.
• Loses: backfills go into whatever the load time is, not the event time — so a backfill of June data loaded in August lands in the August partitions. The cluster fix is to populate a real event_date column and partition on that.
• _PARTITIONTIME IS NULL filter targets the streaming buffer (rows that haven't yet been committed to a partition). Useful for very-recent-data dashboards.

Granularity trade-offs in practice.

• Daily, ~5 GB per partition, 1,000 days of history → ideal. Pruning is sharp, partition count well under the 10,000 limit.
• Hourly, ~200 MB per partition, 30 days of history → ideal for high-frequency tables. Pruning is finer, but the partition count is 720 instead of 30 — still safe.
• Hourly, 5 years of history → bad. 43,800 partitions — well over the limit. Either reduce retention or drop to daily.
• Monthly, daily filters → bad. A WHERE event_date = '2026-06-10' query still scans the entire June partition.

Migration between flavours.

• BigQuery does not let you change the partition column of an existing table. The migration recipe is CREATE TABLE … AS SELECT FROM old_table PARTITION BY new_col, then atomic rename via ALTER TABLE … RENAME TO … after backfill verification.
• Watch the storage cost during migration — you are paying for two copies of the data until the swap.
• For ingestion-time → column-based migration, you have to fill in the event-time column from the existing _PARTITIONTIME value during the rebuild.

Common interview probes on partition flavours.

• "When would you partition by _PARTITIONTIME instead of a column?" — when the load time is the relevant time and backfills are never out-of-order.
• "What's the partition count limit?" — 10,000 in 2026; DAY at this limit gives ~27 years of history.
• "Can you change the partition column?" — no, only via CTAS + rename.
• "When does HOUR partitioning win over DAY?" — high daily row counts plus intra-day filter patterns.

Worked example — DATE partition with daily granularity

Detailed explanation. The default and most common partition pattern: a table with an event timestamp, partitioned by the DATE() of that timestamp, queried daily. Show the DDL, an insert, and a query that prunes to one day.

Question. Create an events table partitioned by DAY on the event_ts timestamp. Insert a few rows from different days. Query a single day and confirm pruning via EXPLAIN.

Input.

Column	Type	Notes
event_ts	TIMESTAMP	event creation timestamp
user_id	STRING	hashed user id
page_id	STRING	URL path
payload	STRING	JSON event blob

Code.

-- DDL — DAY partitioning on the DATE of event_ts
CREATE TABLE analytics.events (
  event_ts TIMESTAMP NOT NULL,
  user_id  STRING NOT NULL,
  page_id  STRING,
  payload  STRING
)
PARTITION BY DATE(event_ts)
OPTIONS (
  partition_expiration_days = 730,
  require_partition_filter  = TRUE
);

-- Inserts spanning three days
INSERT INTO analytics.events (event_ts, user_id, page_id, payload)
VALUES
  (TIMESTAMP '2026-06-10 12:00:00 UTC', 'u1', '/home',   '{}'),
  (TIMESTAMP '2026-06-10 12:01:00 UTC', 'u2', '/pricing','{}'),
  (TIMESTAMP '2026-06-11 09:00:00 UTC', 'u1', '/home',   '{}'),
  (TIMESTAMP '2026-06-12 18:30:00 UTC', 'u3', '/checkout','{}');

-- Query — pruned to one day
SELECT COUNT(*) AS c
FROM analytics.events
WHERE DATE(event_ts) = '2026-06-10';

Step-by-step explanation.

1. PARTITION BY DATE(event_ts) tells BigQuery to bucket every row by DATE(event_ts) at write time. Internally, BigQuery still stores event_ts as a TIMESTAMP, but the storage layer maintains separate physical partitions keyed by the DATE.
2. partition_expiration_days = 730 auto-deletes partitions older than 2 years. This is your retention policy expressed as table options — no janitor job needed.
3. require_partition_filter = TRUE is the safety net: every query against this table must include a WHERE on DATE(event_ts) or an equivalent expression that the planner can resolve to a specific partition.
4. The INSERT lands each row in its appropriate daily partition. Three days of inserts → three physical partitions opened.
5. The SELECT query filters DATE(event_ts) = '2026-06-10'. BigQuery's planner sees the constant filter, resolves it to a single partition, and only reads bytes from that partition. The dry-run estimate will be bytes of the 2026-06-10 partition only.

Output.

Partition (DATE)	Rows	Approx. bytes
2026-06-10	2	0.5 KB
2026-06-11	1	0.25 KB
2026-06-12	1	0.25 KB

Rule of thumb. DAY partitioning on the event timestamp is the default for any append-mostly table. Add partition_expiration_days for retention and require_partition_filter = TRUE as soon as the table exceeds 100 GB.

Worked example — integer-range partitioning with RANGE_BUCKET

Detailed explanation. When the dominant filter is not time but a dense integer key — for example a customer_id workload where 80% of queries say WHERE customer_id IN (…) against a known small list — integer-range partitioning beats time partitioning. The trick is picking the bucket width so the partition count stays under 10,000 while keeping each bucket dense enough to prune effectively.

Question. Design an integer-range partition for an account_balances table with customer_id between 1 and 5,000,000. Pick a bucket width that keeps the partition count under 10,000 and shows good pruning on a WHERE customer_id BETWEEN 200000 AND 200100 query.

Input.

Column	Type	Notes
customer_id	INT64	dense IDs 1 .. 5,000,000
balance_at	TIMESTAMP	snapshot time
amount	NUMERIC(18, 2)	balance

Code.

-- DDL — 1,000 buckets of 5,000 customer_ids each
CREATE TABLE analytics.account_balances (
  customer_id INT64 NOT NULL,
  balance_at  TIMESTAMP NOT NULL,
  amount      NUMERIC(18, 2)
)
PARTITION BY RANGE_BUCKET(customer_id, GENERATE_ARRAY(0, 5000000, 5000))
CLUSTER BY balance_at
OPTIONS (
  require_partition_filter = TRUE
);

-- Query — filter falls inside the bucket [200000, 205000)
SELECT customer_id, MAX(balance_at) AS latest, SUM(amount) AS total
FROM analytics.account_balances
WHERE customer_id BETWEEN 200000 AND 200100
GROUP BY customer_id;

Step-by-step explanation.

1. GENERATE_ARRAY(0, 5000000, 5000) creates the bucket boundaries: [0, 5000), [5000, 10000), …, [4995000, 5000000). That's 5,000,000 / 5,000 = 1,000 partitions — comfortably under the 10,000 limit.
2. PARTITION BY RANGE_BUCKET(customer_id, …) assigns each row to one bucket by integer-range lookup. A row with customer_id = 200050 lands in the [200000, 205000) partition.
3. CLUSTER BY balance_at adds a secondary sort within each partition. Queries that filter on both customer_id and balance_at (most of them) prune at both levels.
4. The query filter customer_id BETWEEN 200000 AND 200100 resolves to one partition at plan time — the [200000, 205000) bucket. The other 999 partitions are pruned away.
5. The require_partition_filter = TRUE constraint enforces that callers must always supply a customer_id range filter, otherwise the query is rejected. This prevents an analyst from writing SELECT MAX(balance_at) FROM account_balances and scanning the whole table.

Output.

customer_id	latest	total
200001	2026-06-10 23:45:01 UTC	4,201.50
200050	2026-06-10 22:11:33 UTC	12,008.20
200100	2026-06-10 19:58:14 UTC	781.00

Rule of thumb. Integer-range partitioning is the right call when (a) IDs are dense, (b) the dominant filter is by ID range or IN-list, (c) you can pick a bucket width that keeps partition count under 10,000. If any of those fail, fall back to time partitioning + cluster on the ID.

Worked example — ingestion-time partitioning with _PARTITIONTIME

Detailed explanation. Ingestion-time partitioning is the right answer when the table is a streaming log and the load time is the relevant time. The _PARTITIONTIME pseudo-column is populated automatically — you do not need a column in the schema for the time value. The trade-off: backfills inherit the load time, not the event time, so out-of-order backfills land in the wrong partition.

Question. Build an audit_logs table partitioned by ingestion time using _PARTITIONTIME. Stream rows from a Pub/Sub topic into it and query the last 24 hours.

Input.

Source	Sink table	Partition column
Pub/Sub audit-events topic	analytics.audit_logs	_PARTITIONTIME (implicit)

Code.

-- DDL — no explicit time column; partition on ingestion
CREATE TABLE analytics.audit_logs (
  actor STRING,
  action STRING,
  resource STRING,
  payload STRING
)
PARTITION BY _PARTITIONDATE
OPTIONS (
  partition_expiration_days = 90,
  require_partition_filter  = TRUE
);

-- Streaming insert (via the streaming API; equivalent here)
INSERT INTO analytics.audit_logs (actor, action, resource, payload)
VALUES
  ('u1', 'login',  '/auth',       '{}'),
  ('u2', 'update', '/users/u2',   '{}'),
  ('u1', 'logout', '/auth',       '{}');

-- Query — last 24h via _PARTITIONTIME
SELECT actor, COUNT(*) AS events
FROM analytics.audit_logs
WHERE _PARTITIONTIME >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
GROUP BY actor
ORDER BY events DESC;

Step-by-step explanation.

1. PARTITION BY _PARTITIONDATE (or _PARTITIONTIME) tells BigQuery to bucket every row by the ingestion time, not by a column value. The pseudo-column is exposed automatically and does not appear in the schema.
2. The streaming insert lands every row in the partition for the current UTC date. Three inserts arriving today all land in today's partition.
3. partition_expiration_days = 90 auto-deletes partitions older than 90 days — a typical log retention policy.
4. The query filter _PARTITIONTIME >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR) resolves at plan time to at most two partitions (today and yesterday, depending on the wall-clock hour). The planner prunes everything else.
5. Gotcha. If you ever need to backfill last week's audit events into this table, you have a problem: those rows will land in today's partition, not last week's. The fix is to add an explicit event_ts column at design time and partition by that instead.

Output.

actor	events
u1	2
u2	1

Rule of thumb. Ingestion-time partitioning wins when (a) the table is append-only streaming, (b) backfills are rare or strictly chronological, (c) you do not want to put the event time in the schema. For anything more flexible, prefer column-based DATE/TIMESTAMP partitioning.

Worked example — 5 TB events table partitioned by event_date

Detailed explanation. Apply the partition-flavour decision to a real-world 5 TB events table and measure the resulting cost. The dominant filter is event_date = … (or a small range), so DAY partitioning on a DATE column is the call. Show the DDL, the daily insert pattern, and a typical analytic query.

Question. Convert a 5 TB unpartitioned events_legacy table to a partitioned events_v2 table with DAY partitioning, daily retention of 730 days, and require_partition_filter enabled. Measure the bytes saved on the canonical 1-day query.

Input.

Source	Size	Partition (today)	Cluster (today)
analytics.events_legacy	5 TB	none	none

Code.

-- Build the partitioned target
CREATE TABLE analytics.events_v2 (
  event_date DATE     NOT NULL,
  event_ts   TIMESTAMP NOT NULL,
  user_id    STRING,
  page_id    STRING,
  country_code STRING,
  payload    STRING
)
PARTITION BY event_date
CLUSTER BY country_code, user_id
OPTIONS (
  partition_expiration_days = 730,
  require_partition_filter  = TRUE
);

-- Backfill (one-shot)
INSERT INTO analytics.events_v2
SELECT
  DATE(event_ts) AS event_date,
  event_ts,
  user_id,
  page_id,
  country_code,
  payload
FROM analytics.events_legacy;

-- Atomic swap (after verification)
ALTER TABLE analytics.events_legacy RENAME TO events_legacy_retired;
ALTER TABLE analytics.events_v2     RENAME TO events;

-- Canonical query on the new table
SELECT COUNT(*) FROM analytics.events
WHERE event_date = '2026-06-10';

Step-by-step explanation.

1. event_date is materialised as a DATE NOT NULL column derived from event_ts. This is the "always partition on a real column, not on an expression" pattern — it makes pruning trivial and the schema self-documenting.
2. The INSERT is a one-shot backfill. BigQuery streams the source table into the target with the new partition column. The job takes ~30 minutes for 5 TB on a typical reservation.
3. The atomic rename swap is the safe migration shape — old table renamed away (kept as a fallback for a few days), new table renamed into the production name.
4. The canonical query now hits exactly the partition for event_date = '2026-06-10', scanning ~5 GB instead of ~5 TB.
5. The cluster on country_code, user_id lets the secondary 5%-of-queries family (per-country / per-user breakdowns) also benefit, by pruning blocks within each daily partition.

Output.

Variant	Bytes scanned (canonical query)	On-demand cost
events_legacy (unpartitioned)	~5 TB	~$31.25
events (partitioned + clustered)	~5 GB	~$0.03

Rule of thumb. Migrating a large unpartitioned table is a one-shot CTAS + atomic rename, never an ALTER TABLE. Always keep the old table around for at least 7 days as a fallback before dropping it.

Senior interview question on partition flavour choice

A senior interviewer might frame this as: "You have three tables — a 5 TB events log, a 200 GB customer balances snapshot keyed by customer_id, and an audit log streaming from Pub/Sub. Pick the partition flavour for each and justify."

Solution Using a flavour-per-workload mapping

Flavour decision — three tables, three flavours
================================================

Events log (5 TB)
  - Dominant filter: event_date / event_ts range
  - Pick: PARTITION BY DATE(event_ts), DAY granularity
  - Cluster: country_code, user_id
  - require_partition_filter: TRUE
  - partition_expiration_days: 730

Customer balances (200 GB)
  - Dominant filter: customer_id IN (…) / BETWEEN x AND y
  - Pick: PARTITION BY RANGE_BUCKET(customer_id, GENERATE_ARRAY(0, 5M, 5K))
  - Cluster: balance_at
  - require_partition_filter: TRUE
  - partition_expiration_days: not set (snapshot table, no retention)

Audit logs (streaming)
  - Dominant filter: last N hours / days
  - Pick: PARTITION BY _PARTITIONDATE
  - Cluster: actor, action
  - require_partition_filter: TRUE
  - partition_expiration_days: 90

Step-by-step trace.

Table	Filter shape	Flavour	Cluster
events	time range	DATE column	country_code, user_id
account_balances	integer range	RANGE_BUCKET on customer_id	balance_at
audit_logs	streaming time	_PARTITIONDATE	actor, action

Output:

Table	Partition column	Granularity	require_partition_filter
events	event_date	DAY	TRUE
account_balances	customer_id bucket	5,000-wide buckets	TRUE
audit_logs	_PARTITIONDATE	DAY	TRUE

Why this works — concept by concept:

• Filter shape → flavour mapping — DATE / TIMESTAMP filter → time partition; integer ID range filter → integer-range partition; streaming with load-time filter → ingestion-time. Memorise this table and the flavour decision becomes mechanical.
• DAY granularity default — DAY is the safe default for any append-mostly table. HOUR only when the daily row count exceeds ~1B and intra-day filters are common.
• Bucket width math — pick (ID range) / (bucket width) so the result is comfortably below 10,000 partitions. 5,000 IDs per bucket for a 5M-ID range = 1,000 partitions.
• Ingestion-time pseudo-column — _PARTITIONTIME requires no schema change but breaks under out-of-order backfills. Trade flexibility for simplicity only when backfills are rare.
• Cost — partition selection is O(log P) at plan time where P is the partition count; integer-range and time both use a sorted partition index. Choosing the right flavour is what makes that index actually useful.

SQL
Topic — SQL
SQL partition design problems

Practice →

ETL Topic — ETL ETL warehouse design drills

Practice →

---

3. Clustering — within-partition organisation

bigquery cluster by sorts blocks inside each partition by up to four columns ordered by selectivity — and free background re-clustering means you do not pay slot-seconds to maintain it

The mental model in one line: partitioning prunes which partitions the planner opens; clustering prunes which storage blocks inside each partition the planner reads, by sorting rows on up to 4 cluster columns and storing block-level metadata that lets the planner skip irrelevant blocks. Partition pruning happens at plan time (planner reads metadata); cluster pruning happens at execution time (planner reads block index and skips blocks).

The four-column cluster key.

• Up to 4 columns, ordered by selectivity from most selective to least selective. The cluster key is a composite — BigQuery sorts rows lexicographically by (col1, col2, col3, col4). A filter that uses only col1 prunes maximally; a filter that uses only col3 prunes less (the planner cannot skip blocks where col1, col2 differ but col3 happens to match).
• Order matters in queries too. A query that filters on col2 but not col1 still gets some block pruning, but the planner has to scan more blocks. The cluster key is most effective when queries hit the prefix of the key.
• Cluster columns can be STRING, INT64, BOOL, DATE, TIMESTAMP, DATETIME, GEOGRAPHY, NUMERIC, BIGNUMERIC. No FLOAT64 (clustering on floats is not well-defined) and no STRUCT / ARRAY types.
• Cluster column NDV matters. A column with NDV (number of distinct values) of 5 (e.g. country_continent) only gives 5-way pruning. A column with NDV of 1M (e.g. user_id) gives much finer pruning. Always put the higher-NDV columns first.

Selectivity = NDV / row count.

• country_code on a global events table — NDV ~250, row count ~5B → selectivity 5e-8 → very selective.
• user_id on the same table — NDV ~50M, row count 5B → selectivity 1e-2 → also selective.
• device_type (mobile / desktop / tablet) — NDV 3, row count 5B → selectivity 6e-10 → not useful as a cluster column (too few distinct values to meaningfully partition blocks).
• Rule: drop any cluster column with NDV < 100 — it does not earn its slot in the 4-column budget.

Block-level metadata — how clustering actually prunes.

• BigQuery storage is broken into "blocks" of ~10–100 MB. Each block carries metadata: the min/max value for every column.
• When a query filters on a cluster column, the planner reads the block metadata for the relevant partitions and skips any block whose [min, max] range does not include the filter value.
• Without clustering, the [min, max] of every block spans the full domain of the column → no blocks can be skipped.
• With clustering, blocks are sorted on the cluster columns, so each block's [min, max] is tight → most blocks can be skipped on a selective filter.

Free background re-clustering.

• After every load or streaming insert, the new rows arrive in their own block, not sorted into the existing cluster order.
• BigQuery runs background re-clustering automatically — no extra DDL, no slot-second charges. The frequency depends on the volume of new writes.
• LAST_REFRESH_TIME in INFORMATION_SCHEMA.PARTITIONS tells you when each partition was last fully re-clustered. If it's been weeks since a hot partition was re-clustered, you may have a fragmented partition; the fix is usually CREATE TABLE … AS SELECT * FROM same_table to force a rewrite.

ALTER TABLE … SET OPTIONS for cluster changes.

• You can change the cluster columns without rewriting the table: ALTER TABLE events SET OPTIONS (clustered_by = ['country_code','user_id']).
• New writes follow the new cluster order. Existing data eventually re-clusters in the background.
• If you need the change to apply immediately, do a CTAS to rewrite.

Common interview probes on clustering.

• "Why is cluster column order important?" — because the storage sort is composite; prefix queries prune best.
• "Up to how many cluster columns?" — 4.
• "Does BigQuery re-cluster automatically?" — yes, for free, in the background.
• "What's the difference between partitioning and clustering?" — partitioning prunes partitions at plan time; clustering prunes blocks within partitions at execution time.

Worked example — single-column cluster on country_code

Detailed explanation. A daily-partitioned events table where 5% of queries filter on country_code. Clustering on country_code alone lets those minority queries prune to the blocks for the requested country within each partition, while leaving the majority date-only queries unaffected.

Question. Add a single-column cluster on country_code to a daily-partitioned events table. Show how a query filtering on (event_date, country_code) prunes both partition and blocks.

Input.

Column	Type	Cardinality (NDV)
event_date	DATE	~1,000 (3 years)
event_ts	TIMESTAMP	~5B (event-time)
user_id	STRING	~50M
country_code	STRING	~250
payload	STRING	high

Code.

CREATE TABLE analytics.events (
  event_date   DATE NOT NULL,
  event_ts     TIMESTAMP NOT NULL,
  user_id      STRING,
  country_code STRING,
  payload      STRING
)
PARTITION BY event_date
CLUSTER BY country_code
OPTIONS (
  require_partition_filter = TRUE
);

-- Query — prunes partition AND blocks
SELECT COUNT(*) AS c
FROM analytics.events
WHERE event_date = '2026-06-10'
  AND country_code = 'US';

Step-by-step explanation.

1. The partition step: event_date = '2026-06-10' resolves at plan time to a single partition. Everything else is dropped before scanning starts.
2. The cluster step: within the 2026-06-10 partition, blocks are sorted by country_code. The planner reads the block index, finds the blocks whose [min(country_code), max(country_code)] range includes 'US', and skips the rest.
3. With 250 countries and a sorted cluster, the planner reads roughly 1/250 of the partition — say ~20 MB instead of ~5 GB.
4. The whole query is end-to-end ~20 MB scanned for a COUNT(*) — cents per hundred queries.
5. What if the cluster were on user_id instead? The same query would not benefit — the WHERE country_code = 'US' filter cannot use a user_id-clustered block index. You would only see partition pruning, not block pruning.

Output.

Step	Bytes considered	Bytes scanned
Full table	5,000 GB	—
After partition prune	5 GB	—
After cluster prune	20 MB	20 MB

Rule of thumb. A single cluster column is enough when there is exactly one secondary filter axis. Add more columns only when the query log shows compound filters that genuinely repeat.

Worked example — multi-column cluster on (country_code, user_id)

Detailed explanation. When the query log shows both country_code filters and user_id filters (sometimes together, sometimes separately), a 2-column cluster on (country_code, user_id) prunes both axes. Order matters: country_code first because filters on country are more common than filters on user id alone in this hypothetical.

Question. Extend the events table to cluster on (country_code, user_id) and show how three different query shapes (country-only, country + user, user-only) prune differently.

Input.

Query shape	Frequency	Filters
Daily dashboard	60%	event_date = …
Country breakdown	25%	event_date = …, country_code = …
Country + user drill-down	12%	event_date = …, country_code = …, user_id = …
User-only lookup	3%	event_date = …, user_id = …

Code.

CREATE TABLE analytics.events_v3 (
  event_date   DATE NOT NULL,
  event_ts     TIMESTAMP NOT NULL,
  user_id      STRING,
  country_code STRING,
  payload      STRING
)
PARTITION BY event_date
CLUSTER BY country_code, user_id;

-- Q1 country + user (best case for this cluster)
SELECT COUNT(*) FROM analytics.events_v3
WHERE event_date = '2026-06-10'
  AND country_code = 'US'
  AND user_id = 'u-12345';

-- Q2 country only (good)
SELECT COUNT(*) FROM analytics.events_v3
WHERE event_date = '2026-06-10' AND country_code = 'US';

-- Q3 user only (mediocre — cluster prefix not hit)
SELECT COUNT(*) FROM analytics.events_v3
WHERE event_date = '2026-06-10' AND user_id = 'u-12345';

Step-by-step explanation.

1. Q1 filters on both cluster columns in prefix order — country_code first, user_id second. The planner prunes to blocks where country_code = 'US' AND user_id = 'u-12345'. Expected scan: ~1 MB.
2. Q2 filters on country_code only. The planner prunes to all blocks where country_code = 'US'. Expected scan: ~20 MB (the same as a single-column cluster would give for this filter).
3. Q3 filters on user_id only — but user_id is the second cluster column, so the storage sort does not guarantee that u-12345 rows are contiguous within the partition. The planner can still skip some blocks where the [min, max] of user_id doesn't include u-12345, but the pruning is weaker. Expected scan: ~500 MB.
4. The takeaway: cluster column order matters. (country_code, user_id) is optimal for the 25% + 12% = 37% of queries that use country; suboptimal for the 3% user-only family.
5. If user-only lookups were the dominant pattern, swap the order to (user_id, country_code) — but the query log says they are not, so keep country first.

Output.

Query	Bytes scanned	Pruning
Q1 country + user	~1 MB	partition + 2-col cluster
Q2 country only	~20 MB	partition + 1st cluster col
Q3 user only	~500 MB	partition + weak 2nd cluster col

Rule of thumb. Match the cluster column order to the most common prefix of compound filters. If query patterns shift, you can re-order with ALTER TABLE … SET OPTIONS — the background re-clustering does the work.

Worked example — clustering on a high-NDV column (user_id)

Detailed explanation. Sometimes the dominant secondary filter is a high-NDV column like user_id. Clustering on it gives extreme block pruning — a query for a single user reads roughly 1/NDV of the partition. The trade-off: writes are slightly slower because BigQuery has to sort by a high-cardinality column.

Question. Build a user_activity table clustered on user_id and show the block-pruning math for a single-user query.

Input.

Stat	Value
Total rows	5,000,000,000
Partitions (DAY)	1,000
Rows per partition	5,000,000
Blocks per partition	~50 (assuming 100 MB blocks)
NDV(user_id)	50,000,000
Rows per user per partition	~0.1 (most users not active every day)

Code.

CREATE TABLE analytics.user_activity (
  event_date DATE NOT NULL,
  user_id    STRING NOT NULL,
  action     STRING,
  payload    STRING
)
PARTITION BY event_date
CLUSTER BY user_id
OPTIONS (
  require_partition_filter = TRUE
);

-- Query — single user, single day
SELECT action, COUNT(*) AS c
FROM analytics.user_activity
WHERE event_date = '2026-06-10'
  AND user_id = 'u-12345'
GROUP BY action;

Step-by-step explanation.

1. Partition pruning resolves to the 2026-06-10 partition — ~5M rows / ~5 GB.
2. Within that partition, blocks are sorted by user_id. With 50 blocks (each ~100 MB / 100K rows) and NDV(user_id) = 50M, each block holds a tight range of user IDs.
3. A single-user filter user_id = 'u-12345' matches at most one or two blocks (the boundary case where 'u-12345' falls on a block boundary). The planner reads those 1–2 blocks, ~100–200 MB.
4. But: if no rows for u-12345 exist on 2026-06-10, the query still does a block scan to confirm. Block min/max metadata cannot prove non-existence — only narrow the range.
5. The dry-run estimate will be ~100 MB. Without clustering, the same query scans the whole 5 GB partition — a 50× reduction.

Output.

Step	Bytes scanned
Without cluster	5 GB
With user_id cluster	~100 MB
Reduction	~50×

Rule of thumb. A single high-NDV cluster column on the most common drill-down filter pays for itself within days of a small dashboarding workload. Just keep an eye on LAST_REFRESH_TIME to make sure the background re-clustering is keeping up.

Senior interview question on cluster column choice

A senior interviewer often probes: "You have a 5 TB events table partitioned by event_date. The query log shows three secondary filters: country_code (in 25% of queries), user_id (in 12%), and device_type (in 8%). Pick the cluster columns and justify the order."

Solution Using the NDV-and-frequency ranking

Cluster column choice — NDV × frequency framework
==================================================

Step 1 — Tally secondary filter columns from the query log
  Query log (last 30 days):
    country_code in WHERE → 25% of queries
    user_id      in WHERE → 12% of queries
    device_type  in WHERE → 8% of queries

Step 2 — Compute NDV for each candidate
  country_code → NDV ~250        (selective enough)
  user_id      → NDV ~50,000,000 (very selective)
  device_type  → NDV 3           (NOT selective enough — drop)

Step 3 — Order by query-frequency × selectivity
  country_code: 25% × log(250)        = 17.4
  user_id:      12% × log(50,000,000) = 11.5
  device_type:  8%  × log(3)          = 0.8 (drop)

Step 4 — Pick top 2-4 columns (4 max)
  CLUSTER BY country_code, user_id

Step-by-step trace.

Step	Decision	Reasoning
1	tally filters	30-day query log
2	compute NDV	identify low-NDV columns to drop
3	rank	frequency × log(NDV) — country_code first
4	finalise	CLUSTER BY country_code, user_id

Output:

Cluster column	Position	Reason
country_code	1st	most frequent secondary filter, moderately selective
user_id	2nd	very selective, second-most-frequent
device_type	dropped	NDV 3 — no pruning benefit

Why this works — concept by concept:

• NDV × frequency ranking — the cluster key budget is 4 columns. Each column "earns" its slot via frequency × selectivity. Columns with NDV < 100 rarely earn the slot.
• Cluster prefix dominance — most queries hit the prefix of the cluster key. Putting the most-frequent column first means the most common ad-hoc filter prunes maximally.
• Drop low-NDV columns — device_type with NDV 3 contributes essentially nothing — the [min, max] range of each block will span all 3 values almost always, so no blocks are pruned.
• Re-order is cheap — if a year from now the query mix shifts and user_id becomes the dominant filter, ALTER TABLE … SET OPTIONS (clustered_by = …) updates the cluster key without a rewrite. Background re-clustering does the work.
• Cost — cluster pruning is O(log B) per partition where B is the number of blocks; usually B is 10–100, so the planner is doing ~7 comparisons to identify the relevant blocks. Compare to scanning every block: a 100× speed-up.

Optimization
Topic — optimization
Cluster column ordering problems

Practice →

SQL Topic — SQL SQL filter and prune drills

Practice →

---

4. require_partition_filter + cost guardrails

require_partition_filter = TRUE makes accidental full-table scans physically impossible — and pairing it with cost-attribution dashboards turns "who burned the budget" from forensics into self-service

The mental model in one line: require_partition_filter = TRUE is a table option that forces every query to include a partition filter the planner can resolve at plan time, otherwise the query is rejected with Cannot query over table without a filter over the partition column. Combined with INFORMATION_SCHEMA.JOBS_BY_PROJECT cost attribution and a default partition expiration, this is the production-safety triad that prevents the 90% of BigQuery cost surprises.

What require_partition_filter = TRUE does.

• Plan-time enforcement. When the planner cannot resolve a partition filter to a finite set of partitions, the query is rejected before any bytes are scanned. No accidental full scans, ever.
• The filter must be on the partition column. A filter on a non-partition column does not satisfy the constraint, even if the cluster column happens to be very selective.
• The filter must resolve at plan time. event_date = (SELECT MAX(event_date) FROM x) does not satisfy the constraint — the planner cannot resolve the subquery at plan time and falls back to scanning all partitions. Use a literal or a query parameter instead.
• Set via OPTIONS or ALTER TABLE. New tables: OPTIONS (require_partition_filter = TRUE). Existing tables: ALTER TABLE x SET OPTIONS (require_partition_filter = TRUE). The change applies to all future queries; existing query history is not affected.

Why the safety net matters.

• Junior analyst risk. A new analyst writes SELECT * FROM events LIMIT 10 to "explore the schema." Without the guardrail, that scans the whole table — potentially terabytes — even with the LIMIT 10. BigQuery's LIMIT is applied after the scan completes.
• BI tool risk. Tableau, Looker, and Metabase often produce queries that compute aggregations across the whole dataset (e.g. cardinality stats on a column). Without the guardrail, these run as full scans every dashboard refresh.
• Bot risk. Cron-driven dashboards, refresh jobs, and CI integration tests can run hundreds of queries a day. One unfiltered query × 24 hourly refreshes × 5 dashboards = a four-figure cost surprise per month.
• The guardrail eliminates all three risks with one table option. Setting it should be a checklist item for every table over 100 GB.

INFORMATION_SCHEMA cost attribution.

• region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT exposes every query's user, project, total bytes processed, total bytes billed, and creation time. Roll up by user to attribute cost. Roll up by query_hash to find the queries that dominate.
• INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT gives slot-second usage over time, useful for capacity reservation tuning.
• INFORMATION_SCHEMA.PARTITIONS exposes per-partition row counts, byte sizes, and LAST_REFRESH_TIME for cluster maintenance monitoring.

The default partition expiration partner.

• partition_expiration_days = N auto-deletes partitions older than N days. Combine with require_partition_filter to bound both query cost (via partition pruning) and storage cost (via auto-deletion).
• For event tables, 365–730 days is typical. For audit logs, 30–90 days. For snapshot tables (current-state-only), no expiration; the table is small.

The BI-tool gotcha — Tableau / Looker extracts.

• BI tools sometimes generate queries that look like SELECT * FROM events WHERE event_date BETWEEN @start AND @end. If @start and @end are bound to UI date pickers and a user clicks "all time," the query becomes effectively unfiltered.
• The fix: configure the BI tool to enforce a default date range at the data-source level, and let the BigQuery guardrail catch any leaks.

Common interview probes on guardrails.

• "How do you prevent a junior analyst from running SELECT * FROM events against a 10 TB table?" — require_partition_filter = TRUE.
• "Why doesn't LIMIT 10 prevent full scans?" — because BigQuery applies LIMIT after the scan.
• "What query satisfies the constraint vs what doesn't?" — literal-resolved filters yes, subquery-resolved filters no.
• "How do you attribute cost back to a user?" — INFORMATION_SCHEMA.JOBS_BY_PROJECT rolled up by user_email.

Worked example — enabling require_partition_filter on an existing table

Detailed explanation. A table is already in production and not protected. Turn on the guardrail with ALTER TABLE, show a query that fails after the change, and the fix.

Question. Enable require_partition_filter on a partitioned events table. Run an unfiltered query before and after the change to demonstrate the enforcement.

Input.

Step	Action
1	Run unfiltered query (succeeds expensively)
2	ALTER TABLE … SET OPTIONS (require_partition_filter = TRUE)
3	Re-run unfiltered query (fails)
4	Add WHERE event_date = … and re-run (succeeds cheaply)

Code.

-- Step 1 — pre-change unfiltered query (scans 5 TB)
SELECT COUNT(*) FROM analytics.events;

-- Step 2 — enable the guardrail
ALTER TABLE analytics.events
SET OPTIONS (require_partition_filter = TRUE);

-- Step 3 — post-change unfiltered query (rejected)
SELECT COUNT(*) FROM analytics.events;
-- Error: Cannot query over table 'analytics.events' without a filter
-- over column(s) 'event_date' that can be used for partition elimination

-- Step 4 — corrected query (5 GB)
SELECT COUNT(*) FROM analytics.events
WHERE event_date = '2026-06-10';

Step-by-step explanation.

1. Before the change, the unfiltered SELECT COUNT(*) runs successfully but scans the whole 5 TB table. On-demand cost: ~$31. This is the exact pattern that drains budgets quietly.
2. ALTER TABLE … SET OPTIONS (require_partition_filter = TRUE) flips the table option. The change is metadata-only and applies immediately — no rewrite, no downtime, no slot-seconds.
3. Re-running the same query now fails at plan time. BigQuery returns Cannot query over table without a filter over the partition column. Zero bytes scanned, zero cost.
4. The corrected query adds WHERE event_date = '2026-06-10'. The planner can resolve the literal filter to a single partition, the constraint is satisfied, and the query runs against ~5 GB.
5. The whole intervention took two seconds of DDL and prevents an unknown number of future accidental full scans.

Output.

Step	Result	Bytes scanned
1 — before guardrail	success	5 TB
2 — alter table	metadata change	0
3 — after guardrail (unfiltered)	rejected	0
4 — after guardrail (filtered)	success	5 GB

Rule of thumb. Enable require_partition_filter on every production table larger than 100 GB. The cost of the alter is zero; the cost of not doing it is one full-scan accident.

Worked example — cost attribution by user via INFORMATION_SCHEMA

Detailed explanation. A FinOps task: "Which users are scanning the most bytes this month?" The answer is a single INFORMATION_SCHEMA.JOBS_BY_PROJECT query rolled up by user_email. Show the query and how to act on the result.

Question. Write the INFORMATION_SCHEMA query that ranks users by total bytes scanned in the last 30 days. Show how to convert bytes to dollars at the on-demand list price.

Input.

Column (in JOBS_BY_PROJECT)	Used for
user_email	grouping key
total_bytes_processed	sum to total
creation_time	30-day window
job_type	filter to QUERY only
state	filter to DONE

Code.

SELECT
  user_email,
  COUNT(*)                                                  AS jobs,
  SUM(total_bytes_processed)                                AS bytes_scanned,
  ROUND(SUM(total_bytes_processed) / POW(1024, 4), 2)        AS tb_scanned,
  ROUND(SUM(total_bytes_processed) / POW(1024, 4) * 6.25, 2) AS usd_cost_estimate
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type      = 'QUERY'
  AND state         = 'DONE'
  AND statement_type != 'SCRIPT'                            -- exclude wrappers
GROUP BY user_email
ORDER BY bytes_scanned DESC
LIMIT 20;

Step-by-step explanation.

1. region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT is the per-project view of every BigQuery job. Replace region-us with your region (e.g. region-eu).
2. Filter to job_type = 'QUERY' and state = 'DONE' to ignore load/copy/extract jobs and running queries. statement_type != 'SCRIPT' excludes the wrapper rows for multi-statement scripts.
3. Group by user_email and sum total_bytes_processed. This is the actual scan volume, before any flat-rate / reservation accounting.
4. Convert bytes to TB by dividing by 1024^4. Multiply by $6.25 (2026 on-demand list price; check your region) for a dollar estimate.
5. Sort descending — the top entries are your high-cost users. Drill into the query history for each to find the specific query pattern.

Output.

user_email	jobs	bytes_scanned	tb_scanned	usd_cost_estimate
[e-mail]	1,213	51,539,607,552,000	46.88	293.00
[e-mail]	412	8,796,093,022,208	8.00	50.00
[e-mail]	21,008	4,398,046,511,104	4.00	25.00

Rule of thumb. Run this query weekly. The top 3 users almost always account for 80%+ of scan volume. Tune their queries (add partition filters, materialise heavy aggregates) and the bill drops accordingly.

Worked example — catching an unfiltered scan via JOB_RESULT inspection

Detailed explanation. When an unexpected $200 query shows up in the bill, the INFORMATION_SCHEMA.JOBS_BY_PROJECT.query column gives you the full SQL text. Combined with total_bytes_billed, you can find the offending pattern in minutes.

Question. Find the top 5 queries by bytes billed in the last 7 days that did not include a partition filter on events.event_date. Show how to detect the absence of the filter via SQL pattern matching.

Input.

Column	Used for
query	SQL text
total_bytes_billed	cost driver
referenced_tables	tables touched by the job
creation_time	window

Code.

SELECT
  job_id,
  user_email,
  ROUND(total_bytes_billed / POW(1024, 3), 2) AS gb_billed,
  query
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND job_type      = 'QUERY'
  AND state         = 'DONE'
  AND EXISTS (
    SELECT 1 FROM UNNEST(referenced_tables) AS rt
    WHERE rt.table_id = 'events'
  )
  AND NOT REGEXP_CONTAINS(LOWER(query), r'event_date\s*(=|in|between|>=|<=)')
ORDER BY total_bytes_billed DESC
LIMIT 5;

Step-by-step explanation.

1. Filter the job log to queries that reference the events table — EXISTS on the unnested referenced_tables array.
2. Filter out queries that do contain event_date followed by a comparison or set operator. The regex is intentionally loose — false negatives (missed filters) are OK as long as false positives (incorrectly flagging a filtered query) are rare.
3. Sort by total_bytes_billed to surface the most expensive offenders first.
4. The top result is almost always a forgotten SELECT * FROM events or a WHERE other_column = … query that the analyst thought would be cheap because of the cluster.
5. Action: either add require_partition_filter (if not already enabled) or tag the user and rewrite the query.

Output.

job_id	user_email	gb_billed	query (truncated)
bq_job_001	[e-mail]	4,800	SELECT COUNT(*) FROM analytics.events WHERE country_code = 'US'
bq_job_002	[e-mail]	1,200	SELECT * FROM analytics.events LIMIT 1000
bq_job_003	[e-mail]	800	SELECT MAX(event_ts) FROM analytics.events

Rule of thumb. Wire a daily cron that runs this audit and posts the top offenders to a Slack channel. Naming and shaming (politely) is cheaper than building cost-attribution dashboards from scratch.

Senior interview question on guardrails

A senior interviewer might frame this as: "Your team woke up to a $5,000 BigQuery bill on a Saturday. The cause was one analyst running SELECT * FROM events LIMIT 100. Walk me through the immediate fix, the medium-term fix, and the long-term fix."

Solution Using the three-horizon remediation

Cost-guardrail incident — three horizons
========================================

Immediate (today, < 1 hour)
  - ALTER TABLE events SET OPTIONS (require_partition_filter = TRUE)
  - Restore from any backup if data was modified
  - Add cost alert at $500/day via Cloud Monitoring + Pub/Sub

Medium-term (this week)
  - Add INFORMATION_SCHEMA audit cron — daily Slack of top 5
  - Add CI dry-run check: any new query must scan < 1 GB
  - Enforce default partition expiration on all event tables

Long-term (this quarter)
  - Migrate every table > 100 GB to require_partition_filter = TRUE
  - Set up project-level reservation with slot caps per team
  - Train analysts on dry-run-first workflow
  - Document the partition + cluster strategy in the data catalog

Step-by-step trace.

Horizon	Action	Effort	Risk reduction
Immediate	enable guardrail	1 minute	full scan accidents → 0
Immediate	cost alert	30 minutes	future surprises detected within 24h
Medium-term	audit cron	2 hours	top 5 offenders surfaced daily
Medium-term	CI dry-run gate	1 day	no new high-cost queries land
Long-term	reservation slot caps	1 week	per-team cost capped

Output:

Horizon	Deliverable	Effective
Immediate	guardrail + alert	same day
Medium-term	audit + CI gate	within a week
Long-term	reservation + training	within a quarter

Why this works — concept by concept:

• Three horizons match the incident response curve — stop the bleeding (immediate), prevent the next one (medium-term), fix the systemic gap (long-term). Senior engineers think in horizons, not point fixes.
• require_partition_filter is the immediate fix — it converts "accidental scan" from a budget incident to a query rejection. Zero learning curve for users — the error message tells them what to add.
• CI dry-run gate — the same --dry_run flag from Section 1 powers the gate. Pre-merge check rejects any query whose estimate exceeds a per-table threshold.
• Reservation slot caps — at scale, on-demand pricing becomes flat-rate / autoscaler. Slot caps per team prevent one team from starving everyone else; reservation-level cost ceilings are absolute.
• Cost — the guardrail is O(1) at plan time (a metadata check); the audit is O(rows_in_INFORMATION_SCHEMA) per day, trivially cheap. The cost-saving leverage versus the cost-of-implementation ratio is among the highest of any BigQuery optimisation.

Optimization
Topic — optimization
Cost guardrail design problems

Practice →

SQL Topic — SQL SQL audit and attribution drills

Practice →

---

5. The 5-step tune-a-table runbook

A senior data engineer turns a slow BigQuery table into a fast, cheap one with a five-step query-log-driven runbook — and the steps are mechanical once the evidence is in hand

The mental model in one line: tuning a BigQuery table is a five-step query-log-driven workflow — (1) pull the last 30 days of queries, (2) pick the partition column from the dominant filter, (3) pick the cluster columns from the secondary filters, (4) enforce require_partition_filter, (5) monitor partition pruning and LAST_REFRESH_TIME to verify the change. Every step has a concrete deliverable, a concrete check, and a concrete next step.

Step 1 — Pull the last 30 days of queries against the table.

• INFORMATION_SCHEMA.JOBS_BY_PROJECT joined to UNNEST(referenced_tables) gives you every query touching the table.
• Tally WHERE-clause columns with a regex over the query text. Group by extracted column to count frequencies.
• For a 30-day window with hundreds of queries, the top 3 filter columns usually represent 80%+ of the workload.

Step 2 — Pick the partition column from the dominant filter.

• DATE / TIMESTAMP in 80%+ of queries → time partition. DAY granularity unless daily row count > 1B.
• Integer ID in 80%+ of queries → integer-range partition with bucket width tuned to keep partition count < 10,000.
• If no single column dominates → no partition, cluster only. Rare but happens for small lookup tables.

Step 3 — Pick the cluster columns from the secondary filters.

• Up to 4 columns, ordered by frequency × log(NDV) (Section 3 ranking).
• Drop any column with NDV < 100 — it doesn't earn its slot.
• Drop any column with frequency < 5% — it doesn't earn its slot.

Step 4 — Enforce require_partition_filter = TRUE.

• One-line ALTER TABLE … SET OPTIONS (require_partition_filter = TRUE) on any table > 100 GB.
• Communicate the change in a team channel — users will see error messages for a week or two as they update their queries.
• Pair with partition_expiration_days for retention.

Step 5 — Monitor partition pruning and LAST_REFRESH_TIME.

• INFORMATION_SCHEMA.JOBS_BY_PROJECT exposes total_bytes_processed per job — verify it dropped after the change.
• INFORMATION_SCHEMA.PARTITIONS exposes last_modified_time and (for clustered tables) the implicit re-cluster timestamp — verify partitions are being re-clustered.
• Run the dry-run wrapper from Section 1 against the top 10 queries weekly — alert if any regress above their post-tune baseline.

Practical tuning loop.

• A tuning session lasts ~half a day per table. The work is mostly waiting for the CTAS backfill if you need to change the partition column.
• The right cadence: run the runbook on every table over 1 TB once per quarter, or when a new query pattern emerges (new dashboard, new ML pipeline).
• Document the partition + cluster choice in the data catalog with a comment block explaining the query-log evidence behind it. Future engineers will thank you.

When the runbook says "no partition."

• Small tables (< 10 GB) often don't benefit from partitioning; the planner overhead is comparable to the scan.
• Cluster-only is the right answer for these. CLUSTER BY alone (no PARTITION BY) is supported.
• Watch the table grow — when it crosses 100 GB, run the runbook again.

Common interview probes on the runbook.

• "What's the first thing you do when asked to tune a BigQuery table?" — pull the query log.
• "Why query log and not the schema?" — partition + cluster choices are workload-dependent.
• "When would you skip partitioning?" — small table, no dominant filter column.
• "How do you know the change worked?" — dry-run before/after delta, INFORMATION_SCHEMA bytes-billed trend.

Worked example — Step 1: extract filter-column frequency

Detailed explanation. Step 1 of the runbook in code form. A single SQL query that returns a frequency table of filter columns across the last 30 days of queries against a target table.

Question. Write a SQL query that returns the top 10 WHERE-clause column references in queries against analytics.events over the last 30 days, with their frequency counts.

Input.

Source	Notes
INFORMATION_SCHEMA.JOBS_BY_PROJECT	last 30 days
UNNEST(referenced_tables)	filter to events only
query text	regex-extracted column references

Code.

WITH q AS (
  SELECT LOWER(query) AS q
  FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
    AND job_type = 'QUERY'
    AND state    = 'DONE'
    AND EXISTS (
      SELECT 1 FROM UNNEST(referenced_tables) AS rt
      WHERE rt.table_id = 'events'
    )
),
candidate_cols AS (
  SELECT col FROM UNNEST([
    'event_date','event_ts','user_id','country_code','page_id','device_type','session_id'
  ]) AS col
)
SELECT
  c.col,
  COUNTIF(REGEXP_CONTAINS(q.q, FORMAT(r'%s\s*(=|in|between|>=|<=)', c.col))) AS hits
FROM q, candidate_cols c
GROUP BY c.col
ORDER BY hits DESC;

Step-by-step explanation.

1. The CTE q selects every query against events in the last 30 days. The EXISTS clause unnests referenced_tables and filters to the target table.
2. The candidate_cols CTE is a hard-coded list of columns that might appear in WHERE clauses. Add to it any column from the table schema you care about.
3. The outer SELECT cross-joins queries × candidates and counts how often each candidate column appears in a WHERE-like position (= … IN … BETWEEN … >= … <=).
4. The regex is loose enough to catch most filter shapes and strict enough to avoid counting column appearances in SELECT clauses or comments.
5. Sort descending — the top result is the partition column candidate; positions 2–4 are cluster column candidates.

Output.

col	hits
event_date	458
user_id	196
country_code	132
page_id	27
device_type	24
event_ts	12
session_id	8

Rule of thumb. Run this once per quarter per large table. The first time, you'll be surprised by which columns actually drive filtering — often the obvious schema documentation guess is wrong.

Worked example — Step 2 + 3: apply partition + cluster

Detailed explanation. Steps 2 and 3 in code form. Given the filter frequencies from Step 1, build the DDL for the partitioned and clustered target table.

Question. Apply the Step 1 evidence (event_date 458 / user_id 196 / country_code 132 / page_id 27 hits) to design a partitioned + clustered events_v4 table. Justify the cluster column order.

Input.

Column	Hits	NDV	Frequency	log(NDV)	f × log(NDV)
event_date	458	1,000	92%	6.9	6.3
user_id	196	50M	41%	17.7	7.3
country_code	132	250	27%	5.5	1.5
page_id	27	1M	5.5%	13.8	0.8

Code.

CREATE TABLE analytics.events_v4 (
  event_date   DATE NOT NULL,
  event_ts     TIMESTAMP NOT NULL,
  user_id      STRING,
  country_code STRING,
  page_id      STRING,
  payload      STRING
)
PARTITION BY event_date
CLUSTER BY user_id, country_code, page_id
OPTIONS (
  require_partition_filter  = TRUE,
  partition_expiration_days = 730
);

Step-by-step explanation.

1. event_date wins partition: 92% of queries filter on it, it's a DATE column, the workload is append-mostly. DAY granularity.
2. Cluster column ranking by frequency × log(NDV): user_id (7.3) > event_date-already-partitioned > country_code (1.5) > page_id (0.8) > device_type (low NDV, dropped).
3. Cluster on user_id, country_code, page_id — three columns out of the four-column budget. Leaving room for a fourth column to be added later if the workload shifts.
4. require_partition_filter = TRUE is mandatory at this table size (5 TB).
5. partition_expiration_days = 730 matches the retention policy.

Output.

Decision	Choice
Partition	DAY on event_date
Cluster	user_id, country_code, page_id
require_partition_filter	TRUE
partition_expiration_days	730

Rule of thumb. Skip cluster columns that don't earn their slot. Three good cluster columns beat four mediocre ones because every column adds storage-sort overhead during background re-clustering.

Worked example — Step 5: verify pruning via INFORMATION_SCHEMA

Detailed explanation. After the tune, prove the change worked. Compare bytes-scanned for the top queries against the table over the 7 days before the change vs the 7 days after.

Question. Write a SQL query that compares the median total_bytes_processed against events in the 7 days before and after a known tune date (2026-06-01). Show the reduction.

Input.

Cutover	Date	Notes
Pre-tune window	2026-05-25 → 2026-05-31	unpartitioned
Post-tune window	2026-06-02 → 2026-06-08	partitioned + clustered

Code.

WITH events_jobs AS (
  SELECT
    creation_time,
    total_bytes_processed,
    CASE
      WHEN creation_time BETWEEN '2026-05-25' AND '2026-05-31' THEN 'before'
      WHEN creation_time BETWEEN '2026-06-02' AND '2026-06-08' THEN 'after'
    END AS window
  FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  WHERE job_type = 'QUERY'
    AND state    = 'DONE'
    AND EXISTS (
      SELECT 1 FROM UNNEST(referenced_tables) AS rt
      WHERE rt.table_id = 'events'
    )
)
SELECT
  window,
  COUNT(*)                                                          AS jobs,
  APPROX_QUANTILES(total_bytes_processed, 2)[OFFSET(1)] / POW(1024, 3) AS median_gb_scanned,
  SUM(total_bytes_processed) / POW(1024, 4)                         AS total_tb_scanned
FROM events_jobs
WHERE window IS NOT NULL
GROUP BY window
ORDER BY window;

Step-by-step explanation.

1. The CTE events_jobs tags every job touching events with its window (before or after).
2. APPROX_QUANTILES(…, 2)[OFFSET(1)] returns the median (the value at offset 1 of 0, 1, 2). Use the median rather than the mean because dashboards run many tiny queries that skew the mean.
3. SUM(total_bytes_processed) / POW(1024, 4) computes total TB scanned in each window. This is the dollars-saved metric.
4. The expected output shows a 100×–1000× reduction in median bytes scanned. If you don't see at least 10×, something is wrong — the partition column or cluster columns are not aligned with the dominant filter.
5. Run this monthly to confirm the tune is still effective. If the workload shifts (a new dashboard with a different filter pattern lands), the reduction will erode and you'll need to re-tune.

Output.

window	jobs	median_gb_scanned	total_tb_scanned
before	1,247	5,000	6,235.0
after	1,331	5	9.8

Rule of thumb. Always measure the median, not the mean, when comparing query bytes. Outliers (one full scan that snuck through) distort the mean enough to hide a successful tune.

Senior interview question on tuning a slow table

A senior interviewer often opens with: "A teammate's BigQuery table costs $40 every time a dashboard refreshes. Walk me through the steps you would take to bring that cost down, what evidence you would gather, and how you would prove the fix worked."

Solution Using the 5-step runbook end to end

Tune-a-table runbook — applied to the $40 dashboard table
=========================================================

Step 1 — Query log
  - Pull 30 days of queries against the table
  - Tally WHERE-clause column frequencies
  - Top filter: event_date (92%), user_id (41%), country_code (27%)

Step 2 — Partition column
  - event_date wins (92% of queries)
  - DAY granularity (daily row count ~5M)
  - PARTITION BY event_date

Step 3 — Cluster columns
  - Rank: user_id (high NDV, 41%), country_code (med NDV, 27%), page_id (med, 5%)
  - CLUSTER BY user_id, country_code, page_id

Step 4 — Guardrail
  - require_partition_filter = TRUE
  - partition_expiration_days = 730
  - Communicate change in #data-eng channel

Step 5 — Monitor
  - Dry-run delta on top 10 queries
  - INFORMATION_SCHEMA median-bytes comparison
  - LAST_REFRESH_TIME check on hot partitions

Step-by-step trace.

Step	Deliverable	Time
1	filter-column tally	5 minutes
2	partition column chosen	5 minutes
3	cluster columns chosen	10 minutes
4	DDL + ALTER + comms	30 minutes
5	monitoring queries	30 minutes
—	CTAS backfill	30 minutes – 2 hours
—	rename swap	2 minutes
—	verification	1 week

Output:

Metric	Before	After	Reduction
Median bytes per query	5 TB	5 GB	1,000×
Dashboard refresh cost	$40	$0.04	1,000×
Wall-clock per query	30 s	1 s	30×
Unfiltered scans per day	12	0	100% (guardrail)

Why this works — concept by concept:

• Evidence-driven — every decision in the runbook comes from INFORMATION_SCHEMA.JOBS_BY_PROJECT data, not from architectural opinions. A senior engineer trusts the query log.
• Mechanical decisions — partition column = dominant filter; cluster columns = next 2–3 ranked by frequency × log(NDV). The decisions are not creative; they fall out of the evidence.
• Guardrail prevents regression — require_partition_filter makes it physically impossible for the next new query to scan unfiltered. The fix sticks.
• Monitoring closes the loop — without dry-run deltas and INFORMATION_SCHEMA comparisons, you don't know whether the tune worked. With them, the result is auditable.
• Cost — the runbook is O(1) per table per quarter. Half a day of senior-engineer time pays for itself in days at any non-trivial workload. The leverage ratio is among the highest in any data engineering task.

Optimization
Topic — optimization
Table tuning runbook problems

Practice →

ETL
Topic — ETL
ETL backfill and migration drills

Practice →

---

Cheat sheet — partition + cluster recipes

• DAY partition + 2 cluster columns template. CREATE TABLE t (event_date DATE NOT NULL, …) PARTITION BY event_date CLUSTER BY col_a, col_b OPTIONS (require_partition_filter = TRUE, partition_expiration_days = 730). The 80%-case template for any event / log / transaction table over 100 GB.
• Integer-range partition template. PARTITION BY RANGE_BUCKET(id_col, GENERATE_ARRAY(0, MAX_ID, BUCKET_WIDTH)) with BUCKET_WIDTH = MAX_ID / 1000 (target ~1,000 buckets). Use only when IDs are dense and filters are by ID range.
• Ingestion-time partition template. PARTITION BY _PARTITIONDATE for streaming-only tables where backfills never go to historical dates. Combine with require_partition_filter and a 30–90 day retention.
• require_partition_filter enforcement. ALTER TABLE t SET OPTIONS (require_partition_filter = TRUE) is a metadata change with zero downtime — flip it on day one for every production table over 100 GB.
• Pruning sanity check. bq query --dry_run --use_legacy_sql=false "<query>" | jq -r '.statistics.totalBytesProcessed' — should be approximately one-partition's bytes for a single-day filter; anything more means the filter is not pruning.
• Cluster column ranking. frequency × log(NDV). Drop columns with NDV < 100 or frequency < 5%. Order most-selective first. Up to 4 columns; usually 2–3 is enough.
• Cluster column change. ALTER TABLE t SET OPTIONS (clustered_by = ['new_col_a','new_col_b']) — new writes follow the new order, background re-clustering rewrites the rest. No CTAS needed unless you want the change to apply immediately.
• Cluster monitoring. SELECT table_name, partition_id, last_modified_time FROM region-us.INFORMATION_SCHEMA.PARTITIONS WHERE table_name = 't' AND last_modified_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) ORDER BY last_modified_time DESC — verify recent partitions are being touched by background re-clustering.
• Partition migration recipe. Build the new table with CREATE TABLE new AS SELECT … FROM old PARTITION BY new_col, verify dry-run delta on top queries, atomically RENAME old away and new in. Keep the old table for 7 days as a fallback. No ALTER TABLE magic — BigQuery does not let you change a partition column in place.
• Cost attribution query. SELECT user_email, SUM(total_bytes_processed) / POW(1024, 4) AS tb FROM region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY) GROUP BY user_email ORDER BY tb DESC. Run weekly, post top 5 to a Slack channel.
• Unfiltered-scan audit. Pair the INFORMATION_SCHEMA.JOBS_BY_PROJECT.query text with a regex that detects queries missing the partition column filter — auto-flag offenders before they snowball.
• DAY vs HOUR rule. DAY is the default. Switch to HOUR only when daily row count > 1B AND intra-day filter patterns are common. Watch the partition-count limit (10,000) — HOUR + 5 years = 43,800 partitions, well over the limit.
• _PARTITIONTIME IS NULL trick. Filters to rows still in the streaming buffer (not yet committed to a partition). Useful for very-recent-data dashboards but not a substitute for proper time-column partitioning.
• 5-step runbook (one-liner). Pull log → pick partition → pick cluster → enforce filter → monitor pruning. Repeat per large table per quarter.

Frequently asked questions

What's the difference between partitioning and clustering in BigQuery?

bigquery partitioning splits the table into separate physical storage segments keyed by the partition column (DATE / TIMESTAMP / INT64 / ingestion time). At plan time, the query planner reads the partition metadata, resolves the WHERE-clause filter to a finite set of partitions, and only opens those — every other partition is never read. bigquery clustering sorts rows within each partition by up to 4 cluster columns, then stores per-block min/max metadata. At execution time, the planner reads the block index and skips blocks whose [min, max] does not include the filter value. Partitioning is coarse pruning (per-partition); clustering is fine pruning (per-block within a partition). Used together, they compose: partition prunes to one day, cluster prunes to a handful of blocks within that day. The combined effect is the difference between scanning 5 TB and scanning 20 MB for the same query, which translates directly to cost — bigquery cost optimization is mostly the discipline of getting these two table options right.

How many cluster columns should I use in BigQuery?

bigquery cluster by supports up to 4 columns, but you almost never need more than 2 or 3. The reason: each additional cluster column adds storage-sort overhead during the (free) background re-clustering, and the marginal block-pruning benefit drops sharply for the 3rd and 4th column. The selection rule is frequency × log(NDV): rank candidate columns by how often they appear in WHERE clauses (from INFORMATION_SCHEMA.JOBS_BY_PROJECT) multiplied by the log of the number of distinct values. Drop any column with NDV < 100 (too coarse) or frequency < 5% (not worth a slot). For a typical events table, 2–3 well-chosen cluster columns deliver 95% of the pruning benefit of the maximum-4 configuration with a fraction of the maintenance cost. The order matters too: most-selective first, because most queries hit the prefix of the cluster key.

Does BigQuery re-cluster tables automatically?

Yes — and for free. BigQuery runs background re-clustering after every meaningful write (load, streaming insert, INSERT, MERGE). You do not pay slot-seconds for the reorganisation; it's bundled into the storage cost. The frequency depends on the volume of new writes — a small daily-load table may re-cluster every day, while a high-volume streaming table may re-cluster continuously. LAST_REFRESH_TIME in INFORMATION_SCHEMA.PARTITIONS exposes when each partition was last fully re-clustered. If you see partitions that have not been refreshed in weeks despite new writes, the partition is fragmented (often because the new writes are tiny relative to the existing partition) — the fix is usually a CREATE TABLE … AS SELECT * FROM same_table to force a rewrite, or to switch to a denser partition granularity (HOUR instead of DAY for high-volume streams). For 99% of workloads, automatic background re-clustering is the right answer and you should not worry about it.

Can I partition a BigQuery table on a non-date column?

Yes — bigquery partition by supports three flavours: time-unit (DATE / TIMESTAMP / DATETIME columns, with DAY / HOUR / MONTH / YEAR granularity), integer-range (PARTITION BY RANGE_BUCKET(int_col, GENERATE_ARRAY(start, end, step))), and ingestion-time (PARTITION BY _PARTITIONDATE or _PARTITIONTIME, using the load timestamp without a schema column). Integer-range is underused — it's the right answer when the dominant filter is customer_id IN (…) or customer_id BETWEEN x AND y against dense integer IDs. The bucket width tuning matters: aim for ~1,000 partitions to stay well under the 10,000 partition limit while keeping each bucket dense enough for effective pruning. Strings, floats, booleans, and complex types (STRUCT, ARRAY) cannot be partition columns — for those, use clustering instead. If the dominant filter is a string column like country_code, the right move is to partition by event_date (or by another date column) and cluster by country_code, not partition by it.

What is _PARTITIONTIME in BigQuery and when should I use it?

_PARTITIONTIME is a pseudo-column that BigQuery exposes automatically on ingestion-time-partitioned tables. It holds the UTC timestamp truncated to the partition granularity (DAY by default) — and it does not appear in the table schema; it's populated automatically by the streaming and load APIs. Use ingestion-time partitioning when (a) the table is streaming-only or append-only with strict chronological loads, (b) the dominant filter is "last N hours / days" using load time as the proxy for event time, and (c) backfills are rare or always chronological. The trade-off: an out-of-order backfill (loading June data in August) lands the rows in the August partition, which breaks event-time queries. For new tables, Google's official recommendation in 2026 is to prefer a column-based DATE / TIMESTAMP partition (with an explicit event_date DATE or event_ts TIMESTAMP column) — it's more predictable and survives backfills. _PARTITIONTIME is still useful for streaming log tables where the load time genuinely is the right time axis, and _PARTITIONTIME IS NULL filters target the streaming buffer for very-recent-data dashboards.

How do I monitor whether my queries hit the partition filter in BigQuery?

Two queries answer this. First, bq query --dry_run --use_legacy_sql=false "<your query>" | jq -r '.statistics.totalBytesProcessed' returns the bytes the query would scan. Compare that to one-partition's worth of bytes — for a single-day filter on a DAY-partitioned table, the dry-run estimate should be approximately the daily partition size (say, 5 GB on a 5 TB / 1,000-partition table). Anything dramatically larger means the filter is not pruning — either because the filter expression cannot be resolved at plan time (e.g. a subquery), or because the partition column is wrong, or because require_partition_filter is off and an unfiltered query slipped through. Second, the historical view: SELECT total_bytes_processed FROM region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT WHERE query LIKE '%your-table-name%' ORDER BY creation_time DESC LIMIT 100 — sort by bytes-processed and look for outliers. The combination of partition pruning bigquery dry-run checks (forward-looking, in CI) and INFORMATION_SCHEMA audits (backward-looking, in monitoring) catches both new and existing pruning failures before they show up in the bill.

Practice on PipeCode

• Drill the SQL practice library → for the partition-filter and aggregation patterns that interviewers probe on BigQuery questions.
• Rehearse on optimization problems → when the interviewer wants table-design and cost-tuning depth.
• Stack the warehouse axis with the ETL practice set → for the load-pattern and backfill scenarios that drive partition-flavour choice.
• Sharpen the rollup family with the aggregation library → for the GROUP BY + windowed-aggregate problems that cluster columns are designed to accelerate.

Lock in BQ partition+cluster muscle memory

BQ docs explain the DDL. PipeCode drills explain the decision — when to partition by date vs integer-range, when to add a second cluster column, when require_partition_filter is the only safe default. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior data engineers actually face.

Practice SQL problems →
Practice optimization problems →