The Real Cost of BigQuery On-Demand Pricing
BigQuery's on-demand pricing looks simple on paper — $6.25 per TB scanned. But once your analytics team starts running 500+ queries per day across multiple projects, that simplicity turns into unpredictability. I've watched monthly bills swing from $8,000 to $47,000 without any significant change in the number of analysts on the team. The problem isn't the pricing model itself. It's that nobody knows how much a query will cost until after it runs.
Flat-rate pricing with reserved slots gives you predictability, but it comes with its own set of tradeoffs. You're buying compute capacity whether you use it or not, and right-sizing that capacity requires understanding your actual workload patterns better than most teams do when they first make the switch.
Slot Architecture and How BigQuery Allocates Compute
A BigQuery slot is a unit of computational capacity — roughly one virtual CPU with a fraction of RAM allocated. When you submit a query, BigQuery's scheduler breaks it into stages, and each stage gets distributed across available slots. More complex queries need more slots. Joins against large tables, window functions over partitioned datasets, repeated subqueries — they all consume slots differently.
The scheduler doesn't guarantee even distribution. A single massive query can starve other queries in the same reservation if you haven't configured concurrency targets properly. I've seen production dashboards time out because a data scientist ran an unpartitioned full-table scan in the same reservation pool.
# Check current slot utilization via INFORMATION_SCHEMA
SELECT
period_start,
period_slot_ms,
period_shuffle_ram_usage_ratio,
project_id,
job_type
FROM
`region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE
WHERE
period_start BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
AND CURRENT_TIMESTAMP()
AND state = 'RUNNING'
ORDER BY period_slot_ms DESC
LIMIT 50;Reservation Architecture for Multi-Team Organizations
BigQuery's reservation model has three layers: commitments, reservations, and assignments. Commitments are the billing construct — you purchase a number of slots for a fixed period (annual or monthly, with flex slots available for shorter durations). Reservations are logical pools carved from your committed slots. Assignments map projects, folders, or the entire organization to specific reservations.
The hierarchy matters more than it looks. When a project doesn't match any assignment, it falls back to on-demand pricing. When a reservation runs out of slots, queries queue instead of failing. This queuing behavior catches teams off guard — they expect errors but get slow queries instead.
# Create a reservation hierarchy with Terraform
resource "google_bigquery_reservation" "analytics_prod" {
name = "analytics-prod"
location = "US"
slot_capacity = 500
edition = "ENTERPRISE"
ignore_idle_slots = false
}
resource "google_bigquery_reservation" "analytics_dev" {
name = "analytics-dev"
location = "US"
slot_capacity = 100
edition = "ENTERPRISE"
ignore_idle_slots = false
}
resource "google_bigquery_reservation_assignment" "prod_assignment" {
assignee = "projects/my-prod-project"
job_type = "QUERY"
reservation = google_bigquery_reservation.analytics_prod.id
}
resource "google_bigquery_reservation_assignment" "dev_assignment" {
assignee = "folders/123456789"
job_type = "QUERY"
reservation = google_bigquery_reservation.analytics_dev.id
}Idle Slot Sharing
Setting ignore_idle_slots = false lets a reservation borrow unused slots from sibling reservations. This sounds great in theory, and it mostly works well, but there's a catch: when the lending reservation suddenly needs its slots back, the borrowing queries don't get preempted — they continue running. So during burst periods, you can temporarily exceed your intended capacity boundaries. For most teams, this tradeoff is worth it. The alternative is wasted capacity sitting idle in one reservation while another queues.
Monitoring Slot Utilization Patterns
You can't optimize what you don't measure. BigQuery exposes slot utilization through INFORMATION_SCHEMA views, but the raw data requires some work to turn into actionable metrics.
# Hourly slot utilization aggregation
SELECT
TIMESTAMP_TRUNC(period_start, HOUR) AS hour,
reservation_id,
AVG(period_slot_ms / (1000 * 60)) AS avg_slot_minutes,
MAX(period_slot_ms / (1000 * 60)) AS peak_slot_minutes,
COUNT(DISTINCT job_id) AS concurrent_jobs
FROM
`region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_ORGANIZATION
WHERE
period_start >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
AND reservation_id IS NOT NULL
GROUP BY 1, 2
ORDER BY 1 DESC, 2;The pattern you're looking for is the ratio between average utilization and peak utilization. If your average is 200 slots but your peak hits 800, you've got a bursty workload that might benefit from autoscaling rather than fixed reservations. If your average is 450 and your peak is 520, you've got a steady workload where fixed capacity makes sense.
Autoscaling with Edition Slots
BigQuery Editions (introduced to replace flat-rate) include an autoscaling feature. You set a baseline slot count and a maximum, and BigQuery scales between them based on demand. The billing is per-slot-second for the autoscaled portion, which sits between on-demand and committed pricing.
Here's the thing most documentation skips: autoscaling doesn't react instantly. There's a warmup period of 30-60 seconds before additional slots become available. For short-running queries, that delay means they finish before the extra capacity arrives. Autoscaling works best for workloads with sustained bursts lasting several minutes — think ETL jobs, not ad-hoc dashboard queries.
Configuring Autoscaling
resource "google_bigquery_reservation" "analytics_auto" {
name = "analytics-autoscale"
location = "US"
slot_capacity = 200 # baseline
edition = "ENTERPRISE"
autoscale {
max_slots = 800 # burst ceiling
}
}I'd recommend starting with a baseline that covers your P50 workload and a max that covers your P95. Watch the autoscaling behavior for two weeks before adjusting — the utilization patterns might surprise you.
Cost Optimization Strategies That Actually Work
Partition pruning is the single biggest cost lever in BigQuery, whether you're on on-demand or flat-rate. A query that scans a partitioned table with a proper WHERE clause on the partition column can reduce scanned data by 95%+ compared to the same query without the filter. This sounds obvious, but I've audited organizations where 40% of their query spend came from queries that didn't filter on the partition column of partitioned tables.
Clustering is the second lever. It won't reduce the bytes scanned as dramatically as partitioning, but it reduces the amount of data BigQuery needs to read from storage blocks. For tables partitioned by date and clustered by customer_id, a query filtering on both dimensions reads a fraction of what an unoptimized scan would touch.
Materialized views are underused. If you've got a dashboard that runs the same aggregation 200 times a day across 50 users, a materialized view computes it once and serves cached results. BigQuery automatically refreshes materialized views when the base table changes, and the query optimizer rewrites queries to use them transparently.
BI Engine Reservations for Dashboard Acceleration
BI Engine maintains an in-memory cache of BigQuery data. It's particularly effective for Looker and Google Sheets connected to BigQuery, where the same tables get queried repeatedly with slight variations in filters. The reservation is separate from your query slot reservation — you're allocating RAM, not CPU.
resource "google_bigquery_bi_reservation" "looker_cache" {
location = "US"
size = "10" # GB of in-memory cache
}Ten GB of BI Engine reservation covered 85% of our Looker dashboard queries in one deployment. The key is identifying which tables your dashboards actually hit — it's usually fewer than you'd expect, and they're almost always summary or dimension tables rather than raw event tables.
Building a Cost Allocation Model
Labels are your friend. BigQuery supports labels on datasets, tables, and jobs. Combined with INFORMATION_SCHEMA billing data, you can build per-team cost allocation without requiring separate projects for each team.
# Per-team cost allocation query
SELECT
labels.value AS team,
SUM(total_bytes_billed) / POW(1024, 4) AS tb_billed,
SUM(total_bytes_billed) / POW(1024, 4) * 6.25 AS estimated_on_demand_cost,
COUNT(*) AS query_count,
AVG(total_slot_ms) / 1000 AS avg_slot_seconds
FROM
`region-us`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION,
UNNEST(labels) AS labels
WHERE
labels.key = 'team'
AND creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
GROUP BY 1
ORDER BY 3 DESC;The combination of slot-based reservations per team (for capacity isolation) with label-based cost tracking (for visibility) gives finance teams what they need without creating an unmanageable number of GCP projects. We settled on this pattern after trying project-per-team and finding the IAM overhead wasn't worth it for analytics workloads.