BigQuery cost spikes usually come from table shape, not queries

When BigQuery spend jumps, inspect the repeated work the platform has normalized before hunting for one embarrassing query.

A single statement can waste money. Persistent cost usually comes from a shape that makes many reasonable statements expensive: a wide table, unclear grain, weak partition contract, repeated joins, full-history rebuilds, or an incremental model that buys confidence by reprocessing far more data than changed.

The invoice is often the first place the architecture admits this.

Table shape becomes query cost

A table is both a semantic interface and a scan interface.

If consumers need five columns from a table carrying a hundred, columnar storage limits some waste, but it doesn’t repair a model whose grain forces repeated joins and aggregations. If business attributes are duplicated across several unstable surfaces, every consumer reconstructs the same meaning. If reporting reads transformation-oriented tables directly, ordinary dashboard interactions inherit pipeline complexity.

Start with the questions the table is meant to answer. Give it one declared grain, the identifiers needed at that grain, and the measures and dimensions that belong there. Split volatile payloads and rarely used detail when doing so creates a cleaner access path. Build serving models for repeated reads instead of asking every query to rediscover the same boundary.

This is why modeling around decision boundaries matters to cost. A useful business surface removes repeated work as well as ambiguity.

Incremental uncertainty is expensive

An incremental model saves compute only when it can identify affected output precisely enough to trust the result.

When change detection is vague, teams compensate by widening lookback windows, rewriting more partitions, or periodically rebuilding everything. Each decision can be rational in isolation. Together they turn an “incremental” pipeline into a recurring full-history insurance policy.

Measure the ratio between source change and output rewritten. A small number of changed business entities causing dozens of partitions to rebuild is a design signal. So is a lookback window that keeps expanding because nobody can state the maximum lateness or dependency propagation.

Make change detection explicit. Track source update timestamps, affected keys, changed child entities, or another signal appropriate to the model. Then define how those changes map to output partitions and unique keys. Incremental models should describe their correction path, not merely their fast path.

Stale-row prevention needs a bounded mechanism

Deletes, status changes, and moved relationships create stale output when append-only logic no longer matches source behavior. The usual reaction is a larger rebuild.

A better design names the affected keys and reconciles them. That may mean a MERGE, partition replacement, a changed-key table, or a periodic bounded rebuild whose scope follows measured lateness. The mechanism depends on the model. The requirement is that correctness doesn’t rely on routinely scanning history without knowing why.

If the only trustworthy recovery is “rebuild everything,” the cost spike is a correctness symptom.

Orchestration can multiply the same work

Schedulers make duplication easy to hide. Two branches build nearly identical intermediate data. A permanent backfill path rereads history beside the normal model. Cleanup SQL runs after every task because the transformation itself doesn’t own its final state.

Keep data logic in reviewable models and orchestration focused on ordering, retries, parameters, and external side effects. Thin orchestration makes it easier to see when the same bytes are being processed twice for no business reason.

Diagnose by mechanism

When spend changes materially, compare the current period with a known baseline:

• which tables and partitions were scanned more often
• which scheduled models increased bytes processed
• which dashboards increased query fan-out
• which jobs changed from bounded incrementals to wide rebuilds
• which table schemas or grains forced new joins
• which backfills or retries duplicated normal work

Tune individual SQL after those checks. Query optimization is useful, but it shouldn’t become a ritual that leaves the production shape intact.

BigQuery bills for the work the platform asks it to repeat. The durable cost control is to make that repeated work match the actual business change.