This is the second post in the Data Architecture series. Post 1 wrote down one Northwind order: 1042, customer 18, a wool coat, £120, on 28 September. This post gives that order two jobs. Northwind Outfitters is a fictional shop, and so is every number about it here.
TL;DR
- The app records the order now. Finance asks about last month later. Those are two jobs.
- The jobs want different things, so a second copy sometimes appears. It is not there by rule on day one.
- Decide on three things: how fresh the answer must be, whether checkout waits, and who keeps the copy current.
Two jobs, one order
What you get: the two jobs, in plain words, and the industry names for them.
Start at the till. The customer pays for the coat. The app saves order 1042 while she waits. It has to be saved correctly, and it has to be saved now. That is the first job.
A month later, someone in finance asks: “How many wool coats did we sell in September, and what did they bring in?” That question is not about order 1042. It is about every coat order from the month. That is the second job.
Here are the names. The first job is operational work: record or look up one order, right now, while someone waits. The industry calls this OLTP, short for online transaction processing. The second job is analytical work: ask a question across many past orders. The industry calls this OLAP, short for online analytical processing.
The email from post 1 does both jobs too. Support records it now and reads it later. That is all this post says about email.
Take away: the same order is written once and used twice. Recording it and asking about it are different jobs.
What each job wants
What you get: what each job asks of the data, with the sources that name the split.
The till wants one order, saved fast and saved right. In 1993, E. F. Codd, S. B. Codd and C. T. Salley wrote a paper on this split. They said transaction applications must work with instantaneously accurate data. They also said a transaction lasts seconds, or perhaps minutes.
Finance wants the opposite shape of work. The same paper says analytical work reads historical data, treated as accurate as of the start of the question. It also says one analytical question can run for days or weeks. That “days or weeks” is the paper’s contrast with transactions. It is not a figure for Northwind.
The shop’s September question is a small version of the same thing. It reads many orders, all at once, as they stood on the first of the month. Nobody is waiting at a till for the answer. Someone in finance wants it right, and can wait a little for it.
Put side by side, the two jobs look like this. The till touches one order and wants it saved now. Finance touches many orders and wants them read together. Same data, opposite habits.
The industry has built tests around the same split. The Transaction Processing Performance Council (TPC) is the body that publishes them. Its TPC-C benchmark is an on-line transaction processing test. It simulates an order-entry mix: entering and delivering orders, recording payments, checking order status, and watching stock. Its TPC-H benchmark is a decision-support test. Its specification describes business questions that are far more complex than most transactions. I’m not quoting any scores. The point is only that the people who measure databases already treat these as two kinds of work.
Take away: the till wants one order, now. Finance wants many orders, read together. Standards bodies test them separately.
When the jobs get in each other’s way
What you get: a plain picture of the clash, and an honest account of where experts disagree.
Imagine it’s the last day of September. The coat sale is on, and checkout is busy. Finance starts its September question, which reads every order from the month. Both jobs now want the same shelf of orders at the same time.
I can’t point to a source for how badly that goes. It depends on the system. So treat it as an illustration: a long question and a stream of checkouts compete for the same resources, and the shopper at the till is the one who notices.
The experts split on what to do about it.
Michael Stonebraker and Uğur Çetintemel argued in 2005 that one design does not serve both jobs well. A system built to write one whole order at a time suits the till. Analytical work mostly reads a lot of history. They said products had already split along that line. Why the layouts differ is a later post, so I’m leaving it there.
Codd and his co-authors reported the opposite view in 1993. People had long argued that the two jobs are incompatible and that the data should be stored twice. The paper called that argument fuzzy and imprecise. It said one relational system can still support both jobs, and it called the opposite conclusion an error.
Neither side says the jobs are the same. They disagree on the remedy. I won’t pick a winner by date.
My view is this. The jobs differ either way. A second copy earns its place when the question gets in the way of recording the order. It does not earn it on the first day, just because a diagram says there are two boxes.
Take away: the clash is real as a risk. Whether it needs a second copy depends on whether finance’s question slows the till.
The second copy
What you get: what a second copy is, why it can be stale, and the one rule that protects the real order.
Say Northwind decides the clash is real. It makes a second copy: the same orders, kept for reading across many of them rather than for saving one. A later post covers where that copy lives and what it’s called. For now it’s just the shop’s orders, arranged for finance’s questions.
The second copy can lag. The TPC-H specification says its database tracks the transaction database “possibly with some delay.” That is the spec’s wording about a benchmark. I won’t put a number on Northwind’s delay, because nothing here supports one. The plain consequence is that finance may see yesterday’s orders when the till already has today’s.
A second copy also protects the real order. Codd’s paper says that when analytical results are stored, speculative results must be kept apart from data that represents the actual state of the enterprise. Finance might try a what-if, such as “what if every coat were £110?” That total belongs in finance’s copy. It does not get written onto order 1042. The order says what the customer paid.
Architecture Reality Check: The Operational Tax A second copy is a second thing to run. Someone has to keep it current, notice when it falls behind, and answer when finance’s number and the till’s number disagree. At Northwind, suppose that someone is the one developer who built the app. Every hour they spend on the copy is an hour they don’t spend on checkout. That is an illustration, but the question is real: who owns the copy?
Take away: a second copy buys finance room to ask. It costs an owner, and it may be a little behind.
When a second copy appears
What you get: a table you can use to decide, and a plain rule for a newcomer.
The table compares staying with one copy against adding a second. Every cell is a constraint to weigh, not a measurement. Northwind is fictional, and no source gives a threshold for when to switch.
| Constraint | One copy | A second copy |
|---|---|---|
| How fresh is the answer? | As fresh as the live orders | Can lag behind the live orders |
| Does checkout wait for finance? | It can, if a long question runs at a busy time | No. Finance reads its own copy |
| Who keeps it current? | Nobody extra. There is one copy | Someone must own it |
| What breaks when it lags? | Nothing lags. A heavy question can slow checkout | Finance’s number drifts from the till’s |
Table: One copy versus a second copy, by constraint. These are illustrations, not measurements.
Here is the rule I’d give someone new. Stay with one copy until finance’s question visibly slows checkout, or until finance needs a stable answer that the live orders can’t give. A stable answer means one that doesn’t change while the question runs. When either happens, you have a reason to add a second copy, and not before.
I’m not giving a size or a time limit. The research behind this post has none, and a made-up threshold would be worse than none.
Take away: one copy is the default. A second copy needs a reason you can name.
Close
The order has two jobs. The app records it now. Finance asks about it later. They want different things, and a second copy can serve the second job at the price of an owner and some lag.
If you remember one thing, make it the order of the questions. First ask whether the jobs clash. Then ask who would own a second copy. Only then ask what to build. In my view, a copy that nobody owns is the likely result of starting with the build.
The next post asks a different question about the first job: where does the app’s copy sit?
3 Questions for Your Architecture Team
- Which of your questions about last month’s orders runs on the same system that takes today’s orders?
- When finance’s number and the app’s number disagree, which copy do you trust, and who keeps it current?
- How would you notice that a long question had started to slow checkout?
Terms in this post
| Term | Meaning in this post |
|---|---|
| Operational work | Recording and reading one order now, while someone waits |
| Analytical work | Asking a question across many past orders |
| OLTP | The industry name for operational work on transactions |
| OLAP | The industry name for analytical work |
| Second copy | The same orders kept for reading across many, not for saving one |
Sources
- E. F. Codd, S. B. Codd and C. T. Salley, “Providing OLAP to User-Analysts: An IT Mandate,” E. F. Codd & Associates, 1993. PDF
- Michael Stonebraker and Uğur Çetintemel, “One Size Fits All: An Idea Whose Time Has Come and Gone,” ICDE 2005. PDF
- TPC, TPC-C homepage. TPC-C was approved in July 1992.
- TPC, TPC Benchmark H Standard Specification, Revision 3.0.1, 28 April 2022.
Leave a Reply