Most data in a production system exists in two forms: the source of truth and data derived from it. The source of truth is the authoritative record, typically the primary database where writes happen. Derived data is any dataset computed from that source to serve a different access pattern. Understanding this distinction is the foundation for reasoning about consistency, staleness, and rebuild strategies across every data system you will encounter.

A materialized view is the simplest example of derived data. It is a precomputed query result stored as a table. Instead of running an expensive aggregation query every time a dashboard loads, the database computes the result once and stores it. When the underlying source tables change, the materialized view is refreshed (either automatically or on a schedule) to reflect the new state.

Consider an e-commerce platform that needs to display the total revenue per product category on an analytics dashboard. The raw data lives in an orders table and a products table. Computing revenue per category requires joining these tables and summing the order amounts, a query that scans millions of rows. Without a materialized view, every dashboard refresh runs this expensive aggregation against live production tables, competing with the transactional workload for CPU, memory, and I/O.

A materialized view precomputes this join and aggregation, reducing the dashboard query from a multi-table scan to a single table read. The dashboard now reads from a small, precomputed table instead of scanning the entire orders table. The production database no longer suffers from analytical query load, and the dashboard loads in milliseconds instead of seconds. The cost is that the dashboard shows data as of the last refresh, not the current moment.

How Materialized Views Stay in Sync

The database engine itself manages the refresh. In PostgreSQL, you call REFRESH MATERIALIZED VIEW to recompute the view from scratch. In Oracle and SQL Server, materialized views can be configured for automatic incremental refresh: when a row is inserted into the source table, the database updates only the affected rows in the view rather than recomputing everything. This incremental approach is faster but more complex internally because the database must track which source changes affect which view rows.

The refresh strategy determines the staleness window. A view refreshed every 5 minutes can serve data that is up to 5 minutes old. A view refreshed on every commit has zero staleness but adds write latency because the database must update both the source table and the view within the same transaction. Most production systems choose a refresh interval that balances query performance against acceptable staleness, typically somewhere between 1 minute and 1 hour depending on the use case.

The cost of refresh depends on the strategy. A full refresh recomputes the entire view from scratch, which is simple but expensive for large datasets. An incremental refresh updates only the rows affected by recent changes, which is faster but requires the database to maintain a change log (sometimes called a "materialized view log") on the source tables. PostgreSQL supports only full refresh. Oracle supports both full and incremental (called "fast refresh"), making it a better fit for views over very large tables that change frequently.

A common production pattern is to schedule materialized view refreshes using a cron job or a database scheduler. The refresh runs every N minutes, and downstream queries always read from the materialized view rather than the source tables. This decouples the read path from the write path: writes go to the normalized source tables at full speed, and reads come from the precomputed view at full speed. The only cost is the staleness window between refreshes.

One operational concern is refresh duration. If a materialized view takes 3 minutes to refresh and is scheduled every 5 minutes, there is a 2-minute idle gap between refreshes. If the underlying data grows and the refresh takes 6 minutes, the schedule overlaps: the next refresh starts before the previous one finishes. This can cause lock contention, increased memory usage, and even cascading failures. Monitoring refresh duration and scaling the refresh interval accordingly is essential for long-term stability.

1-- PostgreSQL: create and refresh a materialized view
2CREATE MATERIALIZED VIEW revenue_by_category AS
3SELECT
4  p.category,
5  SUM(o.amount) AS total_revenue,
6  COUNT(o.id) AS order_count
7FROM orders o
8JOIN products p ON o.product_id = p.id
9GROUP BY p.category;
10
11-- Refresh without blocking reads (concurrent refresh)
12REFRESH MATERIALIZED VIEW CONCURRENTLY revenue_by_category;

Denormalized Tables as Precomputed Joins

Denormalized tables are another form of derived data. In a normalized schema, you store user data in a users table and order data in an orders table, joining them at query time. In a denormalized table, you precompute the join and store the result: each order row includes the user's name, email, and region directly. This eliminates the join at read time, trading storage for query speed.

The tradeoff is write complexity. When a user updates their email address, you must update it in the users table (the source of truth) and in every denormalized table that contains a copy. If you miss one, the system has inconsistent data. This is the fundamental challenge of all derived data: keeping the copies in sync with the source.

