BigQuery Cost Optimization: 12 Proven Strategies to Cut Your Bills in 2025

BigQuery bills look simple on the surface: $5-6.25 per TB scanned on the on-demand tier, or a flat slot reservation fee. In practice, teams regularly watch their bills spike after a single bad query, a scheduled job that stopped pruning partitions, or an analyst running exploratory queries against a 50 TB table.

We've done BigQuery cost optimization engagements for dozens of clients at Warqline Technologies. The patterns repeat. This guide covers the 12 strategies that consistently move the needle, ordered roughly from highest impact to lowest.

Understanding the architecture helps here — if you haven't read our BigQuery architecture deep dive, start there. The cost levers make much more sense once you know what BigQuery is actually doing.

Strategy 1: Partition and Cluster Every High-Value Table

This is the single highest-ROI change for most teams. A well-partitioned, well-clustered table can cost 10-50x less to query than the same table without those settings.

Partition by your most common time-range filter column (usually a date or timestamp). Cluster by your most common equality filter columns.

-- Before: unpartitioned table, full scan every time
CREATE TABLE `analytics.user_events` AS
SELECT * FROM `analytics.user_events_raw`;
-- Every query scans the entire table

-- After: partitioned and clustered
CREATE TABLE `analytics.user_events_optimized`
PARTITION BY DATE(event_date)
CLUSTER BY user_id, event_type
AS SELECT * FROM `analytics.user_events_raw`;
-- Queries with date + user_id filters scan a tiny fraction

If you have existing unpartitioned tables, you can migrate them:

CREATE TABLE `analytics.user_events_new`
PARTITION BY DATE(event_date)
CLUSTER BY user_id, event_type
AS SELECT * FROM `analytics.user_events`;

-- Verify row counts match
SELECT COUNT(*) FROM `analytics.user_events_new`;

-- Then rename
ALTER TABLE `analytics.user_events` RENAME TO `analytics.user_events_bak`;
ALTER TABLE `analytics.user_events_new` RENAME TO `analytics.user_events`;

For very large tables (multi-TB), consider running this as a scheduled query during off-hours to avoid blocking production.

Strategy 2: Require Partition Filters

Once you've partitioned a table, enforce that queries actually use the partition filter. Without this, a careless SELECT * FROM my_big_table still scans everything.

ALTER TABLE `analytics.user_events`
SET OPTIONS (require_partition_filter = true);

With this setting, any query that doesn't filter on the partition column will fail with an error. This protects against accidental full scans, especially in shared analytics environments where not everyone understands BigQuery's cost model.

The downside: some legitimate use cases (full table exports, backfills) break. Handle those by explicitly adding a partition range filter that covers the full table, or temporarily disable the requirement.

Strategy 3: Use Column Selection Aggressively

BigQuery is columnar — you pay for columns read, not rows read. A table with 100 columns and a billion rows charges 100x more for SELECT * than for SELECT user_id, amount.

This is enforced through code review and tooling, not BigQuery settings. Some teams use a linter that rejects SELECT * in scheduled queries and dbt models.

-- Bad: scans all 80 columns
SELECT * FROM `analytics.orders`
WHERE order_date = '2025-01-15';

-- Good: scans only 3 columns
SELECT order_id, customer_id, amount
FROM `analytics.orders`
WHERE order_date = '2025-01-15';

Run this to audit your most expensive queries by columns scanned:

SELECT
  query,
  total_bytes_processed / POW(1024, 3) AS gb_scanned,
  referenced_tables,
  creation_time
FROM `region-us.INFORMATION_SCHEMA.JOBS`
WHERE DATE(creation_time) >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
  AND statement_type = 'SELECT'
  AND total_bytes_processed > 1e9
ORDER BY total_bytes_processed DESC
LIMIT 50;

This gives you the 50 most expensive queries in the past week. Look for SELECT * patterns and column-heavy queries.

Strategy 4: Materialized Views for Repeated Aggregations

If the same GROUP BY runs hundreds of times a day across different dashboards, scheduling tools, or API calls, a materialized view that precomputes it pays for itself almost immediately.

CREATE MATERIALIZED VIEW `analytics.daily_order_summary`
OPTIONS (enable_refresh = true, refresh_interval_minutes = 60)
AS SELECT
  DATE(order_date) AS day,
  region,
  product_category,
  SUM(amount) AS revenue,
  COUNT(DISTINCT customer_id) AS unique_customers,
  COUNT(*) AS order_count
FROM `analytics.orders`
GROUP BY day, region, product_category;

