BigQuery Cost Control That Scales: 12 Patterns from Real Warehouses

BigQuery bills you on two axes: storage (cheap, split into active vs long-term) and compute — either on-demand ($/TB scanned) or slot-based reservations (BigQuery editions). Everything in this playbook maps to one of those two levers.

Before optimizing anything, spend one hour with INFORMATION_SCHEMA.JOBS_BY_PROJECT and your billing export. You'll almost always find that the top 10 queries account for 60–80% of spend. Fix those first; the long tail is noise.

1. Partition every fact table on ingestion timestamp or event date. Turn on require_partition_filter=true so accidental full scans fail loudly instead of silently costing $2k.
2. Cluster on the 1–4 columns you filter or join on most. Zero query changes, up to 10× cheaper scans.
3. Kill SELECT * — enforce with a linter in CI. Projection is the single biggest lever on wide event tables.
4. Approximate aggregates (APPROX_COUNT_DISTINCT, APPROX_QUANTILES) for dashboards. 10–100× cheaper, error < 1%.
5. Materialized views for the top 20 dashboard queries. Auto-refresh, transparent rewrites when the optimizer sees a match.
6. BI Engine reservation for Looker Studio dashboards — sub-second queries, flat monthly cost, no per-scan surprises.
7. Table sampling during development: TABLESAMPLE SYSTEM (1 PERCENT). Saves you and the shared reservation.
8. Session / temp tables for exploratory work — dropped automatically, no long-term storage bill.
9. Search indexes for needle-in-haystack log queries. 20× faster than LIKE '%foo%' on TB-scale logs.
10. Right-size reservations — start with editions autoscaling, then buy 1-year commitments once demand stabilizes.
11. Cross-region audits — egress between multi-region datasets is a silent killer. Co-locate storage with compute.
12. Query labels + billing export — attribute every dollar to a team, workflow, or dashboard. Without labels, cost control is guesswork.

Partitioning splits a table into daily (or hourly) chunks; clustering physically sorts within each partition. Together they let the optimizer skip 99% of the table for typical queries.

create table analytics.events (
  event_id string,
  user_id string,
  event_type string,
  country string,
  event_ts timestamp,
  payload json
)
partition by date(event_ts)
cluster by event_type, country, user_id
options (
  partition_expiration_days = 400,
  require_partition_filter = true
);

Rules of thumb:

• Partition on the column you filter by 90% of the time (usually event date).
• Cluster keys go from most-selective to least. Don't cluster on high-cardinality free text.
• Set partition_expiration_days to auto-drop cold partitions if the business allows.
• Turn on require_partition_filter — you'll catch bad queries at development time, not at end-of-month.

-- ❌ full scan, no filter, no projection
select * from events;

-- ❌ regex on unindexed text against TBs of logs
select * from logs where regexp_contains(message, r'timeout');

-- ❌ cross-join on a wide table
select a.*, b.* from big_a a cross join big_b b;

-- ❌ CURRENT_TIMESTAMP() in a WHERE that defeats partition pruning
select * from events where event_ts > current_timestamp() - interval 1 day;

-- ✅ scoped, projected, partition-aware
select event_id, user_id, event_type
from events
where event_date between date_sub(current_date(), interval 7 day)
                      and current_date()
  and event_type in ('signup','purchase');

The current_timestamp() anti-pattern is subtle: BigQuery can't prune partitions on non-constant expressions in some versions of the optimizer. Cast to current_date() or use a scripting variable computed once.

Once you know your top 20 dashboard queries (from INFORMATION_SCHEMA), turn each into a materialized view. The optimizer transparently rewrites qualifying queries to hit the MV instead of the base table.

create materialized view analytics.daily_active_users
partition by activity_date
cluster by country
as
select
  date(event_ts) as activity_date,
  country,
  approx_count_distinct(user_id) as dau
from analytics.events
where event_type in ('login','session_start')
group by 1, 2;

Layer BI Engine on top for Looker Studio. It caches hot query results in memory; sub-second latency, and the monthly reservation is often cheaper than the on-demand cost it replaces.

Rule of thumb: switch to editions once you can predict monthly TB scanned within ±30%. Start with a small baseline + generous max slots, then trim after two billing cycles.

1. Enable the billing export to BigQuery.
2. Tag every query with labels: team, workflow, env, dashboard. dbt, Airflow, and n8n all support labels on the query API.
3. Build a single materialized view that joins INFORMATION_SCHEMA.JOBS_BY_PROJECT with billing on job ID.
4. Pipe it into Looker Studio with filters by team + dashboard. Ship a weekly digest to owners.
5. Set a per-team budget alert at 80% of last month's baseline.

select
  labels.value as team,
  date(creation_time) as day,
  sum(total_bytes_billed) / pow(1024,4) as tb_billed,
  sum(total_slot_ms) / 1000 / 3600 as slot_hours,
  count(*) as jobs
from `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT j,
     unnest(labels) as labels
where labels.key = 'team'
  and creation_time > timestamp_sub(current_timestamp(), interval 30 day)
group by 1, 2
order by 2 desc, 3 desc;

Cost control decays the moment attention moves elsewhere. Institutionalize a 30-minute weekly ritual:

1. Top 10 by cost — the biggest queries of the week. Owner named. Fix or accept.
2. Top 10 by growth — queries whose cost grew >50% week-over-week. Usually a new dashboard or a bad JOIN.
3. Unlabeled spend — anything >$50 without labels. Chase the owner and label it.
4. Idle reservations — reservations under 40% utilization for two weeks are ready to shrink.
5. New MV candidates — repeated identical query shape from >3 sources? Materialize it.

Track the exact same five items every week for a quarter. The graph of savings vs baseline is the artifact you show leadership.

Cost control on the warehouse is only half the picture. Pair it with the same discipline on your AI workloads and pipelines: