A materialised view stores the answer to a query instead of computing it on demand. Change data capture keeps such views current by reading the database's own replication log rather than by having the application publish updates.
Together they are how derived data is maintained in practice: read models for CQRS, search indexes, caches, analytics tables, and the feeds that keep one service's copy of another's data current.
Why derived data exists
Some questions are expensive to answer and cheap to keep. "Revenue by region this month" over ten million orders is a scan and an aggregation; the answer is one row. Computing it per request wastes the same work repeatedly.
A materialised view trades storage and freshness for read cost. The two questions that follow are the whole design:
- How stale may it be? This is a product decision, not a technical one, and it must be stated as a number. An admin dashboard tolerates five minutes; an account balance tolerates none.
- How is it kept current? Three approaches, with very different properties.
Diagram, as text
- Write to source of truth leads to Primary store
- Primary store leads to Recompute the whole view (1. scheduled refresh)
- Primary store leads to Update view from domain events (2. application publishes)
- Primary store leads to Replication log (3. change data capture)
- Replication log leads to Update view from row changes
- Recompute the whole view leads to Materialised view
- Update view from domain events leads to Materialised view
- Update view from row changes leads to Materialised view
The three ways to maintain it
Scheduled full refresh. Recompute periodically. Simple, self-correcting — every refresh repairs any previous error — and its staleness equals the interval. It stops scaling when a full recompute takes longer than the interval allows.
Application-published events. The application emits a domain event and a projection applies it. Semantically rich: the event says OrderCancelled, which is more meaningful than a row changing. Requires the outbox to avoid the dual-write problem, and any write path that bypasses the application — a migration, an admin fix, a bulk import — silently misses the view.
Change data capture. Read the database's replication log and derive changes from it. Nothing can bypass it, because every committed change is in the log by definition. No application code in the publish path, low latency, and it works for legacy applications that emit no events at all.
The trade is that CDC gives you rows, not meaning. A cancellation appears as status changing from paid to cancelled, and reconstructing intent from that is possible but fragile — the projection now depends on the schema, so a column rename becomes a downstream outage. It also couples you to the database's log format and, in practice, to a piece of infrastructure that needs its own operating.
Choosing between them: scheduled refresh while it fits, domain events when the projection needs intent and the application owns every write path, CDC when writes come from places you do not control or when the source system cannot be changed.
Rebuilding is a first-class requirement
Projections have bugs. A rebuild is how you recover, and it must be designed for rather than improvised during an incident.
- The rebuild must be possible from the source of truth, at any time, without downtime.
- Rebuild into a new view and switch, rather than rebuilding in place — in-place means serving partial data during the rebuild, at exactly the moment someone is watching.
- Projections must be idempotent, so replaying overlapping changes is safe. Under CDC and at-least-once delivery, duplicates are certain.
- Test the rebuild path regularly. A rebuild that has never been run is a plan, not a capability, and it will be attempted for the first time under pressure.
Where this fits with what you already know
A read model in CQRS is a materialised view, and everything here applies to it — the staleness budget, the lag alert, the rebuild path.
A cache is a materialised view with eviction. Cache-aside, write-through and write-behind, covered under caching in high-level design, are the same maintenance choices with different names.
A search index is a materialised view in a store optimised for a different access pattern.
One service holding a copy of another's data is a materialised view maintained by events — which is how you avoid a synchronous call on every request, at the cost of staleness and a copy to keep current.
Check your understanding
Common mistakes
No stated staleness budget. Every design argument is unresolvable without the number.
No lag alert. A stalled projection is silent and serves confidently wrong data.
No tested rebuild path. It will be attempted for the first time during an incident.
Rebuilding in place. Partial data served exactly when someone is watching.
Non-idempotent projections. Duplicate delivery is certain under CDC and at-least-once messaging.
CDC projections tightly coupled to the schema. A column rename becomes a downstream outage.
Business decisions in a projection. Projections transform shape; decisions belong in the write model.
Views nobody queries. Each one is permanent maintenance — build them for real queries.
Practice
A service needs a copy of customer data owned by another team's service, currently fetched synchronously on every request. Design the alternative, including how you would decide between events and CDC.
Write your own answer first, on paper or in a note. Then compare.
Show a hint
Start by asking what the data is used for and how fresh it must be.
Show a strong answer
Establish the staleness budget first, because it decides everything else. If the copy is used to display a customer's name on an order, minutes of staleness are irrelevant. If it is used to check whether an account is suspended before allowing a transaction, staleness is a security question and a local copy may be the wrong answer entirely. I would ask for the specific uses before proposing a mechanism — and this frequently reveals that most uses tolerate staleness while one does not, which is best solved by keeping that one synchronous rather than making the whole copy strongly consistent.
Assuming the tolerant case, the design: a local materialised view of the fields this service needs — not the whole customer record. Copying only what is used keeps the coupling small and means a change to fields we do not use is not our problem.
Events versus CDC, and here I would push hard for events:
- The data is owned by another team. CDC would mean reading their database's replication log, which couples us to their schema — a private implementation detail they are entitled to change without telling us. A column rename on their side becomes an outage on ours, and no reasonable ownership boundary survives that.
- Events are a published contract.
CustomerSuspendedis a fact they have committed to, versioned, and can evolve deliberately. That is the interface between two teams; the schema is not. - Intent matters here. A suspension arriving as a row change requires us to infer meaning from a status column. As an event it is unambiguous.
When I would accept CDC instead: if the owning team cannot or will not publish events — a legacy system, or no capacity to change it. Then CDC is the pragmatic answer, and I would insist on an explicit agreement about schema stability, or place the CDC pipeline under their ownership so the coupling is visible to whoever might break it.
What the design needs regardless of mechanism:
- An initial load, since events only carry changes from now on. Usually a bulk export followed by the event stream, with overlap handled by idempotent application.
- Idempotent projection, since delivery is at-least-once.
- Lag monitoring with an alert, because a stopped subscription means we serve stale customer data with no error.
- A rebuild path, tested, so a projection bug is recoverable.
- A staleness indicator in the data itself — the timestamp of the last applied change — so anything sensitive can check it rather than assuming freshness.
What I would keep synchronous: any check where being wrong is unacceptable. A hybrid — local copy for display, live call for authorisation — is usually better than forcing one answer for all uses.
A revenue dashboard's materialised view has been wrong for three weeks. Nobody noticed until finance queried the numbers. Diagnose the process failure and design the controls.
Write your own answer first, on paper or in a note. Then compare.
Show a hint
The projection bug is the smaller problem here.
Show a strong answer
The projection bug is incidental; the three weeks is the actual failure. Derived data will be wrong sometimes — a bug, a stall, a missed event. The question a review should answer is why nothing detected it, and the answer is that there was no control that could.
Why it was invisible: the dashboard rendered, the queries succeeded, the numbers looked plausible. Every signal a normal monitoring setup produces was green, because staleness and incorrectness do not produce errors. Only someone who independently knew the right answer — finance — could catch it, and they only look monthly.
Controls I would add, in order of value:
- Projection lag, alerting. Time since the last applied change against the source. This catches a stopped or stalled projection within minutes and is one query. If the view had simply stopped, this alone would have caught it on day one.
- A reconciliation check against the source of truth, run daily. Recompute the aggregate directly from the orders table for a recent window and compare with the view. Disagreement alerts. This is the control that catches a projection that is running but wrong, which lag cannot detect — and it is what was actually needed here.
- Freshness displayed on the dashboard itself. "As at 14:32" next to the number. Cheap, and it makes staleness visible to the people reading it rather than only to engineers.
- Continuity checks on the numbers. Revenue dropping to zero, or doubling, should alert regardless of whether the pipeline is healthy. Domain-aware alerting catches classes of error that infrastructure monitoring cannot.
On the rebuild, since it is now needed:
- Rebuild into a new view and switch atomically, not in place. People are already looking at this dashboard.
- Verify the rebuilt view against the reconciliation check before switching, so the switch is evidence-based.
- Then correct the historical record if anything downstream consumed the wrong figures — reports sent, decisions made. That is the part that matters to finance and is easy to forget once the technical fix is in.
What I would put in the review: the lesson is not "we had a projection bug", it is "we had no way to know our derived data was wrong". Every materialised view in the system likely shares that gap, so the action should be a standard — lag alert, reconciliation, displayed freshness — applied across all of them, not a fix to this one dashboard.
Key takeaways
- A materialised view stores the answer instead of computing it, trading storage and freshness for read cost.
- State the staleness budget first. It resolves nearly every design argument that follows.
- Three maintenance approaches: scheduled refresh (simple, self-correcting), application events (rich in intent, bypassable), change data capture (complete, but rows not meaning, and coupled to the schema).
- CDC across a team boundary couples you to their private schema — prefer published events between teams.
- Alert on lag, not errors. A stalled projection is silent and serves confidently wrong data.
- Add a reconciliation check against the source of truth — it catches a projection that is running but wrong, which lag cannot.
- Rebuild into a new view and switch, keep projections idempotent, and test the rebuild path before you need it.
- CQRS read models, caches, search indexes and cross-service data copies are all the same pattern.
Further reading
- Martin Fowler, Patterns of Enterprise Application Architecture (Addison-Wesley, 2002).
- Martin Kleppmann, Designing Data-Intensive Applications (O'Reilly, 2017) — derived data, change data capture and the log as a source of truth.
- Jay Kreps, "The Log: What every software engineer should know about real-time data's unifying abstraction" (2013).
- Chris Richardson, Microservices Patterns (Manning, 2018) — the CQRS view and its maintenance.
- Nathan Marz and James Warren, Big Data (Manning, 2015) — recomputation as a correctness strategy.
- Pat Helland, "Immutability Changes Everything" (CIDR, 2015).