Chapter 19 · Materialized Views, Aggregate Tables, Cubes, and Precomputation
Aggregate Fact Tables at Higher Grain and Semantic Rules for Reconciliation to Base Facts
Design a daily/product aggregate fact at a declared higher grain, preserve additive metric semantics, expose non-additive distinct-count traps, and reconcile every accelerated measure to the atomic paid-order-line fact.
Chapter 19 begins from the Chapter 18 accepted current-state
controls:
9 paid lines, 7 orders, 11 units, 740 USD GMV, 450 USD cost,
and 290 USD gross profit. The atomic grain remains
one current paid order line. Precomputation
never replaces or rewrites that atomic fact. Later in the lab,
source sequence 207 deliberately introduces one new
paid line (O1010/1, 80 USD GMV, 45 USD cost) so the
base truth becomes
10 lines, 8 orders, 12 units, 820 USD GMV, 495 USD cost, 325
USD gross profit. That change is an explicit Chapter 19 fixture extension used
to demonstrate stale acceleration and refresh.
Mandatory runtime: Python standard library plus
SQLite; generation evidence used Python 3.13.5 and SQLite
3.46.1. Environment: local
filesystem/in-process database; no managed service or paid
feature. Storage: SQLite tables; because SQLite
has ordinary views but no native materialized-view command,
agg_daily_product is an explicitly managed
materialized equivalent table.
Time: AtlasMart business dates are UTC dates
and refresh timestamps are UTC ISO-8601 strings.
Keys: atomic sales key is
(order_id,line_no); aggregate key is
(order_date,product_id).
History: this chapter does not change SCD
rules; it accelerates the current paid-sales fact.
Security: synthetic identities only.
Benchmark cache: two warm-ups and seven
measured executions; OS cache is not flushed.
Non-guarantee: local timings do not predict
cloud latency or billing.
Learning outcomes
Declare an aggregate fact grain independently from its physical implementation.
Reconcile additive measures from daily/product aggregates to atomic paid sales.
Identify why distinct order counts are not additive across overlapping aggregate groups.
Preserve conformed dimensions, drill-down paths, lineage, and atomic accessibility.
1. Higher grain is a new fact table contract, not a compressed copy
An aggregate fact table stores measurements rolled up from a more atomic fact at a declared higher grain. AtlasMart’s atomic sales grain is one current paid order line. The Chapter 19 aggregate grain is one paid-sales aggregate row per order date and product. This row no longer identifies customer, channel, order line, or individual order; those dimensions are outside its grain.
That loss of detail is deliberate. The aggregate is suitable for date/product sales summaries, but not for customer segmentation, channel filtering, order-line drilldown, or any request whose dimensions are absent. The atomic fact remains available for those questions.
2. Build only measures whose aggregation semantics survive the rollup
BEGIN;DELETE FROM agg_daily_product;INSERT INTO agg_daily_productSELECT order_date, product_id, COUNT(*) AS line_count, COUNT(DISTINCT order_id) AS order_count, SUM(quantity) AS units, SUM(amount_cents) AS gmv_cents, SUM(cost_cents) AS cost_cents, SUM(amount_cents - cost_cents) AS profit_cents, 206 AS refreshed_through_seq, '2026-09-21T09:00:00Z' AS refreshed_at, :semantic_contract_hashFROM fact_salesWHERE status = 'paid'GROUP BY order_date, product_id;COMMIT;| Measure | Safe at daily/product grain? | Can it be summed again? | Reason |
|---|---|---|---|
| line_count | Yes | Yes across disjoint groups | Each atomic line belongs to exactly one date/product group. |
| units | Yes | Yes | Additive quantity. |
| GMV/cost/profit | Yes | Yes | Additive cents with unchanged paid filter/formulas. |
| order_count = COUNT(DISTINCT order_id) | Can be stored for that group | No across overlapping groups | One order may contain multiple products, so group distinct sets overlap. |
| average order value | Derive carefully | No simple average-of-averages | Needs correct numerator and globally deduplicated denominator. |
3. Controlled failure: an aggregate can reconcile dollars while corrupting distinct orders
Before the Chapter 19 extension, AtlasMart has 7 distinct paid
orders. Its daily/product aggregate stores per-group
COUNT(DISTINCT order_id); summing those counts
produces 9, because multi-product orders
contribute to more than one product group. GMV still reconciles
to 740 USD, so “the money ties out” does not prove every metric
is safe on this aggregate.
-- Safe additive controls at this aggregate grain.SELECT SUM(line_count) AS lines, SUM(units) AS units, SUM(gmv_cents) AS gmv_cents, SUM(cost_cents) AS cost_cents, SUM(profit_cents) AS profit_centsFROM agg_daily_product;-- DO NOT use SUM(order_count) for enterprise distinct orders.SELECT COUNT(DISTINCT order_id) AS true_ordersFROM fact_salesWHERE status = 'paid';Distinct count is not an additive fact across overlapping sets. Repair the semantic contract: either answer enterprise distinct orders from atomic/order-grain data, maintain an appropriate distinct-count acceleration with its own semantics, or expose only group-level order counts and prevent unsafe rollup.
4. Reconciliation is metric-specific
After the explicit sequence-207 insert, safe additive controls
reconcile exactly: 10 lines, 12 units, 820 USD GMV, 495 USD
cost, and 325 USD profit. The aggregate’s
order_count column is still useful when the query
remains at an individual date/product group; it simply cannot be
advertised as additive.
A production reconciliation test should compare base and aggregate over the same filter horizon and semantic version. It should not compare unrelated row counts, and it should not accept approximate equality unless the metric contract explicitly defines approximation.
5. Dimensions, drill-down, and aggregate navigation
Kimball’s aggregate-fact guidance keeps atomic facts available and allows a query layer to choose an aggregate only when the requested dimensions and measures are covered. A higher-grain aggregate may use shrunken conformed dimensions: reduced dimension representations that preserve the same governed members/hierarchies relevant at the aggregate grain. Do not invent a new product taxonomy just because the table is summarized.
The routing rule should be semantic: if a request needs customer, channel, line-level auditability, or exact distinct orders, route to an appropriate lower-grain object. If it needs date/product additive sales measures and the aggregate is fresh, the acceleration path is eligible.
6. Wrong design: “one aggregate table for every dashboard”
Dashboard-specific tables often duplicate filters, labels, and formulas. Over time, one says revenue is paid GMV while another silently includes pending orders. That is semantic divergence disguised as performance work. Repair it by binding every accelerator to a versioned metric contract and tracing its columns back to the same governed atomic measures.
{ "grain": "one current paid order line", "aggregate_grain": [ "order_date", "product_id" ], "filter": "status = paid", "gmv": "sum(amount_cents)", "cost": "sum(cost_cents)", "profit": "sum(amount_cents-cost_cents)"}7. Verification checklist
- Declare atomic and aggregate grain in business language.
- List dimensions deliberately removed by aggregation.
- Classify each measure as additive, semi-additive, non-additive, or scoped.
- Reconcile additive totals at identical filters/watermarks.
- Test distinct-count and ratio boundaries explicitly.
- Keep atomic drill-down accessible and lineage unbroken.
- Version semantic changes and rebuild/dual-read before cutover.
8. Bridge to refresh
A perfectly designed aggregate can still be wrong for the current moment if its base dependencies changed. Lesson 3 adds refresh watermarks, stale status, change-driven invalidation, full-versus-incremental refresh, and retry behavior.
Knowledge check
Check your understanding
- What is the exact aggregate grain?
- Why can GMV be re-summed but order_count cannot?
- What consumer query forces a fallback to atomic data?
- Why is matching total GMV insufficient as the only test?
Review the answers
1. One paid-sales aggregate row per order date and product.
2. GMV partitions additively; distinct-order sets can overlap across products.
3. Any query requiring dimensions/details absent from the aggregate, such as customer or line-level audit.
4. Non-additive metrics, dimensions, freshness, and semantic filters can still be wrong.
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.