Materialized Views vs Pre-Computed Aggregates · System design

Interview framing

Compare database-managed materialized views (often auto-refresh) with pipeline-managed pre-computed aggregate tables (Spark/dbt writes). When each? What failure modes appear, including refresh storms?

Background from first principles

Dashboards time out when they scan huge fact tables on every load. Two common cures:

1. Ask the database to maintain a derived result: a materialized view (MV). 2. Explicitly build an aggregate table in a pipeline you orchestrate (dbt model, Spark job).

Both store precomputed answers. They differ in who controls refresh, how dependencies cascade, how you test definitions, and how blast radius looks when a base table gets a huge backfill.

Materialized view (concept)

An MV is a stored query result the database knows about. Some warehouses refresh on a schedule, some incrementally, some on commit. The optimizer may rewrite queries to use the MV. Convenience is high when the warehouse support is mature.

Pipeline-managed aggregate

You write CREATE TABLE / dbt model SQL in git. An orchestrator runs it at times you choose, with concurrency limits, tests, and lineage. Freshness is an explicit SLA you schedule for, not only a DB background behavior.

Full problem statement

Choose between MVs and explicit aggregate tables for an analytical warehouse with BI concurrency. Explain freshness control, dependency graphs, refresh storms, cost of recomputation vs query latency, and observability of last successful refresh. Recommend based on warehouse capabilities and team practices (e.g. already on dbt).

What "good" looks like

You start from a slow dashboard. You define both options without vendor worship. You explain refresh storms with a concrete cascade. You compare control, testing, and cost windows. You recommend with assumptions about incremental MV quality and orchestration maturity.

Clarifying questions

Does the warehouse support incremental MVs well?
Do we already orchestrate dbt (or similar)?
SLA for dashboard freshness?
How many derived datasets would hang off popular facts?
Are transforms single-warehouse SQL or multi-source?

Scale prompts

Fact table size and scan cost per dashboard open.
Number of MVs on one hot base table.
Warehouse CPU during morning refresh storms.
Cost of a full MV rebuild after a 2-year backfill.

Out of scope

Claiming all MVs are incremental and cheap.
Duplicating the same aggregate in both MV and dbt without reason.
Ignoring freshness monitoring.

First principles expanded

Every dashboard query has a cost. Scanning 10TB repeatedly moves money from the company to the cloud bill and moves patience out of the room. Pre-computation shifts cost to write time.

The architectural fork:

Let the database maintain derived storage (materialized views).
Let pipelines maintain derived tables on purpose (dbt/Spark aggregates).

Both are pre-computation. Control plane differences drive reliability.

Restated problem

"Compare DB-managed MVs with orchestrated aggregate tables. Explain freshness control, dependency graphs, refresh storms, observability, and when each fits. Assume BI concurrency on a large warehouse."

What good looks like

Slow dashboard opening story.
Accurate MV caveats (not all incremental).
Refresh storm narrative with concurrency.
Testing and git workflows contrast.
Recommendation tied to team toolchain.

Clarifying questions expanded

Warehouse product and MV features?
Already on dbt?
Freshness SLA by dashboard tier?
Backfill frequency?
Can heavy rebuilds use isolated compute?

Estimation prompts

Seconds saved per dashboard load × daily views.
CPU minutes per MV refresh × MV count.
Storm risk if 30 MVs share one hot fact.
Cost of nightly dbt vs continuous MV refresh.