Google BigQuery Cost Optimization: Partitioning, Clustering, and Query Best Practices

Introduction

Google BigQuery's serverless on-demand pricing ($6.25 per TB scanned) provides immense convenience, but unoptimized queries on multi-terabyte datasets can quickly generate unexpected monthly cloud bills. By implementing modern schema architecture, partitioning strategies, and query constraints, engineering teams frequently reduce their BigQuery expenditure by 60% to 90% without sacrificing query execution speed.

1. The Foundation: Table Partitioning

A non-partitioned table scans every single column across the entire dataset during every query execution. Table partitioning divides a massive table into discrete segments based on a date, timestamp, or integer range:

-- Creating a Date-Partitioned Table in BigQuery
CREATE TABLE `enterprise_dw.events_partitioned` (
  event_id STRING,
  user_id STRING,
  event_name STRING,
  event_timestamp TIMESTAMP,
  event_value NUMERIC
)
PARTITION BY DATE(event_timestamp)
OPTIONS (
  partition_expiration_days = 365,
  require_partition_filter = true
);

Notice the require_partition_filter = true setting. This critical safeguard guarantees that no developer or BI tool can accidentally execute a full-table scan without specifying a date partition filter (e.g., WHERE DATE(event_timestamp) >= '2026-10-01'), permanently preventing run-away billing spikes.

2. Table Clustering for Multi-Column Filtering

While partitioning organizes data by time, clustering sorts and groups related records based on up to four high-cardinality attributes (e.g., customer ID, country, event type):

-- Creating a Partitioned AND Clustered Table
CREATE TABLE `enterprise_dw.user_activity_optimized` (
  user_id STRING,
  country_code STRING,
  activity_type STRING,
  occurred_at TIMESTAMP,
  metadata JSON
)
PARTITION BY DATE(occurred_at)
CLUSTER BY country_code, activity_type;

When filtering by clustered columns, BigQuery uses block pruning to skip irrelevant storage blocks entirely, reducing data scanned from gigabytes to mere megabytes.

3. Ten SQL Anti-Patterns to Eliminate Immediately

1. Avoid SELECT *: BigQuery is a columnar database. Querying SELECT * on a 50-column table incurs 50x the cost of querying only the single column you need. Always project explicit column lists.
2. Limit Does Not Reduce Scan Cost: Writing SELECT * FROM table LIMIT 10 still scans the entire underlying table before returning 10 rows. LIMIT controls returned rows, not scanned bytes.
3. Filter Before Joining: Use Common Table Expressions (CTEs) or subqueries with tight WHERE clauses before performing heavy table joins.
4. Use APPROX_COUNT_DISTINCT(): Exact COUNT(DISTINCT user_id) requires extensive cross-node data shuffling. For dashboards and exploratory analysis, APPROX_COUNT_DISTINCT() runs within 1% accuracy at a fraction of compute cost.

4. Setting Up BigQuery Project-Level Cost Caps

Protect your organization against rogue queries by configuring custom query quotas in Google Cloud IAM & Quotas:

• Set a Query usage per user per day cap (e.g., maximum 5 TB per developer per day).
• Configure Cloud Monitoring alerts at 50%, 80%, and 100% of your planned monthly budget threshold.