BigQuery doesn't get expensive by accident. It gets expensive through a handful of predictable patterns: full-table scans answering questions about last week, scheduled queries nobody remembers scheduling, dashboards rebuilding the world every morning, and a pricing model chosen once and never revisited.
The good news is that the same predictability works in your favor. In our audits, we find an average of 27% recoverable spend — and structural optimization work typically cuts bills 30–60%, measured on the client's own billing before and after. None of it requires migrating off BigQuery. It requires knowing where to look. (If you'd rather have us look, that's our BigQuery cost optimization service — but everything below is DIY-able.)
This guide covers the 12 tactics that account for most of that reduction, in rough order of effort-to-impact. For each one: what it fixes, how the math works, and what to watch out for. Everything here applies whether your BI layer is Looker, Tableau, Power BI, or custom code — though we'll flag the Looker-specific angles, because that's where we live as a Data & Analytics practice.
One piece of context before the tactics. BigQuery charges you on two meters:
- Compute — either bytes processed (on-demand pricing, $6.25 per TiB scanned in the US region at the time of writing, with the first TiB free each month) or slot capacity (BigQuery editions, billed per slot-hour).
- Storage — logical or physical bytes stored, with an automatic discount for data untouched for 90 days.
Every tactic below reduces one of those two meters. Keep that model in mind and the whole discipline stops being mysterious.
1. Partition your tables
What it fixes: the single biggest source of waste — queries that scan an entire table to answer a question about a slice of it.
An unpartitioned 2 TB events table costs the same to query whether you ask about yesterday or about the last three years: the full 2 TB, every time, roughly $12.50 per query on-demand. Partition that table by date and a "yesterday" query scans only yesterday's partition — often a 100x–1000x reduction in bytes processed for time-bounded questions, which is most dashboard questions.
How to do it: partition large fact tables by ingestion date or by a DATE/TIMESTAMP column that queries actually filter on. Then make sure the filter reaches the partition column — a filter on a derived expression (CAST, date functions applied to the column) can silently disable pruning. In Looker, add an always_filter or templated filter on the partition column so users can't accidentally query all of history.
Watch out for: partitioning by a column nobody filters on. Pruning only happens when the WHERE clause hits the partition column directly. Check your real query patterns in INFORMATION_SCHEMA.JOBS before choosing.
2. Cluster the tables you partition
What it fixes: scans within a partition that still read far more than they need.
Clustering physically co-locates rows by up to four columns — customer_id, country, event_type, whatever your queries filter and aggregate by. BigQuery then skips blocks that can't contain matching rows. On high-cardinality filter columns, clustering routinely cuts scanned bytes by another 30–90% on top of partition pruning.
How to do it: cluster by the columns that appear most often in WHERE clauses and JOIN keys, ordered from most-filtered to least. Partitioning plus clustering is the default posture for any fact table over ~1 GB. Clustering costs nothing extra — there is no reason a large, frequently-filtered table shouldn't have it.
Watch out for: clustering benefits are best-effort, not guaranteed, and they decay as data is written; BigQuery re-clusters automatically in the background at no charge. Also: on-demand cost estimates (the green checkmark) show the worst case for clustered tables — actual billed bytes are often lower.
3. Kill SELECT *
What it fixes: paying for columns nobody reads.
BigQuery is columnar: you pay for the columns you touch, not the rows you return. SELECT * on a 40-column table where the dashboard uses 6 columns pays roughly 7x more than it should — and LIMIT 10 doesn't help, because the limit applies after the scan.
How to do it: name your columns. In exploration, use the free table preview instead of SELECT * LIMIT 100. In Looker, audit views built with SELECT * in derived tables — they inherit every column forever, including the ones added later. For genuinely wide tables where analysts need flexibility, SELECT * EXCEPT(...) at least trims the known-heavy columns.
Watch out for: BI tools generating the SQL for you. Looker only selects the fields used in the query, which is one of its quiet cost advantages — but hand-written derived tables and custom SQL blocks bypass that protection.
4. Audit your scheduled queries and dead pipelines
What it fixes: compute spent on outputs nobody consumes.
Every environment we audit has them: scheduled queries feeding a table that feeds a dashboard that was replaced eight months ago; hourly refreshes for a report read once a week; two pipelines computing nearly identical tables for two teams that never compared notes.
How to do it: list every scheduled query, materialized view refresh, and orchestrated job, then trace each one to a living consumer. In Looker, cross-reference System Activity: dashboards and Looks with zero views in 90 days, and the PDTs that exist only to serve them. Turn off what's dead. This is the cheapest tactic on the list — pure deletion, visible in the next billing cycle.
Watch out for: quarterly and year-end consumers. Before deleting, downgrade frequency first (hourly → daily → weekly), then remove after a safe window.
5. Choose the right pricing model — and it's often a hybrid
What it fixes: paying on-demand rates for predictable workloads, or paying for reserved capacity you don't use.
On-demand pricing charges per TiB scanned. BigQuery editions charge per slot-hour, with autoscaling and optional baselines. The crossover math is workload-specific, but the principle is simple: predictable, high-volume compute belongs on capacity; spiky, low-volume compute belongs on-demand.
How to do it: pull 90 days of INFORMATION_SCHEMA.JOBS and compute what your actual usage would have cost under each model. Many teams land on a hybrid — reservations for the ELT and BI projects that hum all day, on-demand for ad-hoc analysis projects. Assign projects to reservations deliberately instead of letting everything inherit one default.
Watch out for: buying a baseline sized for peak load. Autoscaling exists so you don't have to. And revisit the decision quarterly — workloads drift, and the pricing model that was right at 2 TB/day may be wrong at 10.
6. Set cost guardrails before you need them
What it fixes: the single bad query that becomes a bad month.
One analyst joining two unpartitioned tables can scan hundreds of terabytes in an afternoon. On-demand, that's a four-figure surprise with zero alerts — unless you configured them.
How to do it: three layers. First, maximum_bytes_billed on queries (settable per query, and in Looker per connection) — a query that would exceed the cap fails instead of billing. Second, custom quotas per project and per user for daily bytes scanned. Third, billing alerts and a budget on the GCP project. Guardrails cost nothing and convert catastrophic surprises into a polite error message.
Watch out for: setting caps so tight that legitimate workloads fail silently in schedules. Start generous, tighten with data.
7. Materialize expensive logic — once
What it fixes: the same expensive transformation computed dozens of times a day.
If five dashboards each run a query that aggregates the raw events table, you're paying for the same aggregation five times per refresh cycle. Materialization moves that cost to once per cycle.
How to do it: three tools, in order of preference. Materialized views for straightforward aggregations — BigQuery keeps them incrementally fresh and can rewrite queries to use them automatically. Incremental PDTs or dbt incremental models for complex logic — process only new data instead of rebuilding history every time. Plain scheduled tables where neither fits. The keyword is incremental: a PDT that rebuilds three years of history nightly to add one day of data is the pattern we fix most often in Looker environments.
Watch out for: redundant materialization — multiple PDTs computing overlapping data on different schedules. Consolidate before you optimize; deduplication is often worth more than tuning.
8. Use aggregate awareness in Looker
What it fixes: dashboard queries scanning detail-level data to answer summary-level questions.
An executive dashboard showing monthly revenue by region does not need to touch billions of raw rows. With Looker's aggregate_table parameter, you define rollups once and Looker transparently routes queries to the smallest table that can answer them — users notice nothing except speed.
How to do it: find your highest-traffic Explores in System Activity, identify the common grain of their dashboard queries (usually day/week/month by a few dimensions), and define aggregate tables at that grain. This is the highest-leverage Looker-specific tactic: it cuts bytes scanned and load times with zero changes to dashboards. It's also the heart of our Looker performance optimization work — slow and expensive are usually the same problem, as we unpack in why your Looker dashboards are slow.
Watch out for: aggregates that never match because queries include one extra dimension. Design rollups against real query patterns, not assumptions.
9. Exploit caching at every layer
What it fixes: paying to run the same query twice.
BigQuery caches query results for 24 hours — identical queries return free. Looker adds its own cache layer governed by datagroups. Between them, a well-configured stack answers most repeat questions without touching the warehouse.
How to do it: in Looker, define datagroups that match how often the underlying data actually changes — if your ELT lands at 6 a.m., a sql_trigger on the load timestamp beats a blanket "cache for 1 hour" policy that re-queries static data 24 times a day. Check that dashboards aren't set to ignore cache, and be deliberate about auto-refresh tiles: a dashboard on a TV refreshing every 5 minutes runs ~288 query sets a day.
Watch out for: non-deterministic SQL (CURRENT_TIMESTAMP(), etc.) silently disabling BigQuery's result cache.
10. Right-size your storage
What it fixes: the quieter half of the bill — storage that grows forever because deleting is nobody's job.
BigQuery automatically halves the storage rate for data untouched for 90 days (long-term storage). You can help it along: set table expiration on staging datasets and scratch tables so temporary data actually expires; delete the _backup_final_v2 tables; and evaluate physical storage billing per dataset — for datasets that compress well, switching from logical to physical billing can cut storage costs significantly, sometimes dramatically for repetitive data like logs and events.
How to do it: query INFORMATION_SCHEMA.TABLE_STORAGE to rank datasets by cost under both billing models, flip the ones where physical wins, and set default expirations on every dev and staging dataset.
Watch out for: physical billing charges for time-travel storage, so datasets with heavy churn (frequent deletes/updates) may be cheaper on logical. Run the comparison per dataset — it's one query.
11. Turn on BI Engine where the workload fits
What it fixes: repeated small dashboard queries that are individually cheap but collectively expensive — and slow.
BI Engine is an in-memory acceleration layer: you reserve capacity, it caches hot data, and qualifying dashboard queries run sub-second without billing bytes scanned.
How to do it: it pays off for high-concurrency dashboard workloads on a bounded set of tables — the classic Looker or Looker Studio pattern. Start with a small reservation, use the preferred-tables setting to focus it, and measure the offset: reservation cost versus on-demand scan cost avoided plus the latency win.
Watch out for: treating it as a blanket fix. Queries that don't qualify (certain joins, large results) fall back to normal execution and normal billing. Measure before scaling the reservation.
12. Build cost attribution — so the savings stick
What it fixes: the organizational problem underneath all the technical ones: nobody can say which team, dashboard, or query drives the bill, so nobody owns it.
How to do it: INFORMATION_SCHEMA.JOBS contains every job with its bytes billed, user, project, and labels. Build a small monitoring layer on top — a scheduled aggregation plus a dashboard showing spend by project, user, and query pattern, with alerts when a query or user breaks their historical baseline. Label your workloads (team, dashboard, pipeline) so attribution is automatic. In Looker environments, join against System Activity to attribute cost per dashboard — which is the number that finally gets a slow, expensive dashboard deprecated. This kind of governed attribution is bread-and-butter Looker consulting work.
Watch out for: building the dashboard and never assigning an owner. Attribution without accountability decays in a quarter. The teams that keep their savings review the cost dashboard on a cadence, the same way they review uptime.
The order of operations
If you're starting from zero, this sequence front-loads the impact:
- Week 1 — visibility and guardrails: cost attribution queries (tactic 12),
maximum_bytes_billedand quotas (6), kill dead scheduled jobs (4). No structural risk, immediate effect. - Weeks 2–4 — structure: partition and cluster the heavy tables (1, 2), fix the
SELECT *offenders (3), consolidate and make materialization incremental (7). - Weeks 4–8 — strategy: pricing-model analysis (5), aggregate awareness (8), caching review (9), storage billing per dataset (10), BI Engine trial (11).
That sequence is, not coincidentally, roughly how we run our own engagements — quick wins visible in the next billing cycle, structural work landing its full impact within 6–8 weeks. The 30–60% number comes from doing all of it, measured against your baseline bill, not from any single silver bullet. For a worked example of the whole sequence in production, see our performance and cost optimization case study.
Where to start if you want a second pair of eyes
You can run this entire playbook in-house — that's why we wrote it down (and why we also published the full Looker audit checklist). If you'd rather start with your specific number: our free check takes 15 minutes with read-only access. We look at your billing and query patterns and name the estimated recoverable spend. If there's nothing worth fixing, we tell you that — audits find 27% waste on average, but "your setup is fine" is a real possible outcome, and hearing it costs you nothing.
Frequently asked questions about BigQuery costs
How much can I realistically reduce my BigQuery costs?
In our engagements, structural optimization typically cuts BigQuery bills 30–60%, measured on actual billing before and after. Our audits find 27% recoverable spend on average. The exact number depends on your starting point: environments with unpartitioned tables, no slot strategy, and unmonitored scheduled queries sit at the high end of the range; already-tuned environments at the low end.
What is the single biggest driver of high BigQuery costs?
Full-table scans — queries that read an entire table to answer a question about a small slice of it, usually because the table isn't partitioned or the filter doesn't reach the partition column. Partitioning plus clustering on large, frequently-queried tables typically delivers the largest single reduction in bytes processed.
Should I use on-demand or capacity (slot) pricing for BigQuery?
It depends on your workload pattern. Predictable, high-volume workloads (ELT, BI refreshes) usually cost less on reserved capacity; spiky or low-volume workloads are usually cheaper on-demand. Most environments we audit end up on a hybrid: reservations for steady workloads, on-demand for ad-hoc analysis. The decision should be made from your actual usage history in INFORMATION_SCHEMA.JOBS, and revisited quarterly.
Does partitioning or clustering cost extra in BigQuery?
No. Partitioning and clustering are free features — you pay the same storage rate, and BigQuery re-clusters tables automatically in the background at no charge. Their entire effect is reducing the bytes each query scans, which directly reduces on-demand costs and frees slot capacity under editions pricing.
Why is my BigQuery bill high even though my queries return few rows?
Because BigQuery bills bytes scanned, not rows returned. A SELECT * ... LIMIT 10 on a large table scans the full table (all selected columns, all relevant partitions) before applying the limit. The fixes are selecting only the columns you need, filtering on partition columns, and using table preview instead of SELECT for inspection.
How do I stop a single bad query from causing a huge BigQuery bill?
Three guardrails: set maximum_bytes_billed so oversized queries fail instead of billing (Looker supports this per connection); set custom quotas for daily bytes scanned per user and per project; and configure billing budgets and alerts on the GCP project. All three are free and take under an hour to set up.
Do these tactics apply if we use Tableau or Power BI instead of Looker?
Yes. Everything on the warehouse side — partitioning, clustering, pricing model, guardrails, materialization, storage tiers, cost attribution — applies to any BI tool on top of BigQuery. The Looker-specific tactics (aggregate awareness, datagroups, PDT strategy) have equivalents in other tools: extracts and refresh schedules in Tableau, import mode and aggregations in Power BI.
How long does it take to see BigQuery cost savings?
Quick wins — deleting dead scheduled queries, fixing misconfigured queries, adding guardrails — show up in your next billing cycle. Structural changes like partitioning, materialization redesign, and slot reservations land their full impact within 6 to 8 weeks. We put that timeline in the SOW as a commitment when we run the work.
What does a professional BigQuery cost audit include and cost?
Our free 15-minute check is read-only: we look at billing and query patterns and name the estimated recoverable spend. The full scoped audit is $1,500 fixed, delivered in one week: a complete FinOps review of queries, jobs, slots, and storage, with a prioritized savings plan and estimated impact per finding. Ongoing FinOps support starts at $2,000/month. If the free check finds nothing worth fixing, we tell you and you owe nothing.
Want your specific number?
Our free cost check takes 15 minutes with read-only access. We look at your billing and query patterns and name the estimated recoverable spend — before you spend anything. If your setup is fine, we tell you that too.
