BigQuery Performance and Optimization: Controlling Cost and Speed at the Same Time

BigQuery's serverless model removes cluster management, but it replaces "is my cluster big enough" with a different question: "how much data is this query actually scanning, and does it need to?" That question is where almost all BigQuery optimization work lives.

1. Partitioning is your primary cost lever, not just a performance one

Because BigQuery bills (in on-demand pricing) by bytes scanned, an unpartitioned table means every query touching it scans the whole thing — a cost problem before it's even a speed problem.

SQL
CREATE TABLE sales.orders (
    order_id INT64, customer_id INT64, order_date DATE, amount NUMERIC
)
PARTITION BY order_date
CLUSTER BY customer_id;

• ▸Partition by date at day granularity for most fact tables — this is the single highest-leverage optimization available and costs nothing to implement on a new table.
• ▸Set a partition expiration on tables with a natural retention window (e.g., 90-day rolling data) to control storage cost automatically rather than relying on manual cleanup jobs.
• ▸Always check whether a query's
  Code

  WHERE

  clause actually references the partition column — a filter on a different date-like column that isn't the partition key gets you none of the pruning benefit.

2. Clustering complements partitioning, it doesn't replace it

Clustering (up to 4 columns) sorts data within each partition, letting BigQuery skip blocks within a partition based on additional filter columns:

SQL
CREATE TABLE sales.orders
PARTITION BY order_date
CLUSTER BY customer_id, region;

Order clustering columns from highest to lowest filter/join frequency — the first clustering column gets the strongest pruning benefit.

3. Dry run before you run — this should be a habit, not a tool

Shell
bq query --dry_run --use_legacy_sql=false 'SELECT * FROM sales.orders WHERE order_date = "2026-08-01"'

This returns bytes-that-would-be-scanned without executing the query. For any query going into a scheduled job, dashboard, or shared notebook, dry-running first catches an accidental full-table scan before it costs money — especially valuable for catching a missing partition filter before it ships.

4. SELECT * is expensive in a columnar engine

BigQuery is columnar — it only reads the columns you actually select.

SELECT *

SQL
-- Expensive
SELECT * FROM sales.orders WHERE order_date = '2026-08-01';

-- Cheap
SELECT order_id, customer_id, amount FROM sales.orders WHERE order_date = '2026-08-01';

This sounds obvious written down, but it's the single most common cost issue in inherited dashboards and notebooks — audit for it specifically.

5. Materialized views for repeated aggregation patterns

If the same rollup query runs repeatedly (a dashboard hitting the same aggregation with minor filter changes), a materialized view avoids re-scanning and re-aggregating raw data every time:

SQL
CREATE MATERIALIZED VIEW sales.daily_totals AS
SELECT order_date, SUM(amount) AS total FROM sales.orders GROUP BY order_date;

BigQuery automatically keeps these incrementally updated and will transparently rewrite qualifying queries against the raw table to use the materialized view instead — a meaningful speed and cost win with no query changes required downstream.

6. Slot management: reservations vs on-demand

• ▸On-demand pricing (pay per byte scanned) is simplest for unpredictable, low-to-moderate volume workloads.
• ▸Capacity-based pricing (reservations) makes sense once query volume is high and predictable enough that a fixed slot commitment costs less than metered scanning — and it also protects you from one heavy query starving others, the same problem WLM solves in Redshift.

SQL
SELECT * FROM `region-us`.INFORMATION_SCHEMA.RESERVATIONS_TIMELINE;

Review this regularly if you're on reservations — underutilized slot commitments are wasted spend just as surely as an oversized always-on Redshift cluster is.

7. Avoid unnecessary JOINs on unpartitioned/unclustered large tables

A join between two large, unpartitioned tables forces a full shuffle with no pruning on either side. Where possible:

• ▸Filter each table on its own partition/cluster columns before the join, not after — push predicates down explicitly if the optimizer isn't doing it for you (check with
  Code

  EXPLAIN

  / query execution details in the console).
• ▸For genuinely large-large joins with no natural filter, consider whether a pre-aggregated or materialized intermediate table would reduce the join's actual data volume.

8. Watch INFORMATION_SCHEMA for the real cost drivers

SQL
SELECT user_email, SUM(total_bytes_billed) AS bytes_billed
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
GROUP BY user_email
ORDER BY bytes_billed DESC
LIMIT 20;

Run this monthly. It reliably surfaces the handful of recurring queries or dashboards responsible for the bulk of spend — usually a missing partition filter or a

SELECT *

The pattern across all of this

In BigQuery, performance and cost are almost the same lever — reducing bytes scanned makes queries both faster and cheaper simultaneously. That's different from cluster-based warehouses, where you can sometimes buy speed with more hardware regardless of query efficiency. In BigQuery, there's no equivalent shortcut: partition, cluster, and select only what you need, and the cost problem and the speed problem both get solved at once.