BigQuery Cost Optimization in 2026: Slot Reservations, Editions, and the Levers That Actually Cut the Bill

Updated: June 26, 2026

BigQuery cost optimization means cutting your GCP data warehouse bill by matching workload patterns to the right pricing model (on-demand for spiky ad-hoc work, BigQuery Editions slot reservations for predictable production pipelines), while ruthlessly partitioning, clustering, and pruning the data each query scans. I've migrated three FinOps programs off the legacy flat-rate model onto Editions in the last twelve months, and the same five-lever playbook has dropped each bill 45–68%. This guide walks through every lever with 2026 prices, real SQL, and the spreadsheet math I actually use on engagements.

• BigQuery on-demand pricing is $6.25 per TiB scanned (US multi-region, 2026); the first 1 TiB per month is free per billing account.
• BigQuery Editions (Standard, Enterprise, Enterprise Plus) replaced flat-rate in 2023 and autoscale slots per second. Three-year commits cut Enterprise slot-hours by 40%.
• Partition by ingestion time or a date column, then cluster on your two highest-cardinality filter columns. Typical scan reduction is 60–95%.
• Materialized views and BI Engine sub-second caching can cut interactive dashboard cost to near zero for the same SLA.
• INFORMATION_SCHEMA.JOBS_BY_PROJECT is the single best billing telemetry source. Query it daily for chargeback, anomaly detection, and slot utilization.
• Switch on-demand workloads that exceed ~$2,000/month per project to Enterprise Edition autoscaling. The break-even is usually two to three concurrent heavy users.

BigQuery pricing in 2026: on-demand vs Editions

Google retired the legacy flat-rate model and migrated everyone to BigQuery Editions in July 2023. As of June 2026, the headline rates that drive every optimization decision are: on-demand analysis at $6.25 per TiB scanned, Standard Edition slots at $0.04 per slot-hour (pay-as-you-go autoscaling, no commits), Enterprise at $0.06, and Enterprise Plus at $0.10 per slot-hour. One-year commitments knock 20% off Enterprise and Enterprise Plus; three-year commits hit 40%. Storage is $0.02 per GiB-month for active logical, $0.01 for long-term logical (untouched 90+ days), with optional physical billing at $0.04 / $0.02 that often pays for itself on highly compressible data.

The trap I see most: teams stay on on-demand long past the break-even, because the on-demand price per query looks tiny. Run a 500 GiB scan and it costs $3.05. Feels harmless. Now multiply by twelve analysts running thirty queries each per day and you're at $1,098 daily, or roughly $33,000 a month for the same workload that 400 Enterprise slots ($17,280/month on a 1-year commit) would handle with capacity to spare.

How do I choose between on-demand and slot reservations?

The decision rule I use in workshops: stay on on-demand if your monthly BigQuery bill is under ~$2,000 per project AND your scans are bursty and small (median < 50 GiB). Switch to Standard Edition autoscaling slots if you have 1–2 predictable hourly pipelines plus interactive use. Move to Enterprise the moment you have BI dashboards needing materialized views, row-level security, or you're spending more than $5,000/month. Go Enterprise Plus only if you need cross-region replication or customer-managed encryption with key access justifications.

The break-even calc in plain math: an Enterprise slot delivers roughly 0.5–1.5 TiB scanned per slot-hour depending on query complexity. So 100 autoscaling Enterprise slots for an hour ($6) processes 50–150 TiB. On demand, 50 TiB would cost $312.50. Once your concurrent demand crosses two heavy analysts running multi-TiB scans, slots win, every single time. The official BigQuery pricing page includes a slot estimator, but I prefer reconciling against my own seven-day INFORMATION_SCHEMA pull (script below) because it accounts for retries and DML statement cost that the estimator misses.

Partitioning and clustering: the biggest single win

Honestly, every cost-cutting story I've ever shipped started here. BigQuery on-demand bills you for bytes scanned, not bytes returned, and Editions slot-hours scale linearly with the work too. Partition pruning lets the planner skip whole files; clustering refines that further inside a partition. The rule: partition on the column you filter by most (almost always a date), cluster on the next one to four columns you filter or join by, in decreasing selectivity order.

-- Bad: a 4 TiB events table, no partitioning, queried daily by date.
-- Every query scans the whole table -> $25 per query, 30 queries/day -> $22,500/month.
CREATE TABLE analytics.events_raw AS
SELECT * FROM source.events;

