Materialized View Refresh Strategies, Incremental Refresh Concepts, and Dependency Management

Learning outcomes

1. Materialization is persisted query state plus a refresh contract

A materialized view is an engine-managed persisted result of a query on systems that implement that feature. PostgreSQL 18, for example, persists rows and later regenerates them with REFRESH MATERIALIZED VIEW. Other engines have different automatic/incremental refresh rules, eligibility restrictions, rewrite behavior, and concurrency semantics. SQLite has ordinary views but no native materialized-view command, so the mandatory lab uses an explicitly managed table. The vendor-neutral mechanism is the same: base dependencies change; the persisted result becomes stale until a refresh process makes it current to a defined watermark.

2. Full refresh versus incremental refresh

The lab uses partition/key recomputation because it makes the correctness mechanism observable without pretending to emulate a proprietary incremental materialized-view engine.

3. Stale-read trace: base sequence 207, aggregate sequence 206

The full refresh records refreshed_through_seq=206 at 09:00 UTC. Sequence 207 arrives at 09:07 and is committed to fact_sales. The acceleration state is immediately marked stale. If a reader at 09:08 is allowed a 15-minute freshness SLO, policy could still permit the accelerator; if the report requires “through latest committed sequence,” it must route to base or wait for refresh. The data engineer must state which contract applies rather than silently choosing.

base:      watermark 207, GMV $820aggregate: watermark 206, GMV $740state:     stalefreshness gap: $80 for this fixturerefresh age: a timestamp observation, not proof that all dependencies are complete

4. Dependency-driven repair

change_log records which old and new aggregate keys each changed fact can affect. Sequence 207 dirties only (2026-09-22,P100). The refresh deletes that aggregate row and re-derives it from current base truth, then advances the acceleration watermark only after the transaction commits.

-- Conceptual partition-local recompute for dirty aggregate keys.-- The complete lab derives these keys from change_log between watermarks.DELETE FROM agg_daily_productWHERE order_date = '2026-09-22' AND product_id = 'P100';INSERT INTO agg_daily_productSELECT order_date, product_id,       COUNT(*), COUNT(DISTINCT order_id), SUM(quantity),       SUM(amount_cents), SUM(cost_cents), SUM(amount_cents-cost_cents),       207, '2026-09-21T09:10:00Z', :semantic_contract_hashFROM fact_salesWHERE status='paid'  AND order_date='2026-09-22'  AND product_id='P100'GROUP BY order_date, product_id;

The complete executable function discovers dirty keys between the old and requested watermarks. On an immediate rerun through sequence 207, it finds zero new dirty keys and the aggregate semantic hash stays unchanged. That is idempotent refresh behavior for this fixture.

5. Controlled wrong approaches

6. Dependency graph and invalidation scope

AtlasMart’s simplified lineage is source CDC → fact_sales → agg_daily_product → certified sales dashboard. A new sales row invalidates one or more aggregate groups. A metric-definition change invalidates every accelerator built on the old semantic contract. A product-dimension label correction may require no measure recomputation if the aggregate stores only product_id, but a pre-joined aggregate containing product category may need broader rebuild. Invalidation scope follows actual lineage, not table names.

{  "object": "agg_daily_product",  "base": "fact_sales",  "grain": "one paid-sales aggregate row per order_date + product_id",  "measures": ["line_count", "units", "gmv_cents", "cost_cents", "profit_cents"],  "non_additive_warning": "order_count cannot be summed across product/date groups for global distinct orders",  "refresh": {    "mode": "incremental-partition-recompute",    "watermark": 207,    "freshness_slo_seconds": 900  },  "semantic_contract_hash": "9a7b963a31b049bbe64a822b6a2dd9549f43b2732222cfac29cd600c0bf9a747",  "owner": "Analytics Engineering",  "lineage": ["fact_sales -> agg_daily_product -> certified sales dashboard"]}

7. Production refresh judgment

• Transaction boundary: publish new aggregate rows and watermark consistently.
• Retry: rerun from the same base watermark must converge.
• Late data: change evidence must identify historical groups that become dirty.
• Schema evolution: contract-breaking base changes block refresh until mapping/semantics are approved.
• Security: refresh identity needs only the privileges required to read governed inputs and publish its derived object.
• Observability: record requested/committed watermarks, dirty-key count, rows rebuilt, duration, failures, and reconciliation.
• Rollback: keep the last certified accelerator generation or route to atomic facts if the new generation fails validation.

8. Bridge to cubes and semantic acceleration

One daily/product aggregate is tractable. A cube-like system considers many grouping levels and dimension combinations. Lesson 4 shows why the power set grows quickly, why real dimensional spaces are sparse, and why workload-driven selection beats “materialize everything.”

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.