When a query matches the materialized view pattern, BigQuery rewrites it automatically. A query that previously scanned 500 GB of orders now scans the materialized view — maybe 50 MB. 10,000x cheaper.

Materialized views add storage cost (for the precomputed data) and slot usage (for the periodic refresh). For aggregations that run 100+ times per day against large base tables, the economics are compelling.

Check view utilization to confirm rewriting is happening:

SELECT
  mv.table_name,
  mv.refresh_watermark,
  mv.last_refresh_time
FROM `project.dataset.INFORMATION_SCHEMA.MATERIALIZED_VIEWS` mv
WHERE mv.table_name = 'daily_order_summary';

Strategy 5: Audit and Shut Down Scheduled Queries

Ghost queries — scheduled queries that run hourly or daily and haven't been looked at in two years — are a common cost driver. Someone sets up a BI tool to refresh a dashboard, the dashboard gets abandoned, and the query runs forever.

-- Find all scheduled queries in your project
SELECT
  display_name,
  query,
  schedule,
  next_run_time,
  state
FROM `region-us.INFORMATION_SCHEMA.SCHEDULED_QUERIES`
WHERE state = 'ACTIVE'
ORDER BY next_run_time;

Review every scheduled query. Kill anything that's not referenced by an active dashboard or pipeline. For remaining queries, review for partition pruning and column selection.

Strategy 6: Use Slot Reservations for Predictable Workloads

On-demand pricing charges per TB scanned. For teams with consistent, predictable workloads, slot reservations under BigQuery Editions often cost less.

The math: if you run 100 TB/day at $6.25/TB, that's $625/day = $19,000/month. A 500-slot Enterprise reservation with reasonable utilization might cost $5,000-8,000/month depending on term.

# Create a slot reservation
gcloud bigquery reservations create prod-reservation   --project=my-project   --location=US   --slots=500   --edition=ENTERPRISE

# Create an assignment (links a project to the reservation)
gcloud bigquery reservations assignments create   --reservation=projects/my-project/locations/US/reservations/prod-reservation   --assignee=projects/my-analytics-project   --job-type=QUERY

The break-even point is around 50-100 TB/day depending on your region, slot utilization, and term discount. Run the math for your specific usage before committing.

For larger commitments, see our GCP Committed Use Discounts guide which covers the commitment structures in detail.

Strategy 7: Enable BI Engine for Dashboard Queries

BI Engine is in-memory caching for BigQuery that eliminates per-query scan costs for dashboard queries. Dashboard tools like Looker Studio, Tableau, and Sigma hit the same aggregation patterns repeatedly — BI Engine absorbs those queries entirely.

# Enable 10 GB BI Engine reservation
gcloud bigquery bi-engine update   --reservation-size=10   --location=US   --project=my-project

Once enabled, queries that fit BI Engine's supported syntax (aggregations, filters, window functions on supported data types) are served from memory. The scan charge is zero for cache hits.

For a Looker Studio dashboard that refreshes every 5 minutes with 50 users, BI Engine can reduce scan costs by 90%+. Verify it's actually hitting the cache:

SELECT
  query,
  bi_engine_statistics.bi_engine_mode,
  total_bytes_processed
FROM `region-us.INFORMATION_SCHEMA.JOBS`
WHERE bi_engine_statistics.bi_engine_mode = 'FULL'
  AND DATE(creation_time) = CURRENT_DATE()
LIMIT 20;

FULL mode means 100% cache hit. PARTIAL means part of the query hit cache. DISABLED means BI Engine wasn't used.

Strategy 8: Use Authorized Views for Access Control

Teams sometimes create redundant copies of tables for different teams with different access needs. Authorized views achieve the same access control without duplicating storage.

-- Create a view with restricted columns for external team
CREATE VIEW `analytics.orders_external_view` AS
SELECT order_id, product_id, region, DATE(order_date) AS order_date, amount
FROM `analytics.orders`;

-- Authorize it to access the base table without granting access to the base table
GRANT `roles/bigquery.dataViewer` ON TABLE `analytics.orders_external_view`
  TO 'group:external-analysts@company.com';

One copy of the data, multiple views with different column/row access. No storage duplication.

Strategy 9: Archive Old Data to Lower Storage Tiers

BigQuery has two storage classes: active storage and long-term storage. Tables not modified in 90+ days automatically move to long-term storage at half the price.

The key word is "modified" — if you append to a table or run DML on it, the 90-day clock resets. Partition-level modifications only affect the modified partition.

