BigQuery Cost Optimisation: Reduce Your Bill

Most teams don’t notice their BigQuery spend when the platform is new. The first few datasets are small, queries are mostly ad hoc, and the bill looks reasonable enough. Then six months later someone opens the invoice, sees a step change, and asks the familiar question: why are we paying this much just to run SQL?

The awkward answer is that BigQuery makes it very easy to spend money accidentally. That’s not a criticism of the platform — it’s one of the reasons it scales so well. But if you don’t put some engineering discipline around storage layout, query patterns, slot usage and governance, costs drift upwards quietly and continuously.

We’ve seen this across data platforms built for businesses ranging from scale-ups to large enterprises, including work for organisations such as IKEA, SoundCloud, Napster, Hilti Group, Ocado and NEOM. The patterns are usually the same. Too many full-table scans. Partitioning that exists on paper but not in practice. Incremental models that aren’t actually incremental. BI tools issuing expensive queries every few minutes. Reservations bought without a clear workload strategy. Or the opposite: everything left on on-demand pricing long after usage became predictable.

This guide is how we approach BigQuery cost optimisation in practice. Not generic advice, but the checks and changes that actually reduce BigQuery costs.

Start with the billing model, because everything else depends on it

Before tuning anything, be clear on how you are being charged. BigQuery pricing has several moving parts, but for most teams the main levers are:

1. Query compute

• On-demand pricing: charged per TiB scanned
• Capacity pricing: charged for slots via reservations

• Active storage
• Long-term storage

1. Ancillary services

• BI Engine
• BigQuery ML
• Search indexes
• Metadata indexing
• Cross-cloud or external query features where used

If you’re trying to reduce BigQuery costs, the first decision is whether your workloads are better suited to on-demand or capacity-based pricing.

A simple rule of thumb:

• On-demand is usually better when workloads are bursty, unpredictable, and not especially large.
• Capacity pricing is usually better when workloads are steady, heavy, or business-critical enough that predictable performance matters.

The mistake is assuming one is always cheaper. It isn’t. We’ve seen teams move to reservations too early and pay for idle slots, and we’ve seen others stay on on-demand while repeatedly scanning tens of terabytes every day because nobody wanted to revisit the commercial model.

A practical evaluation looks like this:

-- Estimate bytes processed by user / project / day
SELECT
  DATE(creation_time) AS run_date,
  project_id,
  user_email,
  COUNT(*) AS query_count,
  ROUND(SUM(total_bytes_processed) / POW(1024, 4), 2) AS tib_processed,
  ROUND(SUM(total_slot_ms) / 1000 / 60 / 60, 2) AS slot_hours
FROM region-eu.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
  AND statement_type != 'SCRIPT'
GROUP BY 1, 2, 3
ORDER BY run_date DESC, tib_processed DESC;

This gives you two important views at once:

• how much data you scan under on-demand pricing
• how much slot consumption your workloads actually require

If your usage is stable, slot-hours are consistently high, and critical pipelines are competing with analysts for resources, it’s time to look seriously at BigQuery slot management.

Find the expensive queries before changing architecture

Most cost reduction work starts with the wrong question: “Should we repartition everything?” Usually not. First identify the top 20 queries, models or dashboards responsible for most spend.

BigQuery gives you enough metadata to do this properly. We normally start with INFORMATION_SCHEMA.JOBS* and group by query hash, user, labels, service account and destination.

WITH jobs AS (
  SELECT
    creation_time,
    user_email,
    job_id,
    total_bytes_processed,
    total_slot_ms,
    query,
    destination_table.project_id AS dest_project,
    destination_table.dataset_id AS dest_dataset,
    destination_table.table_id AS dest_table
  FROM region-eu.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 14 DAY)
    AND job_type = 'QUERY'
)
SELECT
  user_email,
  COUNT(*) AS executions,
  ROUND(SUM(total_bytes_processed) / POW(1024, 4), 2) AS tib_processed,
  ROUND(AVG(total_bytes_processed) / POW(1024, 3), 2) AS avg_gib_per_query,
  ROUND(SUM(total_slot_ms) / 1000 / 60, 2) AS total_slot_minutes
