Precompute ladder: cache -> scheduled tables -> MVs -> extracts

Precompute when a read pattern becomes more stable than its freshness requirement.

The mechanism comes second. First decide how stale the result may be, how often it’s read, which interactions must remain dynamic, and who will own another stored representation. Then choose the least complicated rung that meets that contract.

Result cache is free help, not architecture

BigQuery’s result cache helps when an identical eligible query repeats. It requires almost no ownership and can eliminate work entirely.

It’s also easy for BI tools to miss through changed query text, parameters, destination behavior, or underlying-table updates. Treat a hit as upside. Don’t set a dashboard latency target that depends on it unless the actual generated workload proves the cache is reliable enough.

When the cache misses matter, move deliberately rather than trying to make every consumer emit identical SQL forever.

Scheduled tables are the SME default

A scheduled or orchestrated table is usually the clearest answer for a recurring aggregation with an hourly or daily freshness budget.

The refresh is explicit. The SQL can use the same review and testing path as other models. Cost moves to a predictable job. Consumers read a narrow serving table instead of rebuilding the same joins and metrics.

Use this rung when:

• the query shape is stable
• refresh can happen on a cadence
• replacement or incremental semantics are easy to explain
• several consumers need the same result

The extra storage is often trivial compared with repeated live compute and human time spent debugging variable dashboard behavior.

Materialized views fit eligible workloads

A materialized view can maintain eligible results incrementally and keep the consumer close to the base tables. It’s attractive when the supported SQL shape matches the problem naturally.

Don’t contort the model to qualify. If joins, transformations, freshness, or maintenance behavior make the view difficult to reason about, a scheduled table is usually the better operational choice. Cleverer maintenance isn’t inherently cheaper once exceptions and fallback queries enter the picture.

Validate actual query rewrite and refresh behavior. Declaring a materialized view doesn’t prove consumers are benefiting from it.

Extracts trade lineage for isolation and speed

A BI extract copies data into the reporting tool or its serving layer. It can produce fast, predictable interactions and remove dashboard traffic from BigQuery.

The cost is another refresh process, duplicated storage, tool-specific semantics, and a weaker path from visible number to warehouse model. That trade is acceptable for a stable executive dashboard or high-traffic report when the extract remains a projection of governed upstream data.

It’s dangerous when business logic moves into the extract and the copy becomes an unreviewed source of truth.

BI Engine is adjacent, not another rung

BI Engine accelerates eligible live queries. It doesn’t change their freshness model or remove repeated execution. Use it when the workload deserves to remain live and already has a sane serving shape.

A dashboard can combine mechanisms: scheduled serving tables for heavy shared logic, BI Engine for the interactive slice, and cache where it happens to hit. What matters is knowing the cost and ownership attached to each mechanism.

Price freshness explicitly

Ask the consumer for the decision that changes inside the requested interval.

If a weekly meeting uses the report, hourly refresh may already be excessive. If an operations team acts within minutes, a daily table is obviously wrong. “Real time” isn’t a requirement until it names the action delayed by stale data.

Document the freshness SLO beside the serving model. Include source latency, transformation cadence, and destination refresh. A five-minute dashboard refresh on top of data that arrives hourly is decorative impatience.

Don’t harden unstable meaning

Precomputation multiplies confusion when the grain or metric definition is still under argument. Stabilize logic upstream first. Otherwise every scheduled table, view, and extract becomes another place that must be migrated when the definition changes.

Start live while the question is exploratory. Precompute once the question, grain, and access pattern repeat. Move down the ladder again when the requirement changes. An extract isn’t a life sentence.

Choose the cheapest operational mechanism that meets the stated freshness and interaction need. Feature collecting isn’t a serving strategy.