Part 2 of 7 · 2 min read
Many copies of the same data
No single store does everything, so each fact lives in several shapes.
Think of a school. The register records who is in class. The canteen has a list of lunch orders, the sports teacher has a team sheet, the head has a report on attendance. If a child moves class, all four lists must change, but only the register is the official record.
DDIA says no single system can satisfy all data storage, querying and processing needs. A non-trivial application combines an OLTP database (to serve requests), a cache (to speed up common requests), a full-text index (for search) and a data warehouse (for analytics). Each has its own copy of the data, in its own shape, optimised for its job. When an item changes in the database, it also has to change in the cache, the index and the warehouse.
System of record
The system of record (or source of truth) is the place that holds the authoritative version of some data. If another copy disagrees, the system of record wins.
Example: the product database is the system of record for product prices. The search index only holds a copy.
Derived data
Derived data is data created from other data by a repeatable process. If lost, it can be rebuilt from the source.
Example: a search index built from the product table, a cache of product pages, a daily sales table in the warehouse.
The oldest way to refresh copies is a batch process (page 5): take a full dump of the database, transform it and bulk-load it into the warehouse. This is ETL (extract, transform, load). It is simple, but slow, because it copies everything even if little has changed, and the copy is as old as the last run.
| Method | How fresh is the copy? | Cost | Main risk |
|---|---|---|---|
| Nightly full dump | Up to a day old | Reads all data each time | Stale data |
| Dual writes by the application | Seconds | Cheap | Inconsistency (next section) |
| Change stream (CDC) | Seconds | Needs a log reader | Lag, replay |
Check yourself:
- Which copy wins when the search index and the product database disagree, and why?
- Give two examples of derived data in an online shop.