FROM jobs
GROUP BY user_email
ORDER BY tib_processed DESC
LIMIT 20;

Then go one level deeper:

• Which dashboards run every 5 minutes?
• Which dbt models scan the same raw tables repeatedly?
• Which ad hoc queries read 100 columns to return 3?
• Which scheduled queries are still running even though nobody uses the output?

A good optimisation programme is usually very boring in the best possible way. You remove waste first.

Here’s the process we typically follow:

flowchart TD
    A[Collect billing and JOBS metadata] --> B[Rank high-cost queries and workloads]
    B --> C[Classify by cause]
    C --> D1[Excessive bytes scanned]
    C --> D2[Poor slot utilisation]
    C --> D3[Storage growth]
    C --> D4[BI / orchestration waste]
    D1 --> E[Fix SQL, partitioning, clustering, materialisation]
    D2 --> F[Adjust reservations, assignments, concurrency]
    D3 --> G[Retention, table lifecycle, compaction]
    D4 --> H[Refresh cadence, caching, model redesign]
    E --> I[Measure savings]
    F --> I
    G --> I
    H --> I
    I --> J[Set guardrails and monitoring]

If you skip the measurement step, you’ll never know whether your BigQuery cost optimisation work actually worked.

Reduce bytes scanned: this is still the biggest win for most teams

For on-demand workloads, scanned bytes are the bill. Even under capacity pricing, excessive scanning still hurts because it burns slots and creates contention. In both cases, better table design and better SQL matter.

Partition properly, then make sure queries actually use partitions

A partitioned table that is queried without a partition filter is one of the most common self-inflicted costs in BigQuery.

Create partitioned tables on a column that aligns with real access patterns, not just what happened to be available at ingestion time.

CREATE TABLE analytics.events
PARTITION BY DATE(event_timestamp)
CLUSTER BY customer_id, event_name
AS
SELECT * FROM raw.events;

Then enforce partition filters where appropriate:

ALTER TABLE analytics.events
SET OPTIONS (
  require_partition_filter = TRUE
);

This one setting prevents a lot of accidental full-table scans.

A good query:

SELECT
  customer_id,
  COUNT(*) AS purchases
FROM analytics.events
WHERE DATE(event_timestamp) BETWEEN '2026-03-01' AND '2026-03-07'
  AND event_name = 'purchase'
GROUP BY customer_id;

A bad query:

SELECT
  customer_id,
  COUNT(*) AS purchases
FROM analytics.events
WHERE event_name = 'purchase'
GROUP BY customer_id;

The second query may scan months or years of data depending on table size.

Use clustering where filters are selective

Clustering is not a substitute for partitioning. It helps when queries frequently filter or aggregate on specific columns within each partition. Good cluster keys are:

• commonly filtered
• reasonably high cardinality
• stable
• used consistently across workloads

Typical examples:

• customer_id
• account_id
• country_code
• event_name

Poor examples:

• free-text columns
• rapidly changing fields
• columns never used in predicates

Stop using SELECT * in production models

This still appears far too often in dbt models, scheduled queries and BI-generated SQL.

If a table has 150 columns and your report needs 6, project 6. Columnar storage helps, but only if you ask for fewer columns.

SELECT
  order_id,
  customer_id,
  order_date,
  net_amount
FROM analytics.fact_orders
WHERE order_date >= '2026-03-01';

Not:

SELECT *
FROM analytics.fact_orders
WHERE order_date >= '2026-03-01';

For wide event tables, this difference is often material.

Materialise expensive transformations once

If the same join and aggregation is executed repeatedly by dashboards, notebooks and downstream models, stop recalculating it.

Options include:

