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. Leaving it live now treats repeated compute like free convenience.

Dashboard churn follows this pattern. BigQuery can answer the question, but the system pays query-time cost every time for a read path that has already become predictable. Once the access pattern is stable, repeated live querying usually exposes missing serving discipline. Genuine freshness requirements are rarer.

The useful question is freshness, not features

Cache, scheduled tables, materialized views, and extracts are easy to treat as separate product features. Choose among them by 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

A report that can tolerate an hour of staleness shouldn’t 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’s 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 moves cost upstream. Refresh jobs need to run. Storage has to exist. Dependencies need to be managed. Ownership has to be clear. That’s 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. So this decision belongs close to broader cost guardrails. When the same read pattern keeps showing up in the bill, move that read path onto a cheaper rung of the ladder. Tighter limits rarely fix a read path that should no longer use 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, materialization 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 finally gives the reporting path the shape it should have had already.