← All articles

BigQuery Cost Control: Slots, On‑Demand Pricing, and Query Tuning

1. Why BigQuery Costs Spike

BigQuery charges you for two orthogonal resources:

• Slots – a reservation of compute capacity (1 slot ≈ 1 vCPU). You pay a flat monthly rate for a committed slot pool or let the system allocate slots on demand.
• On‑demand query processing – measured in bytes processed. If you run a query without a slot reservation, each byte scanned is billed at the on‑demand rate.

When a team forgets to cap slots, runs cross‑project queries, or scans unpartitioned tables, the bill can grow overnight. The goal of this post is to give you concrete levers you can pull today.

2. Slots vs. On‑Demand: Choose the Right Model

Actionable step: If your daily processed data exceeds 2 TB consistently, calculate the break‑even point:

# Approximate daily on‑demand cost
processed_tb=3
cost=$(echo "$processed_tb * 5" | bc)
echo "Daily on‑demand cost: $${cost}"

If the result is >$150 per day, a flat‑rate slot purchase is likely cheaper.

3. Monitoring Slot Utilization

BigQuery provides two primary ways to see how many slots you are using:

1. bq command‑line – query the INFORMATION_SCHEMA.JOBS_BY_PROJECT view.
2. Cloud Monitoring – built‑in metric bigquery.googleapis.com/slot_utilization.

3.1 Quick CLI check

# Show average slot usage for the last 24 h (requires Cloud Billing permission)
bq query --use_legacy_sql=false \
'SELECT AVG(slot_ms/1000) AS avg_seconds_per_job 
 FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT 
 WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR) 
   AND state = "DONE";'

If the average is far below the total slots you own, you are over‑provisioned.

3.2 Set up an alert in Cloud Monitoring

1. Open Monitoring → Alerting → Create Policy.
2. Select Metric → bigquery.googleapis.com/slot_utilization.
3. Condition: Mean over 1 hour < 30 % of your purchased slots.
4. Notification channel: email or Slack.

When the alert fires, consider downsizing the slot reservation or moving idle workloads to on‑demand.

4. Query‑Level Cost Controls

Even with a perfect slot size, poorly written SQL can waste bytes. Follow these concrete practices.

4.1 Use SELECT‑only the columns you need

-- Bad: SELECT *
SELECT * FROM `myproject.dataset.large_table`;

-- Good: select needed fields
SELECT user_id, event_timestamp FROM `myproject.dataset.large_table`;

BigQuery still scans the entire table, but downstream processing is faster and downstream tools (e.g., Data Studio) read less data.

4.2 Filter early with WHERE clauses on partitioned columns

If event_date is a DATE column used for partitioning:

SELECT COUNT(*) FROM `myproject.dataset.events`
WHERE event_date BETWEEN '2024-01-01' AND '2024-01-31';

Only the 31 partitions are read, reducing scanned bytes dramatically.

4.3 Leverage WITH (CTE) wisely

BigQuery materializes each CTE unless you add the OPTIONS (materialized_view = false) hint. For large intermediate results, rewrite as a sub‑query that can be pruned.

4.4 Use APPROX_COUNT_DISTINCT instead of COUNT(DISTINCT …)

SELECT APPROX_COUNT_DISTINCT(user_id) FROM `myproject.dataset.events`;

The approximation is accurate to ±1 % and avoids a full shuffle.

5. Partitioning, Clustering, and Table Design

5.1 Partition by ingestion date or a natural time column

bq mk --time_partitioning_type=DAY \
  --time_partitioning_field=event_date \
  myproject:dataset.events_partitioned

Load data into the partitioned table; queries that filter on event_date will read only relevant partitions.

5.2 Add clustering on high‑cardinality columns used in filters

bq update --clustering_fields=user_id,region myproject:dataset.events_partitioned

Clustering stores rows with the same user_id/region together, allowing BigQuery to skip blocks during filter execution.

5.3 When to use materialized views

If you have a frequently run aggregation (e.g., daily active users), create a materialized view:

bq mk --use_legacy_sql=false \
  --materialized_view "SELECT event_date, COUNT(DISTINCT user_id) AS dau FROM `myproject.dataset.events_partitioned` GROUP BY event_date" \
  myproject:dataset.dau_mv

BigQuery automatically keeps the view up‑to‑date and charges only for the bytes read from the underlying table, not the full scan.

6. Automating Cost‑Saving Checks

1. Scheduled query to flag high‑scan jobs

CREATE OR REPLACE TABLE `myproject.dataset.high_scan_jobs` AS
SELECT job_id, user_email, total_bytes_processed/ (1024*1024*1024) AS gb_processed,
       TIMESTAMP_TRUNC(start_time, DAY) AS day
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE total_bytes_processed > 10*1024*1024*1024  -- >10 GB
  AND state = 'DONE'
  AND start_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY);

Schedule it daily via Cloud Scheduler. Review the table and reach out to owners to optimize.

2. Enforce a maximum on‑demand spend per project Set a budget in Cloud Billing → Budgets & alerts with a threshold of 80 % of your monthly allowance. Attach a Pub/Sub notification that triggers a Cloud Function to disable the project's BigQuery API temporarily if the threshold is breached.

7. Quick Checklist for Engineers & Founders

• [ ] Identify if your workload is >2 TB/day → consider flat‑rate slots.
• [ ] Set up slot‑utilization alerts (<30 % usage).
• [ ] Partition tables on date or ingestion time.
• [ ] Add clustering on frequent filter columns.
• [ ] Replace SELECT * with explicit column lists.
• [ ] Use APPROX_COUNT_DISTINCT where exact counts aren’t required.
• [ ] Create materialized views for repetitive aggregations.
• [ ] Schedule a daily “high‑scan jobs” report.
• [ ] Configure a budget alert that notifies the team before overspend.

How CloudBudgetMaster helps: Our platform continuously scans your BigQuery projects, automatically detects under‑utilized slot pools, high‑scan queries, and tables missing partitioning or clustering, and surfaces the estimated dollar impact so you can act before the bill surprises you.