• materialised views
• scheduled aggregate tables
• dbt incremental models
• BI Engine for specific low-latency use cases

For example, a daily aggregate table is often cheaper than letting every dashboard query raw clickstream data.

CREATE OR REPLACE TABLE analytics.daily_customer_revenue
PARTITION BY order_date AS
SELECT
  DATE(order_timestamp) AS order_date,
  customer_id,
  SUM(net_amount) AS revenue
FROM analytics.fact_orders
WHERE order_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 180 DAY)
GROUP BY 1, 2;

If ten dashboards use this table instead of scanning the raw fact table each time, the cost reduction is immediate.

Fix transformation patterns that look incremental but aren’t

A lot of BigQuery bills are inflated by orchestration and modelling layers rather than ad hoc SQL. dbt is a common example. Teams declare models as incremental, but the SQL still forces large scans upstream.

A practical dbt pattern:

{{ config(
    materialized='incremental',
    unique_key='order_id',
    partition_by={
      "field": "order_date",
      "data_type": "date"
    },
    cluster_by=["customer_id"]
) }}

SELECT
  order_id,
  customer_id,
  DATE(order_timestamp) AS order_date,
  net_amount,
  updated_at
FROM {{ source('raw', 'orders') }}
{% if is_incremental() %}
WHERE updated_at >= (
  SELECT COALESCE(MAX(updated_at), TIMESTAMP('1970-01-01'))
  FROM {{ this }}
)
{% endif %}

That’s only useful if:

• updated_at is reliable
• the source table is partitioned or clustered to support that filter
• downstream merges are not rewriting huge partitions unnecessarily

If your incremental model still scans the whole source table every run, it’s not incremental in any meaningful cost sense.

We also regularly see expensive MERGE statements against very large partitioned tables. Sometimes a simpler pattern is cheaper:

• rebuild only recent partitions
• use insert-overwrite by partition
• split hot and cold data
• compact small files or micro-batches into daily partitions

Example of partition-scoped overwrite logic in orchestration:

models:
  marts:
    finance:
      fact_orders:
        +materialized: incremental
        +incremental_strategy: insert_overwrite
        +partition_by:
          field: order_date
          data_type: date
        +partitions:
          - "current_date()"
          - "date_sub(current_date(), interval 1 day)"
          - "date_sub(current_date(), interval 2 day)"

This is often more cost-effective than broad merges across historical data.

BigQuery slot management: where capacity pricing pays off or goes wrong

Once workloads become predictable, BigQuery slot management becomes the main optimisation lever. This is where many organisations overspend because they buy reservations without separating workloads.

If you use capacity pricing, do not put everything into one shared reservation and hope for the best. Split workloads by characteristics:

• production ELT / ETL
• interactive analytics
• data science experimentation
• BI serving
• backfills and one-off engineering jobs

That lets you control both spend and performance.

A simple Terraform example:

resource "google_bigquery_reservation" "prod_etl" {
  name              = "prod-etl"
  location          = "europe-west2"
  slot_capacity     = 500
  edition           = "ENTERPRISE"
  ignore_idle_slots = false
}

resource "google_bigquery_reservation" "bi" {
  name              = "bi"
  location          = "europe-west2"
  slot_capacity     = 200
  edition           = "ENTERPRISE"
  ignore_idle_slots = false
}

resource "google_bigquery_assignment" "prod_etl_assignment" {
  reservation = google_bigquery_reservation.prod_etl.name
  assignee    = "projects/my-prod-project"
  job_type    = "QUERY"
}

resource "google_bigquery_assignment" "bi_assignment" {
  reservation = google_bigquery_reservation.bi.name
  assignee    = "projects/my-bi-project"
  job_type    = "QUERY"
}

A few practical points:

1. Measure concurrency, not just average usage

An average of 300 slots consumed tells you very little if your peak is 2,000 slots at 09:00 when BI dashboards and scheduled transformations collide.

Look at:

