Cutting BigQuery Spend 35% Without Slowing a Single Dashboard

Problem Statement

The client’s BigQuery bill grew from $24K to $96K/month in a little over a year. Growth was real — but an analysis of INFORMATION_SCHEMA.JOBS showed 71% of bytes billed came from just 4% of queries, almost all of them dbt full-table rebuilds and unfiltered Looker explores over a 2.1B-row events table.

Two compounding issues:

1. Everything was a full refresh. The core fct_events model rebuilt the entire table on every run — a 2.1 TB scan, 18 times a day.
2. No governance surface. On-demand pricing, one flat project, no labels. When the bill spiked, nobody could say whose query did it.

System Architecture & Tradeoffs

Three levers, ranked by effort-to-impact:

The deliberate non-goal: no query rewrites in the BI layer until the storage layout was fixed. Optimizing LookML against a badly-clustered table just moves cost around.

Implementation Details

1. Partition + cluster the hot path. Events went from unpartitioned to DATE(event_timestamp) partitions clustered by (account_id, event_type):

CREATE OR REPLACE TABLE raw_events.events
PARTITION BY DATE(event_timestamp)
CLUSTER BY account_id, event_type
AS SELECT * FROM raw_events.events_legacy;

2. Convert the worst offenders to incremental models. The pattern matters more than the tool — merge on the partition key, late-arriving data handled by a lookback window:

{{ config(materialized='incremental', unique_key='event_id',
          partition_by={'field': 'event_date', 'data_type': 'date'},
          cluster_by=['account_id', 'event_type']) }}

select *
from {{ ref('stg_events') }}
{% if is_incremental() %}
where event_date >= date_sub(
  (select max(event_date) from {{ this }}), interval 3 day)
{% endif %}

3. Governance via Terraform-managed slot reservations with project_id + team job labels on every scheduled query, feeding a nightly showback model in dbt.

Business ROI

The engagement paid for itself in 11 days of run-rate savings. More durable than the savings: every scheduled query now carries a cost label, so the next anomaly gets attributed in hours, not quarters.