Denormalization is most valuable when read volume vastly exceeds write volume. A product catalog page that serves 10,000 reads per second but updates once per hour is a strong candidate. A user profile page that updates every time the user interacts with the app is a weaker candidate because the write amplification (updating every denormalized copy) becomes expensive.

The decision of what to denormalize should be driven by your query patterns. If the product listing page always shows the seller's name alongside the product, precomputing that join eliminates a query that runs on every page load. But if the seller's name only appears on the product detail page (which gets 100x fewer hits), the join is cheap enough to compute on the fly. Denormalize the joins that appear in your hottest read paths, and leave everything else normalized.

A common mistake is denormalizing everything preemptively. Teams sometimes copy every related field into every table "just in case," creating a maintenance nightmare where a single source change requires updating dozens of tables. The better approach is to start normalized, measure which queries are slow, and denormalize only those specific joins that appear in performance-critical paths. You can always denormalize later; un-denormalizing (removing redundant copies and rebuilding consumers to use joins) is much harder.

When you do denormalize, document the derivation relationship explicitly. A comment on the denormalized column that says "derived from users.email, updated by the order_denormalization_job" prevents future engineers from treating the denormalized copy as a source of truth. Without this documentation, a new team member might update the email in the denormalized orders table directly, thinking it is the authoritative record, and wonder why the change does not propagate to the users table.

Version mismatches between the source and the denormalized copy create a specific class of bugs worth understanding. Suppose the orders table has a denormalized user_name column. When a user changes their name from "Alice Smith" to "Alice Johnson," the denormalization job updates all existing orders. But if the job crashes after updating 500 of 1,000 orders, some orders show "Alice Smith" and others show "Alice Johnson." This partial update is invisible to casual inspection because both values are plausible names. The only way to detect it is a reconciliation query that compares the denormalized column against the source table. Building this reconciliation check into the denormalization job itself (verify after update, retry on mismatch) is a best practice that prevents silent data drift.

The partial update problem is amplified in systems with high write volume. If the denormalization job takes 10 minutes to update all orders and users change their names continuously during that window, some orders may be updated to a name that is already outdated by the time the job finishes. The more fundamental solution is to move from batch denormalization (periodic jobs that sweep the table) to event-driven denormalization (a CDC consumer that updates the denormalized copy immediately when the source changes). Event-driven denormalization reduces the inconsistency window from minutes to milliseconds, at the cost of requiring CDC infrastructure.

The Source-of-Truth Test

The key mental model is that every materialized view and every denormalized table is a function of the source data. If you can re-derive it from the source at any time, it is derived data. If losing it would mean losing information permanently, it is a source of truth. This distinction guides every decision about caching, indexing, and replication in distributed systems.

A practical way to apply this test: imagine deleting the dataset entirely. If you can recreate it from other data in your system with no information loss, it is derived. If you cannot, it is a source of truth. The orders table is a source of truth because if you delete it, those order records are gone. The revenue_by_category materialized view is derived because you can recompute it from the orders and products tables. The denormalized order_with_product_details table is derived because you can reconstruct it by joining orders with products. Even if the reconstruction takes hours of compute time, the information is recoverable.

This test also reveals hidden sources of truth. A "cache" that stores user preferences that the user set through a mobile app, where the app writes only to the cache and a background job syncs to the database, is actually a source of truth during the sync window. If the cache node crashes before the sync, those preferences are lost. Recognizing this helps you make better decisions about durability and redundancy.

The source-of-truth concept also applies across services in a microservices architecture. The Users service owns user data (source of truth). The Orders service stores a copy of the user's name and email for display purposes (derived data). The Notifications service stores email addresses for sending alerts (derived data). When a user changes their email, the Users service is the single place that must accept the write. The Orders and Notifications services receive the update through an event or CDC stream and update their local copies. If their copies drift, the fix is always the same: re-derive from the Users service.

This ownership model is sometimes called the "single writer principle." Each piece of data has exactly one service that is allowed to write to it. All other services that need that data receive it through events or CDC and store it as derived data in their own databases. This eliminates distributed write conflicts entirely: there is no scenario where two services disagree about a user's email because both tried to update it simultaneously. Only the Users service writes user data, and all other copies are downstream derivations.

The single writer principle also simplifies authorization and validation. Because all writes to user data go through the Users service, that service is the single place where business rules are enforced (email format validation, uniqueness checks, rate limiting). Derived copies in other services do not need to implement these checks because they never accept direct writes. They trust the source of truth to have validated the data before publishing it.