• p50 / p95 / p99 slot consumption
• queueing time
• job wait time
• failed or delayed SLAs
• idle reserved capacity

2. Separate noisy workloads

Ad hoc exploration and backfills should not starve production pipelines. If they do, teams usually compensate by buying more slots than they really need.

3. Use autoscaling where it makes sense

If your workloads spike around business hours or batch windows, autoscaling can be more efficient than permanently reserving peak capacity. But only if the baseline and max settings are grounded in actual usage data.

4. Revisit reservations quarterly

A reservation design that was sensible six months ago may be wasteful now. New BI tools, more frequent ingestion, or a change in data model can shift the profile quickly.

If you’re unsure whether reservations are worth it, compare 30-90 days of:

• on-demand bytes processed
• slot-hours consumed
• queueing and latency
• business criticality of workloads

That gives you a commercial and operational basis for the decision, which is far better than guessing.

Don’t ignore storage, retention and table lifecycle

Query compute gets most of the attention, but storage bloat matters too, especially in event-heavy platforms with long retention.

Storage optimisation is less glamorous, but it’s straightforward.

Apply dataset or table expiration where appropriate

Not every staging table needs to exist forever.

ALTER TABLE staging.temp_sessions
SET OPTIONS (
  expiration_timestamp = TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
);

For whole datasets:

ALTER SCHEMA staging
SET OPTIONS (
  default_table_expiration_days = 14
);

This is one of the easiest ways to reduce BigQuery costs in poorly governed environments.

Move users off duplicated tables

A common anti-pattern is every team creating its own copy of the same table because it’s easier than agreeing semantics. That increases storage and often doubles query spend as well.

Prefer:

• curated shared marts
• authorised views
• row/column security where needed
• clear ownership of canonical models

Keep an eye on long-term storage eligibility

BigQuery long-term storage pricing is attractive, but frequent rewrites can keep tables in active storage. If you repeatedly rebuild historical partitions unnecessarily, you may be paying more than expected.

Review external tables carefully

External tables over Cloud Storage can be useful, but for frequently queried data they are often less efficient than loading into native BigQuery storage. If a dataset is queried regularly, benchmark both approaches.

Put guardrails in place so costs do not creep back

One-off tuning helps, but durable BigQuery cost optimisation needs controls.

Set query limits for exploratory work

Custom quotas and sandboxed projects are useful for analyst-heavy environments. They won’t solve poor architecture, but they do stop accidental disasters.

Label jobs and pipelines

If you cannot attribute spend by team, platform component or business function, optimisation becomes political very quickly.

For scheduled jobs, dbt runs and orchestrated pipelines, attach labels consistently.

Python example:

from google.cloud import bigquery

client = bigquery.Client()

job_config = bigquery.QueryJobConfig(
    labels={
        "team": "finance",
        "environment": "prod",
        "pipeline": "daily_margin_model"
    }
)

query = """
SELECT customer_id, SUM(net_amount) AS revenue
FROM analytics.fact_orders
WHERE order_date = CURRENT_DATE()
GROUP BY customer_id
"""

job = client.query(query, job_config=job_config)
job.result()

Monitor cost signals directly

At minimum, track:

• bytes processed per day
• slot-hours consumed per reservation
• top 20 most expensive queries
• storage growth by dataset
• failed partition pruning
• dashboard refresh frequency
• cost by label / team / environment

A lightweight scheduled query for storage monitoring:

SELECT
  table_schema,
  table_name,
  ROUND(total_logical_bytes / POW(1024, 3), 2) AS logical_gib,
  ROUND(total_physical_bytes / POW(1024, 3), 2) AS physical_gib,
  row_count
FROM region-eu.INFORMATION_SCHEMA.TABLE_STORAGE_BY_PROJECT
ORDER BY logical_gib DESC
LIMIT 100;

Add cost checks to code review

This is underrated. We often look for the following in pull requests:

