You gotta push if you wanna pull

Summary

Now, the thought of duplication might trigger some reservations: isn’t chaos going to ensue if there are multiple copies of the same data set? Which version is the right one? Aren’t you at the risk of producing inconsistent query results? I think there’s not much to fear if you keep one fundamental principle in mind: there should be exactly one canonical instance of the data set. As the system of record it is the only one that gets mutated by business transactions. All other views of the data set are derived from this one, i.e. it is the source of truth. In practice, it is not feasible to update all derived views synchronously, in particular if they are located in another system. This means consumers need to account for eventual consistency of view data. For many use cases, such as analytics, that is perfectly acceptable. Other situations might have stronger consistency requirements, making it necessary to prevent stale data from being retrieved from a view. Different techniques exist for doing so, such as tracking logical timestamps or log sequence numbers (LSNs). This raises the question of how to keep all these different flavors of materialized views in sync, as the original data set changes. New records will be added, existing ones be updated or removed, and all the derived views need to be updated in order to reflect these changes. You could periodically recreate any derived views from scratch, but not only might this be a very costly operation, you’d also have to deal with outdated or incomplete query results very quickly again. Thinking about it, recomputing materialized views from scratch can be pretty wasteful. Typically, only small parts of a dataset change, hence also only small parts of any derived views should require to be updated. Intuitively, this makes a lot of sense. For instance, assume you’d want to keep track of the revenue per product category across the purchase orders in your system. When a new order arrives, would you recalculate the totals ...