The implication for system design is that you should identify your sources of truth early. Before building any caching, search indexing, or denormalization, ask: where is each piece of data created, and where is it merely copied? The answers define your data ownership model. Every write path goes to a source of truth. Every read-optimized copy is derived and can be rebuilt. This mental framework prevents the common mistake of treating a cache as authoritative or an analytics table as the canonical record. When in doubt, trace the data back to its origin: that origin is the source of truth, and everything downstream is derived.

A data flow diagram that shows every write entering through the source of truth and flowing outward to derived systems is a powerful communication tool. Draw the primary database in the center, with arrows pointing outward to the search index, the cache, the analytics warehouse, and the recommendation engine. Each arrow represents a CDC consumer or a derivation job. The diagram makes it immediately clear which systems are derived (they only receive data, never originate it) and which are sources of truth (they receive writes from the application). Any arrow pointing in the wrong direction (from a derived system back to the source) is a design issue that should be resolved before it causes consistency problems.

This uni-directional data flow pattern has a name in the literature: the "lambda architecture" applies it to batch and stream processing, and the "kappa architecture" simplifies it to stream-only processing. Regardless of the architecture name, the underlying principle is the same: a single source of truth feeds all derived views, and each derived view can be rebuilt independently. This principle scales from a single PostgreSQL database with a materialized view to a distributed system with dozens of specialized data stores, each optimized for a different query pattern.

The practical takeaway is simple: whenever you add a new data store to your system (a cache, a search index, an analytics database, a recommendation engine), ask three questions. First, what is the source of truth for the data this store will hold? Second, how will changes in the source of truth propagate to this store (CDC, event, polling, TTL)? Third, can this store be rebuilt from scratch without downtime?

If you can answer all three questions clearly, you have a well-designed derived data system. If any answer is unclear, the system will eventually produce inconsistencies that are hard to diagnose and expensive to fix. These three questions should be part of every system design review for any feature that introduces a new data store or a new copy of existing data. Making derived data relationships explicit in your architecture documentation prevents future engineers from accidentally treating a cache or search index as a source of truth.

When Materialized Views Break Down

Materialized views have limitations that become apparent at scale. First, the refresh window creates a consistency gap. During the window, the view shows stale data. For dashboards viewed by internal teams, a 5-minute staleness window is acceptable. For customer-facing features (showing a user their account balance), even a few seconds of staleness can cause confusion and support tickets.

Second, complex views with multiple joins and aggregations can take minutes to refresh. If the refresh takes longer than the refresh interval, refreshes start queuing up. A view that takes 6 minutes to refresh on a 5-minute schedule never catches up. The fix is either simplifying the view query, increasing the refresh interval, or switching to incremental refresh if the database supports it.

Third, materialized views consume storage proportional to their result set. A view that aggregates by category stores one row per category (small). A view that denormalizes orders with product details stores one row per order (potentially billions). The storage cost of the view must be weighed against the compute cost of running the query on demand.

Despite these limitations, materialized views remain one of the most practical tools for improving read performance. The key is choosing the right queries to materialize. Good candidates are queries that run frequently (dashboard widgets, common API endpoints), aggregate large amounts of data (SUM, COUNT, AVG over millions of rows), and tolerate bounded staleness (analytics dashboards, recommendation lists). Poor candidates are queries that need real-time accuracy (account balance, inventory count) or queries that run infrequently (ad hoc analyst reports that change every time).

A useful heuristic: if the same expensive query runs more than 10 times per refresh interval, materializing it saves net compute. If a GROUP BY query runs 100 times per minute and takes 2 seconds each time, that is 200 seconds of database compute per minute. Materializing the result and refreshing every minute costs 2 seconds per minute (one refresh) plus negligible read time (reading from a precomputed table). The savings are 198 seconds of database compute per minute, which can be redirected to handling writes or other queries.

Materialized views also serve as an abstraction layer. The downstream consumers (dashboards, APIs, reports) query the view, not the source tables. If the source schema changes (a table is renamed, a column is split into two), you update the view definition to accommodate the change. The consumers continue querying the same view with the same column names. This decoupling is valuable in large organizations where the team that owns the source tables and the team that builds dashboards operate on different release cycles. The materialized view acts as a contract between the data producers and the data consumers, absorbing schema changes on one side without disrupting the other.