• Is the source partitioned?
• Does the filter hit the partition column?
• Are we selecting only required columns?
• Is this model genuinely incremental?
• Will this join explode row counts?
• Is a materialised aggregate more sensible?
• Does this dashboard really need 5-minute refresh?

This catches a surprising amount of waste before it lands in production.

A practical optimisation checklist

If we were dropped into a BigQuery estate tomorrow to reduce spend quickly, this is the order we’d usually work in:

1. Extract 30-90 days of JOBS and billing metadata
2. Identify top cost drivers by query, user, tool and dataset
3. Fix missing partition filters and broad scans
4. Remove or rewrite repeated expensive transformations
5. Optimise dbt incremental logic and merge strategies
6. Review dashboard refresh cadence and BI query patterns
7. Assess whether on-demand vs reservations still makes sense
8. Redesign reservations and assignments if using capacity pricing
9. Apply retention and expiration to staging and temporary datasets
10. Put monitoring, labels and governance in place

In many estates, the first three steps capture a meaningful portion of the savings. It’s not unusual to find that a small number of queries or scheduled jobs account for a large share of spend.

The important point is this: reducing your BigQuery bill is rarely about one magic feature. It’s about aligning data model design, SQL patterns, orchestration behaviour and commercial model with the way the platform is actually used.

When to Consider Professional Help

If your BigQuery bill is growing faster than your data team can explain, or you’re stuck between on-demand pricing and reservations without clear evidence either way, it’s usually worth bringing in an external view.

We help teams with practical BigQuery cost optimisation work: workload analysis, query tuning, dbt and pipeline optimisation, table design, governance, and BigQuery slot management. As a BigQuery consulting UK partner, we focus on hands-on engineering rather than slide decks — the goal is to reduce waste without breaking delivery or slowing analysts down. If you're not sure whether you need external help, our post on why businesses hire data engineering consultants covers the common signals.

If you want a clear plan to reduce BigQuery costs, review your BigQuery pricing model, or redesign reservations and workload isolation properly, get in touch for a discovery call.

Frequently Asked Questions

What is BigQuery cost optimisation?

BigQuery cost optimisation is the process of reducing spend without harming performance or data quality. It usually involves improving query patterns, storage layout, BigQuery pricing choices, and BigQuery slot management.

How can I reduce BigQuery costs quickly?

Start by identifying the biggest queries, removing unnecessary full-table scans, and ensuring tables are partitioned and clustered properly. Then review scheduled jobs, BI tool queries, and whether your workload is better suited to on-demand or capacity pricing.

Is on-demand or capacity pricing cheaper in BigQuery?

It depends on your workload. On-demand pricing is often better for bursty or unpredictable usage, while capacity pricing can be cheaper for steady, high-volume workloads where BigQuery slot management is easier to control.

Why is my BigQuery bill so high?

The most common causes are large scans, inefficient SQL, poor partitioning, repeated dashboard queries, and unused or over-provisioned slots. Storage growth, streaming inserts, and extra services can also increase BigQuery pricing over time.

How does BigQuery slot management help reduce costs?

BigQuery slot management helps you control compute spend by matching reserved capacity to real workload demand. With the right reservation strategy, you can avoid paying for idle capacity while keeping performance predictable.

Do I need BigQuery consulting UK support to cut costs?

BigQuery consulting UK support can be useful if you need a structured review of billing, workload patterns, and architecture decisions. It is especially valuable when you want to reduce BigQuery costs across multiple teams or move from on-demand to capacity pricing safely.

What are the best ways to optimise BigQuery pricing for BI dashboards?

Use aggregated tables, limit refresh frequency, and make sure dashboards query partitioned data rather than scanning entire tables. Caching, materialised views, and careful BigQuery slot management can also reduce repeated dashboard costs.

When should I review BigQuery costs?

Review BigQuery costs whenever usage changes significantly, such as after a new dashboard rollout, data model change, or growth in query volume. A monthly review is a sensible minimum, but high-traffic environments may need weekly monitoring.