When Precomputation Beats Repeated Raw Fact Scans: Latency, Freshness, and Maintenance Cost

Learning outcomes

1. AtlasMart’s problem: the answer is correct, but the repeated work is expensive

After Chapter 18, AtlasMart knows how to reduce the work of scanning its atomic fact through projection and data skipping. That does not eliminate every expensive repeated aggregation. The sales dashboard repeatedly asks for GMV and units by date and product. If hundreds of consumers repeatedly scan the same atomic rows and regroup them, the warehouse can choose to precompute some of that deterministic work. Precomputation means persisting a result derived from governed base data so later reads can do less work.

The decision is not “materialized views are fast.” It is: is the repeated read work large and frequent enough to justify another persisted object with refresh, lineage, security, testing, storage, invalidation, and ownership obligations? The consumer needs low latency, finance still needs atomic drill-down, and operations need a freshness contract that tells them whether a cached aggregate is allowed to lag.

2. Three costs move in opposite directions

A normal SQL view saves query text, not necessarily query work. A persisted aggregate or materialized view saves work because data is stored. Product behavior varies: PostgreSQL 18 materialized views persist rows and can be explicitly refreshed, while the mandatory SQLite lab below manages an equivalent table itself. That implementation difference does not change the logical requirement to declare grain, freshness, and lineage.

3. Evidence: same query semantics, radically different row counts

The Chapter 18 deterministic scale-up produces 216,000 paid order-line benchmark rows. Grouping once by date and product produces 731 aggregate rows. Both queries below answer the same date-window/product totals. In the generation environment, the base query had a median of 23.256 ms; the aggregate query had a median of 0.045 ms. Those timings are evidence for this local run only. The more portable mechanism evidence is that one plan scans a 216,000-row fact while the other scans a 731-row aggregate and both return identical results.

fact rows: 216000aggregate rows: 731base median: 23.256 msaggregate median: 0.045 msbase plan: [[7, 0, 0, 'SCAN f'], [14, 0, 0, 'USE TEMP B-TREE FOR GROUP BY']]aggregate plan: [[7, 0, 0, 'SCAN a'], [12, 0, 0, 'USE TEMP B-TREE FOR GROUP BY']]result equality: PASSNOTE: timings are generation-environment observations, not portable guarantees.

Do not use this ratio as a cloud cost estimate. Cache, vectorization, distributed shuffle, concurrency, storage format, partition pruning, and billing units can dominate on another engine. Re-run the same semantic corpus on the target platform.

4. Controlled failure: “materialized” does not mean “current”

At source sequence 206, AtlasMart builds agg_daily_product and records watermark 206. Then source sequence 207 inserts O1010/1 for 80 USD GMV and 45 USD cost. The atomic fact is immediately 820 USD GMV, while the untouched acceleration object still says 740. A dashboard routed blindly to the aggregate is now wrong by 80 USD relative to current truth.

canonical before Chapter 19 extension: 9 lines / 7 orders / 11 units / $740 GMV / $450 cost / $290 profitfresh aggregate before change:            9 lines / 11 units / $740 GMV / $450 cost / $290 profitbase after unrefreshed seq-207 insert:     10 lines / 8 orders / 12 units / $820 GMV / $495 cost / $325 profitstale aggregate after insert:              9 lines / 11 units / $740 GMV / $450 cost / $290 profitstaleness gap:                              $80 GMVstate before repair:                        watermark 206, status=staledirty aggregate key:                        (2026-09-22, P100)aggregate after incremental refresh:        10 lines / 12 units / $820 GMV / $495 cost / $325 profitrefresh rerun:                              0 dirty keys; aggregate hash unchangedsemantic contract hash:                     9a7b963a31b049bbe64a822b6a2dd9549f43b2732222cfac29cd600c0bf9a747

5. A practical precomputation decision rule

Use workload evidence rather than a universal threshold. Estimate a review-period budget such as reads_per_period × work_saved_per_read, then compare it with refresh work, storage, invalidation complexity, and freshness risk. The units can be rows, bytes, CPU-seconds, warehouse credits, or dollars only when the engine exposes trustworthy measurements. The rule is intentionally economic rather than vendor-specific.

Precompute when the query shape is common, the higher grain can answer it without semantic loss, refresh can meet the consumer’s freshness requirement, and the derived object is operationally owned. Do not precompute when query shapes are highly diverse, atomic latency already meets the SLO, source updates make invalidation disproportionately hard, or the only justification is “cubes are standard.”

6. Reproducible local probe

Run python ch19_lab.py from a writable directory. The complete script is supplied in Lesson 5; this chapter excerpt shows the aggregate physical contract.

CREATE TABLE agg_daily_product(  order_date TEXT NOT NULL,  product_id TEXT NOT NULL,  line_count INTEGER NOT NULL,  order_count INTEGER NOT NULL,  units INTEGER NOT NULL,  gmv_cents INTEGER NOT NULL,  cost_cents INTEGER NOT NULL,  profit_cents INTEGER NOT NULL,  refreshed_through_seq INTEGER NOT NULL,  refreshed_at TEXT NOT NULL,  semantic_contract_hash TEXT NOT NULL,  PRIMARY KEY (order_date, product_id));

Reset with rm -rf atlasmart_ch19_lab on shells that support it, or delete that directory manually on Windows. Re-running the complete script recreates the database deterministically.

7. Production judgment

• Correctness: acceleration cannot redefine the paid-sales filter, grain, currency, or profit formula.
• Freshness: expose base watermark, acceleration watermark, refresh timestamp, and status to routing/monitoring.
• Idempotency: a refresh retry at the same watermark must converge to the same semantic rows/hash.
• Security: aggregated data can still be sensitive; derived objects need equivalent policy boundaries.
• Observability: record query work, refresh work, result reconciliation, stale duration, failures, and consumer routing.
• Rollback: atomic facts remain authoritative, so consumers can route back to the base calculation if the accelerator is stale or corrupt.

8. Bridge to aggregate fact design

Precomputation is useful only after its result has a precise business grain. Lesson 2 designs that grain and shows why GMV can reconcile through a daily/product aggregate while enterprise distinct order count cannot be obtained by simply summing per-group distinct counts.

Knowledge check

Authoritative references

• Kimball Group — Aggregate Fact Tables or CubesNamed dimensional technique for numeric rollups that accelerate queries while retaining atomic facts and conformed dimensional semantics.
• Kimball Group — Fact TablesBackground for declaring grain first and retaining atomic facts as the expressive foundation beneath higher-grain aggregates.
• PostgreSQL 18 — Materialized ViewsCurrent official example of persisted query results that may be faster to read but are not inherently current. PostgreSQL is a reference, not a prerequisite for the local lab.
• PostgreSQL 18 — REFRESH MATERIALIZED VIEWCurrent official full-refresh semantics and concurrency conditions; exact behavior is PostgreSQL-specific and must not be generalized to all engines.
• PostgreSQL 18 — GROUPING SETS, CUBE, and ROLLUPOfficial SQL example for cube-like grouping semantics and the power-set nature of CUBE.
• SQLite — CREATE VIEWOfficial ordinary-view semantics. The mandatory lab deliberately uses a table plus explicit refresh metadata instead of pretending SQLite provides native materialized views.
• SQLite — EXPLAIN QUERY PLANOfficial plan evidence used in the local benchmark. Plan output is diagnostic and engine/version dependent.
• Python — sqlite3Standard-library interface used by the no-cost local lab.