BigQuery Cost and Performance: Partitioning, Clustering, Slots

BigQuery is fast by default, but careless queries on large tables can be expensive. Most savings come from reading fewer bytes.

flowchart LR
  Q["Query: orders of 5 Nov<br/>for customer C-1042"] --> P["Partition pruning<br/>only 2026-11-05"]
  P --> C["Clustering by customer_id<br/>only matching blocks"]
  C --> S["Bytes scanned: 40 MB<br/>instead of 2 TB"]
  DR["--dry_run"] --> S
  MB["maximum_bytes_billed"] --> S
  %% step Q: The query filters on the partition column and on a clustered column.
  %% step P: With a table partitioned by order_date, BigQuery skips every other day’s partition.
  %% step C: Inside the partition, clustering by customer_id lets it read only the blocks that can match.
  %% step S: The query scans megabytes instead of terabytes, so it is cheaper and faster.
  %% step DR: A dry run reports the bytes a query would scan without running it.
  %% step MB: A maximum bytes billed limit makes a query fail instead of running up a large bill.

Partitioning

• Set require partition filter so queries without a date filter are rejected.
• Set partition expiration to drop old data automatically.

Clustering

Sort storage by up to four columns you filter or join on (for example customer_id, country). BigQuery maintains it automatically. Clustering helps most on large tables (over ~1 GB).

CREATE TABLE shop.orders_p
PARTITION BY DATE(created_at)
CLUSTER BY customer_id, country
OPTIONS (require_partition_filter = TRUE, partition_expiration_days = 730)
AS SELECT * FROM shop.orders;

Checking and capping cost

bq query --use_legacy_sql=false --dry_run   'SELECT total FROM shop.orders_p WHERE DATE(created_at) = "2026-11-05"'
bq query --use_legacy_sql=false --maximum_bytes_billed=10000000000 '…'

• The console shows the estimated bytes before you run a query.
• Set custom quotas on query bytes per day per user or per project.
• Watch INFORMATION_SCHEMA.JOBS to find the most expensive queries and users.

Performance tips

• Select only needed columns; filter early; avoid SELECT * in views.
• Pre-aggregate with materialized views; repeat queries hit the cache (24 hours, free).
• BI Engine accelerates dashboards in memory.
• Denormalise with nested and repeated fields (STRUCT, ARRAY) instead of many joins.

Slots and editions

A slot is a unit of compute. On-demand projects share a large pool. With editions you create reservations (baseline slots plus autoscaling) and assign projects to them; commitments lower the price. Use editions when on-demand bills are high and steady, or when you need workload isolation.

Traps: wrapping the partition column in a function the planner cannot use can disable pruning; clustering on too many or rarely used columns gives little benefit; the query cache does not apply to queries with non-deterministic functions such as CURRENT_TIMESTAMP().