-- Good: ingestion-time partition + cluster on the two main filter columns.
CREATE TABLE analytics.events
PARTITION BY DATE(event_timestamp)
CLUSTER BY tenant_id, event_name
OPTIONS (
  partition_expiration_days = 400,
  require_partition_filter = TRUE
)
AS SELECT * FROM source.events;

-- Now this query scans ~12 GiB instead of 4 TiB:
SELECT tenant_id, COUNT(*) AS hits
FROM analytics.events
WHERE DATE(event_timestamp) BETWEEN '2026-06-01' AND '2026-06-07'
  AND event_name = 'checkout_completed'
GROUP BY tenant_id;

Two non-obvious tactics. First, set require_partition_filter = TRUE. That throws an error on any query that forgets a partition predicate, which is the only way to stop someone from triggering a $25 surprise scan during incident response (ask me how I know). Second, when your filter is a timestamp at sub-day granularity, partition by day but cluster on TIMESTAMP_TRUNC(event_timestamp, HOUR) as the first clustering column. Daily partitions keep the partition count manageable (BigQuery's hard limit is 10,000 partitions per table), while hourly clustering still gets you tight pruning on intra-day windows. For long-lived event tables, also pair this with the broader patterns in our cloud database cost optimization guide.

Query rewrites that actually move the needle

Most "BigQuery best practices" lists rehash SELECT * warnings. True, but the bigger wins in 2026 are subtler:

• Push aggregation into CTEs before joins. A common pattern is joining a 10 GiB dim table to a 4 TiB fact, then aggregating. Aggregate the fact down first, then join. Same result, scan stays inside one branch.
• Use APPROX_COUNT_DISTINCT when you don't need exact counts. The HyperLogLog++ implementation is accurate to about 1% and uses a fraction of the slot-seconds of COUNT(DISTINCT …).
• Avoid SELECT * EXCEPT (col) on wide tables. BigQuery is columnar, so every named column you read costs scan bytes. List the columns you actually need.
• Replace WHERE date_col BETWEEN x AND y with explicit partition column references. A function over the partition column (EXTRACT, FORMAT_DATE, casts) can defeat partition pruning. Confirm by checking the "bytes processed" estimate in the UI before you run.
• Materialize intermediate steps for repeated pipelines. If two scheduled queries both scan the same 800 GiB raw table to produce overlapping aggregates, write the shared aggregation to a table once and have both downstream jobs read from it.

Materialized views, BI Engine, and result caching

Three caching layers compound on each other, and most teams use none of them.

Result cache. Free, automatic, 24-hour TTL, invalidated by any DML on the underlying tables. Identical queries from the same query text hit the cache. Use deterministic queries (no CURRENT_TIMESTAMP()) and you'll see cache hit rates of 30–60% for dashboard traffic, at zero cost.

Materialized views. Pre-computed, incrementally maintained aggregates. Cost is the storage of the view plus the maintenance scan when source data changes. A materialized view over a 5 TiB events table that pre-aggregates daily-tenant counts will turn a $32 query into a $0.04 query. As of BigQuery's 2025 update, materialized views now support more aggregations (including APPROX_QUANTILES) and joins with constant-size dimension tables.

BI Engine. An in-memory accelerator that targets sub-second response for dashboards. You allocate GiB of reservation per project ($0.04/GiB/hour, so $30/GiB/month). For a 100 GiB BI Engine reservation backing a Looker workspace serving 200 users, you'll often see queries that used to scan 80 GiB ($0.50 each) drop to free because they're served entirely from memory. The math: at $3,000/month for the reservation, you break even at 6,000 such queries. Easy on any real BI deployment.

-- Materialized view: pre-aggregate the dashboard's hot query.
CREATE MATERIALIZED VIEW analytics.events_daily_tenant_mv
PARTITION BY event_day
CLUSTER BY tenant_id
AS
SELECT
  DATE(event_timestamp)         AS event_day,
  tenant_id,
  event_name,
  COUNT(*)                      AS event_count,
  APPROX_COUNT_DISTINCT(user_id) AS approx_uniques
FROM analytics.events
GROUP BY 1, 2, 3;

-- Looker now hits this 200 MiB view instead of the 5 TiB base table.
-- Maintenance: BigQuery refreshes incrementally on insert; you pay only for delta scans.

Storage pricing: physical billing and table TTLs

Storage is usually 10–25% of a BigQuery bill, and it's the easiest line to forget. Two underused levers:

Switch high-compression tables to physical billing. Logical bytes is what you'd see if data were uncompressed JSON; physical bytes is what BigQuery actually stores after columnar compression. Wide event tables with low-cardinality columns commonly compress 6–10x. At logical pricing ($0.02/GiB-month) you pay for the uncompressed size; physical pricing ($0.04/GiB-month, double the per-byte rate) charges only the compressed footprint. Run this and switch anything compressing better than 2:1:

SELECT
  table_schema,
  table_name,
  total_logical_bytes  / POW(1024, 3) AS logical_gib,
  total_physical_bytes / POW(1024, 3) AS physical_gib,
  SAFE_DIVIDE(total_logical_bytes, total_physical_bytes) AS compression_ratio,
  -- monthly savings if you switch billing model:
  ROUND(((total_logical_bytes * 0.02) - (total_physical_bytes * 0.04)) / POW(1024, 3), 2)
    AS monthly_savings_usd
FROM `region-us`.INFORMATION_SCHEMA.TABLE_STORAGE
WHERE total_physical_bytes > 0
ORDER BY monthly_savings_usd DESC
LIMIT 50;

Set table and partition expiration. partition_expiration_days = 400 on an event table is the difference between paying for 13 months of partitions forever versus paying for 14 months once and rolling. Use default_table_expiration_ms at the dataset level to clean up exploratory analyst tables that nobody remembers to drop. This pairs well with the broader cleanup patterns I covered in find and eliminate zombie cloud resources.

Monitor cost with INFORMATION_SCHEMA

This is the spreadsheet I open every Monday. INFORMATION_SCHEMA.JOBS_BY_PROJECT (and the org-level JOBS_BY_ORGANIZATION view) gives you per-job bytes billed, slot-millis, user identity, and labels. Pull it daily into a dashboard and you have FinOps showback without a third-party tool.

-- Top 25 most expensive on-demand queries last 7 days.
SELECT
  user_email,
  job_id,
  query,
  total_bytes_billed / POW(1024, 4)              AS tib_billed,
  total_bytes_billed / POW(1024, 4) * 6.25       AS est_on_demand_usd,
  total_slot_ms / 1000 / 3600                    AS slot_hours,
  creation_time
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND statement_type = 'SELECT'
  AND state = 'DONE'
  AND error_result IS NULL
ORDER BY total_bytes_billed DESC
LIMIT 25;

-- Daily slot-hour consumption by team (using a 'team' label):
SELECT
  DATE(creation_time) AS day,
  labels.value        AS team,
  ROUND(SUM(total_slot_ms) / 1000 / 3600, 1) AS slot_hours,
  ROUND(SUM(total_slot_ms) / 1000 / 3600 * 0.06, 2) AS enterprise_cost_usd
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT,
     UNNEST(labels) AS labels
WHERE labels.key = 'team'
  AND creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
GROUP BY day, team
ORDER BY day DESC, slot_hours DESC;

I export this to BigQuery itself with a scheduled query, then sync it into a FOCUS-conformant cost dataset alongside our AWS and Azure data. That's exactly the unified-billing pattern from our FOCUS 1.2 unified cloud cost data guide. Cross-cloud chargeback is dramatically easier when BigQuery rows look like every other line item.

Guardrails: custom quotas and maximum bytes billed

The single most effective anti-bill-shock control is --maximum_bytes_billed. Set it at the query, job, or session level and BigQuery refuses to run any query whose dry-run estimate exceeds it. I configure 1 TiB as the default ceiling on analyst-run-and-forget queries; production pipelines get explicit budgets per job.

# bq CLI: cap an interactive session at 100 GiB.
bq query \
  --maximum_bytes_billed=107374182400 \
  --use_legacy_sql=false \
  'SELECT tenant_id, COUNT(*) FROM analytics.events
   WHERE DATE(event_timestamp) = "2026-06-25" GROUP BY 1'

Layer on custom user-level quotas in the project IAM settings: cap an individual user at, say, 5 TiB per day on on-demand projects, and cap entire projects at a daily ceiling. Combine with billing budgets and Pub/Sub alerts to get paged before you blow the month's budget. For the broader cross-cloud guardrail pattern, see our cloud cost anomaly detection guide. There's also a useful breakdown in the Google Cloud custom quotas docs if you want the exact gcloud syntax.

Chargeback and cost allocation in a multi-team project

Most BigQuery deployments I audit share one project across multiple teams, which makes per-team attribution painful. The clean fix is a labeling discipline enforced at the connection layer:

1. Define a team label (mandatory) and a cost_center label (optional) on every job. Set them in your dbt project's profiles.yml, your Looker connection, your Airflow operator's labels kwarg, and your scheduled-query JSON.
2. Build an Org Policy that denies job creation without the required labels (though as of June 2026 you'll need a custom validator since BigQuery doesn't natively reject unlabeled jobs).
3. Materialize a daily allocation table joining JOBS_BY_ORGANIZATION labels with slot reservation assignments. For autoscaled Editions, attribute slot-hour cost proportionally to total_slot_ms by team.
4. For storage, attribute by dataset owner. Use a dataset naming convention like team_marketing__raw and parse the prefix in your allocation script. Much more reliable than relying on dataset labels (which existed before bytes-based billing was common).

That five-line Python proportional allocation (slot-hours per team, divided by total slot-hours, times the Enterprise commit cost) plus storage attribution by dataset prefix has gotten me within 2% reconciliation against the consolidated GCP invoice on every engagement. The pattern is the same one I described for Kubernetes teams in our Kubernetes chargeback and showback pipeline, and the principles transfer directly to BigQuery.

Putting it together: a 90-day playbook

If I land at a new client tomorrow with a $40,000/month BigQuery bill, here's the order I work in. Days 1–7: pull JOBS_BY_PROJECT for the last 30 days, identify the top 20 queries by bytes billed (this is almost always 80% of the spend), and force-add partition filters or rewrite them. Days 8–21: stand up a Standard or Enterprise reservation sized to p95 slot demand, switch dashboard projects onto it. Days 22–45: materialize the top five dashboard queries as materialized views, evaluate BI Engine for the heaviest Looker workspaces. Days 46–75: roll out labeling, build the daily allocation table, kick off chargeback conversations. Days 76–90: switch the highest-compression tables to physical billing, set partition TTLs, and commit to a one-year or three-year reservation now that demand is stable. I have yet to see this sequence not cut the bill by at least half.

Frequently Asked Questions

Is BigQuery cheaper than Snowflake in 2026?

For predictable, high-concurrency workloads, BigQuery Editions with a three-year Enterprise commit usually beats Snowflake's equivalent committed capacity by 10–25% per TiB processed, and BigQuery's storage is significantly cheaper. For bursty, low-volume use, on-demand BigQuery is often cheaper than the smallest Snowflake warehouse left running. See our Snowflake cost optimization guide for the detailed apples-to-apples comparison.

What is the difference between BigQuery Editions and the old flat-rate model?

Flat-rate sold you a fixed slot pool for a flat monthly fee. Editions sells slots in three feature tiers (Standard, Enterprise, Enterprise Plus) and bills per slot-hour with autoscaling, so you only pay for slots actually used per second, with optional 1-year or 3-year commits at 20% and 40% discounts. Flat-rate was sunset in July 2023.

How do I monitor BigQuery costs by user or team?

Query INFORMATION_SCHEMA.JOBS_BY_PROJECT or JOBS_BY_ORGANIZATION daily. It exposes user_email, labels, total_bytes_billed, and total_slot_ms per job. Enforce a team label on every connection (dbt, Looker, Airflow), then group by label for showback. Schedule the query to materialize into a daily cost table for dashboards.

Should I use physical or logical storage billing?

Use physical when your compression ratio exceeds 2:1. Wide event tables with repeating low-cardinality columns commonly compress 6–10x and save 50%+ on storage by switching. Run a query against INFORMATION_SCHEMA.TABLE_STORAGE comparing total_logical_bytes and total_physical_bytes per table to decide.

Does BI Engine save money or just speed things up?

Both. Queries served from BI Engine memory are not billed for bytes scanned and consume zero slot-time on the base reservation. The reservation itself costs about $30 per GiB-month, so any workload with more than a few thousand cache-hittable queries per month against the same underlying tables pays for itself, often by 5–10x.

How do I prevent a single query from blowing up my BigQuery bill?

Set --maximum_bytes_billed on every job (default 1 TiB for interactive sessions is a good starting cap), enable require_partition_filter = TRUE on large tables, and configure custom per-user daily query quotas in IAM. Layer on a billing budget with a Pub/Sub alert at 80% to get paged before month-end surprises.