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

Live querying stops being the right default fairly early

A reporting stack can stay live for a while without much trouble. Early on, that is often the cleanest option. Query the warehouse directly, avoid premature serving layers, and let the usage pattern reveal itself. That works until the same reads start showing up over and over again. The same dashboard gets opened all day, the same summary lands in the same meeting every morning, the same endpoint keeps asking for the same slice with only minor variation. At that point, leaving it live is no longer flexibility. It’s just repeated compute being treated like free convenience.

That’s the pattern behind dashboard churn. The problem isn’t that BigQuery can’t answer the question. The problem is paying query-time cost every time for a read path that has already become predictable. Once the access pattern is stable, repeated live querying usually says more about missing serving discipline than about any real need for freshness.

The useful question is freshness, not features

Cache, scheduled tables, materialized views, and extracts are easy to treat as separate product features. That framing usually makes the decision worse. The real question is how fresh the result needs to be, how often it will be reused, and how much duplication or refresh ownership the system can carry in exchange for a calmer read path.

1. Result cache
   cheapest
   fragile
   best-effort

2. Scheduled table
   simple
   explicit refresh
   very SME-friendly

3. Materialized view
   narrower SQL shape
   maintenance overhead
   can be elegant when it fits

4. BI extract
   fastest dashboard
   highest duplication
   weakest "single source of truth" story

Once the decision gets framed as a freshness budget, the ladder gets much easier to use. A report that can tolerate an hour of staleness should not still be negotiating for live warehouse work. A dashboard opened all day shouldn’t keep rerunning the same heavy path just because the tool makes that easy. Repeated live querying is often just stale architecture wearing a real-time badge.

Each rung solves a different kind of problem

Result cache is great when it hits and useless as a strategy. It costs nothing when the SQL stays identical and falls apart the moment the query text shifts, parameters move, or a BI layer wraps the statement in slightly different ways. That’s fine. Cache is a bonus. It’s not a plan.

Scheduled tables are the default answer more often than anything else. They’re explicit, easy to reason about, and far less temperamental than the more “clever” options. If the logic is stable and the consumer can live with hourly or daily refresh, a scheduled table is usually the best trade available. It moves cost and complexity into a place that is easy to observe and easy to control.

Materialized views can be elegant when the query shape and base tables actually cooperate. When they don’t, they become a neat wrapper around a bad fit. They still depend on the underlying model being sane, including basic things like honest partitioning. If the base tables are awkward and the read path is already strained, an MV doesn’t fix that.

Extracts are the blunt instrument. They can produce the fastest dashboard experience and the weakest semantic boundary at the same time. That trade is sometimes fine. It just needs to be deliberate. If the system is going to duplicate data for speed, that decision should be named clearly and owned accordingly.

BI Engine sits next to this ladder, not on it. It accelerates live queries. It doesn’t replace them. That’s why when BI Engine helps is a separate decision.

Freshness should be bought, not assumed

A lot of reporting architecture goes wrong because freshness is never made explicit. The consumer quietly assumes live data is better. The serving path quietly absorbs the cost. Then the warehouse ends up doing expensive repeated work for a freshness target nobody ever stated clearly.

That is backwards. Freshness is only worth paying for when the reader actually needs it. If a dashboard can tolerate fifteen minutes, seconds are waste. If it can tolerate an hour, constant live scans are waste. If the number still isn’t stable enough to trust, lower latency doesn’t fix that. It just makes the instability arrive faster, which is the same problem behind freshness versus trust. A bad freshness target usually creates a bad serving architecture long before it creates a performance problem.

Precompute shifts cost upstream on purpose

Materialization is not free. The cost just moves. Refresh jobs need to run. Storage has to exist. Dependencies need to be managed. Ownership has to be clear. That is all normal. The useful comparison is whether those costs are lower than rerunning the same live query path all day long.

In practice, they often are. That’s why this decision belongs close to broader cost guardrails. When the same read pattern keeps showing up in the bill, tighter limits are usually not the first fix. The better fix is often to move that read path onto a cheaper rung of the ladder and stop pretending it still deserves live compute.

create or replace table mart.sales_daily as
select
  order_date,
  store_id,
  sum(net_revenue) as net_revenue,
  count(*) as orders,

from
  mart.orders

group by
  order_date,
  store_id,
;

That kind of move is rarely glamorous, but it usually works. The system stops paying for the same answer over and over, and the read path gets simpler at the same time.

Don’t precompute unstable semantics

Precompute becomes a mess when the underlying logic is still drifting. If the metric definition keeps changing, if the business grain is still under argument, or if the reporting layer is compensating for upstream model gaps, materializing the output doesn’t improve anything. It just hardens the confusion into a more durable artifact.

That’s why this decision stays close to logic living upstream. Before choosing a serving mechanism, the system needs to know whether the thing being served is stable enough to deserve one. Otherwise the ladder gets used to preserve instability more efficiently, which is not much of a win.

The rule

Choose the cheapest mechanism that honestly meets the freshness need, and stop there. Don’t keep paying live query cost for recurring read paths just because the warehouse allows it. Once the access pattern is predictable, precompute is usually less about performance tuning than about finally giving the reporting path the shape it should have had already.