For tables older than 90 days that you never touch, long-term storage kicks in automatically. For tables you periodically touch, consider a more aggressive archiving strategy:

-- Move old partitions to a separate archive table
INSERT INTO `analytics.orders_archive`
SELECT * FROM `analytics.orders`
WHERE DATE(order_date) < DATE_SUB(CURRENT_DATE(), INTERVAL 2 YEAR);

-- Then delete from the main table
DELETE FROM `analytics.orders`
WHERE DATE(order_date) < DATE_SUB(CURRENT_DATE(), INTERVAL 2 YEAR);

Archive tables are rarely queried and will move to long-term storage quickly.

Strategy 10: Use LIMIT Thoughtfully

LIMIT does NOT reduce scan cost in BigQuery. This trips up everyone from traditional SQL backgrounds.

-- BAD: scans the entire 10 TB table even though you only want 100 rows
SELECT * FROM `analytics.events` LIMIT 100;

-- Good: scan a single partition first, then limit
SELECT * FROM `analytics.events`
WHERE DATE(event_date) = '2025-01-15'
LIMIT 100;

BigQuery executes the full query and returns only 100 rows, but the scan happens on all data that matches the WHERE clause (or everything if there's no WHERE clause). Partition filters are how you reduce scan cost — LIMIT only reduces data transfer back to the client.

Strategy 11: Monitor Bytes Scanned with Alerts

Set up a billing alert that fires when daily BigQuery costs exceed a threshold. Also set up a query-level alert that notifies you when a single query scans more than a threshold.

# Create a budget alert for BigQuery spending
gcloud billing budgets create   --billing-account=BILLING_ACCOUNT_ID   --display-name="BigQuery Daily Alert"   --budget-amount=500   --threshold-rules-percent=0.5,0.9,1.0   --filter-services=bigquery.googleapis.com

For per-query monitoring, use Cloud Logging or INFORMATION_SCHEMA:

-- Run as a daily audit job
SELECT
  user_email,
  query,
  total_bytes_processed / POW(1024, 4) AS tb_scanned,
  total_bytes_processed / POW(1024, 4) * 6.25 AS estimated_cost_usd,
  creation_time
FROM `region-us.INFORMATION_SCHEMA.JOBS`
WHERE DATE(creation_time) = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
  AND total_bytes_processed > 500e9  -- over 500 GB
ORDER BY total_bytes_processed DESC;

Email this to your team every morning. When someone accidentally scanned 5 TB with a bad query, they find out the next day rather than at billing time.

Strategy 12: Optimize Joins with Table Order and Filter Pushdown

BigQuery's query optimizer does most of this automatically, but complex multi-join queries benefit from help.

Put the largest table first in the FROM clause (though the optimizer usually reorders anyway). Filter aggressively before joining:

-- Less efficient: join then filter
SELECT o.order_id, u.name, o.amount
FROM `orders` o
JOIN `users` u ON o.user_id = u.id
WHERE o.amount > 1000
  AND u.country = 'NL';

-- More efficient: filter then join
WITH large_orders AS (
  SELECT order_id, user_id, amount FROM `orders` WHERE amount > 1000
),
dutch_users AS (
  SELECT id, name FROM `users` WHERE country = 'NL'
)
SELECT lo.order_id, du.name, lo.amount
FROM large_orders lo
JOIN dutch_users du ON lo.user_id = du.id;

The optimizer often handles this transformation, but making it explicit ensures it happens and makes the intent clear.

Putting It Together: The Monthly Optimization Routine

We suggest a monthly routine:

1. Review the top 50 most expensive queries from INFORMATION_SCHEMA. Look for missing partition filters, SELECT *, and redundant self-joins.
2. Audit scheduled queries. Kill unused ones. Optimize the expensive remaining ones.
3. Check materialized view utilization. Are they being used? Are they fresh enough?
4. Review BI Engine cache hit rate. If it's below 70%, investigate why.
5. Compare actual slot utilization to reservation size (if using reservations). Too much slack means you bought too many slots; near-100% utilization means you're leaving performance on the table.
6. Check for orphaned datasets. Datasets no one has queried in 6+ months can often be deleted or archived to GCS.

The average client we work with cuts BigQuery spend 40-60% in the first month of applying these strategies. The highest-impact changes are nearly always the first three: partition + cluster your biggest tables, require partition filters, eliminate SELECT *. Everything else is incremental on top of that foundation.

For real-time streaming patterns that work efficiently with partitioned tables, see our BigQuery real-time streaming guide. For the ML angle on BigQuery, see our BigQuery ML guide.