Operational Data vs Analytical Data

The Number That Changed Itself

In chapter 1 we left a Monday meeting with 3 failed-claim counts — 214, 187, 243 — and the uncomfortable news that all 3 were correct. This chapter starts with a quieter horror. Take the 187, the client-MIS number, the one everyone eventually agreed to report. 3 weeks after month-end, someone reran the exact same query, byte-for-byte, no edits to the code and no edits to “last month's data.” It returned 172.

Nobody touched last month. That's the point. There is no “last month” in the system that produced the number — only now, and now had moved. Between month-end and the rerun, 15 claims sitting in a failed state were reopened, reprocessed, and overridden to success by QC staff, and the operational database did exactly what it is built to do: it updated each row to reflect current reality, silently rewriting history a client contract treated as settled. The June number was not wrong in June and wrong in July. It was a photograph of a river.

Here is the mental shift this chapter demands, and experienced engineers resist it hardest, because they have spent careers being right about databases: a store that is completely correct for running your business can be completely unfit for explaining it, and no amount of SQL closes the gap, because the gap is not in the queries — it is in what the data is for. The production store answers “what is true right now, so I can run the next step.” The business is asking “what happened over June, and will the same question return the same answer in September.” Different jobs, wanting different data — shaped, kept, and owned differently. Ask one store to do both and it does the second badly, mutating the evidence while you watch.

This chapter is about that split — operational versus analytical data — and the phrase that papers over it. Every organization says “source of truth” as if it names one thing. The moment you have a system that runs the business and a system that reports on it, “source of truth” fractures into two questions with two answers, and knowing which is which for a given number is most of the job. We walk from the beginner's picture of the two planes, through the failure modes a senior engineer meets on-call, to the principal's question of where the boundary belongs and who pays to keep it.

The Problem: Two Sources, One Phrase

The trap: “source of truth” sounds like a location. It is actually a responsibility, and it can only sit in one place per fact.

Start with the beginner mental model, because it is the one everyone nods along to and then violates by lunchtime.

An operational system is optimized for a demanding job: accept a claim, assign it, capture the OCR output, record an override, close the case — thousands of times an hour, each a small transaction that must be correct, isolated, and fast. To do this it keeps current state: one row per claim, overwritten when the claim changes, because yesterday's value isn't needed to process today's step. The schema is shaped like the workflow — cases, assignments, documents, a status column enumerating the states a case can be in. This is not a flaw. A store that kept every historical version inline and modeled business concepts instead of workflow steps would be slower and more fragile at the one job it exists to do.

Analytical data has the opposite priorities. It wants history — every state a claim passed through and when, not just what it is now. It wants to aggregate across millions of rows without touching the system busy processing them. It wants reproducibility: the June number computed today and computed in September should match, or differ only for a reason someone can name. And it wants to be shaped like the business — clients, SLAs, failure reasons, turnaround — not like the application's state machine.

Now the phrase. “Just get it from the source of truth” means the operational database, because that is where reality is entered. But the operational database is the source of truth for current state — is this case open, who holds it, what is its status this second. It is emphatically not the source of truth for what happened in June; it has already forgotten. The analytical plane, if you build one, becomes the source of truth for history and business definitions. Two stores, two authorities, and the discipline of this chapter is refusing to let them blur. The production database knows what happened so it can run the workflow; the analytical platform knows what happened so the organization can reason about it.

The Failure Gallery, Continued

Chapter 1's failures were about disagreement — the same metric in 4 places. This chapter's are subtler and sometimes worse: they happen even with one query, one dashboard, one diligent analyst. They come from asking operational data to be analytical.

The Number That Changed Itself

We have met it already. Current-state-as-truth is computing a historical metric directly against mutable operational rows. The query is correct; the data is correct. But “correct” in an operational store means “reflects reality now,” while a historical report needs “reflects reality as of a past instant.” Because the store overwrites, every rerun silently re-photographs the current river and calls it June. Good engineers ship this because it works in the demo and for about 3 weeks after — until someone reruns an old report and the number has moved, with no error and no failed job to announce it. Appendix A.1 shows the two answers 3 weeks apart; nothing in it looks wrong, which is the problem. Insurance produces this constantly, because claims are supposed to change after they close: a case can be operationally closed yet analytically alive, still owed a reopened dispute, a late override, or an audit correction. The operational store has no reason to keep the pre-override value; the contract, the SLA report, and next quarter's audit all assume it did.

The Column Rename That Broke Finance

Source-schema coupling is what you get when analytical models are built directly on operational table shapes: the analytical layer reads cases.status, joins documents the way the app does, and inherits the operational schema as a de facto contract the operational team never agreed to and doesn't know exists. Then the product team does something reasonable — splits status into workflow_state and qc_state because the enum had come to mean 4 things (chapter 1's ambiguity), renames a column, normalizes a table in two. Healthy refactoring from their side; from the analytical side, 3 dashboards break, or worse keep running on the old join and go quietly wrong. The failure is structural, not careless: two teams that should move independently were welded together through a schema meant to be the product's private business.

This is the deeper cousin of chapter 1's replica-as-warehouse problem. Pointing BI at the read replica felt like architecture but bought two couplings at once — reports to the product schema (this failure) and analytical load to OLTP capacity (the next). The replica is where both are usually discovered together, because it is the shortcut that creates both.

When the Report Slows the Product

The workload version. Operational queries are short, indexed, latency-sensitive; analytical queries are long, scan-heavy, bursty, reading millions of rows for one aggregate. On the same engine they fight: the scan evicts the operational working set from cache, holds locks, or saturates I/O, and now checkout is slow or claim assignment lags because someone ran a quarterly report. The cruel “fix” is an index added to the production schema to speed a report — optimizing the system that runs the business for the one meant to understand it.

Three failure modes, one root: the two planes have different physics, and every attempt to make one store serve both jobs pays for it somewhere — in mutated history, coupled teams, or a product slowed by its own reporting.

Building the Boundary

So you separate them — deliberately, instead of letting an analytical plane accrete out of replicas and CSVs. Three ideas do most of the work, and two are borrowed: this atlas is not the first to notice that the thing which runs a system and the thing which reads it want to be different.

Workload isolation is the least glamorous and comes first: the analytical store is a separate system, so a scan can never evict a checkout and a schema refactor becomes a negotiation rather than an ambush. Isolation buys the product team freedom to change and the analytics team freedom to run heavy queries — the two freedoms coupling destroys — and, not coincidentally, gives history somewhere to live, since the analytical store can keep every version of a row the operational store overwrites.

The second idea reframes the relationship and bridges to how large product systems already think: the analytical plane is a projection of operational truth — a read model derived from the operational write model, exactly as the B2C systems atlas develops it under CQRS. The operational store owns the authoritative writes; the analytical store is a derived view, free to be shaped completely differently because it is downstream, not authoritative. See it as a projection and “why did the number change” stops being a mystery: a projection is only as good as the change stream feeding it, and if that stream is “whatever the current-state table says today,” the projection has no memory.

Which is why the transport matters. Something must carry operational change across, and the honest options are a periodic extract — simple, stale, and blind to anything that happened and reverted between runs — or change data capture, reading the database's own transaction log to stream every committed change as it happens, the same CDC the B2C atlas uses to keep read models current. CDC matters here because it sees transitions, not just end states — the raw material history is made of. Building it is chapter 5; where the data lands (warehouse, lake, lakehouse) is chapter 9. The architectural point: the analytical plane is fed by a stream of operational facts, and the quality of your history is decided at that boundary.

The figure's annotation is the third idea: system of record. For every business fact, exactly one store is authoritative and the other derived — and confusion is guaranteed until you can say which. The operational store is the system of record for current state (the live status of a case, who holds it now); the analytical store is the system of record for history and business definitions (what the failure rate was in June, what “failed claim” even means). Draw the line and disputes get short: the current queue is an operational-store question, last quarter is an analytical-store question, and a historical number read from the current-state store is simply a bug you can now name.

The Tradeoff Worth Arguing About

The one hard decision this chapter forces is copy versus couple, and it has no universal answer.

Couple means answering the analytical question against operational data more or less in place — query the replica, read the source tables. Fresh to the second, no pipeline; you pay in the couplings above (reports break on schema change, heavy reads threaten the product). Copy means moving operational data into a separate analytical store on some cadence and answering there. You get isolation, reproducibility, and a home for history; you pay in staleness, in the cost of a pipeline to build and operate, and in a subtler risk — the copy can be wrong, diverging from the source until reconciliation catches it.

The decision hinges on what the question is for, not on which is more modern. A near-real-time SLA-breach alert — “this case breaches in 20 minutes, act now” — is an operational decision in an analytical costume; it wants fresh data close to the source, and a nightly copy would make it useless. A client's month-end MIS wants isolation and a frozen, reproducible history, and answering it off the live store is how you get a number that changes itself. Same organization, same claims data, opposite correct answers. The split between operational and analytical is a split between jobs, not a wall between systems — a mature platform serves some analytical jobs close to the operational plane on purpose. The failure is not “querying the source”; it is querying the source for a job that needed history and isolation, or building a pipeline for one that needed the current second.

What the Companies Actually Live With

The insurance-claims platform is the cleanest teacher because its data is supposed to keep changing after the fact, which makes the split unavoidable rather than optional. A claim's operational life is a state machine — received, assigned, digitized, QC'd, overridden, closed — and the operational store holds where each claim is now, which is what the ops floor needs to assign the next case. But nearly every number the business sells to clients is historical and definitional: turnaround time (the sequence of timestamps, not the current one), failed-claim counts as of a contractual month-end (which must not move when a claim is reopened), override rates (which need the pre-override value the row already discarded), quarter-long productivity. None can be computed correctly from current state — the one thing the operational store keeps, and the one thing history is not. An analytical plane that captures every transition, one immutable row per state change, turns all of them from “fragile or impossible” into “a query,” without a scan ever touching the machine that assigns cases.

The large B2C product tells the same story in consumer clothes, and complicates identity in a way claims does not. The operational order table is a workflow object — cart, payment, shipment, refund, cancellation — mutated in place as the order progresses. The business wants revenue, GMV net of refunds, cohort retention, funnel drop-off, every one historical and needing the order's journey rather than its final row. And identity: a user who reinstalls or logs in on a new device may become a new operational identity, because the operational system only needs to serve this session. Compute retention off that current-state identity and you report churn that is really a reinstall and growth that is really the same people counted twice. The operational store is behaving perfectly; it never promised the continuity “retention” assumes. That promise is made and kept on the analytical side.

The complication worth sitting with — the example that argues against a naive reading of this chapter — is the fraud-and-operations desk, at a payments company or a claims floor alike. These teams run real analytical workloads (patterns across many events, anomaly detection, live dashboards) against data that must be seconds fresh, because the decision is operational: block this transaction, escalate this case, now. The textbook “copy everything to the warehouse nightly” is simply wrong for them; mature platforms build a fast path near the operational plane for these jobs and reserve copy-and-isolate for reproducible reporting. If you take one thing from the companies, let it be that the divide is a statement about the question — the same raw data can sit on both sides at once.

The Principal Engineer's View

The Operating Model, Briefly

Exercises

First, the reproducibility probe: take one historical number your organization published last quarter and rerun today the exact query that produced it. If it moved, you have found current-state-as-truth in the wild; now trace which rows mutated and whether any consumer relied on the old value. If it didn't move, find out why — is history actually preserved, or has nothing simply changed yet? The second answer is scarier than the first.

Second, the coupling audit: pick your most-used analytical dashboard and list every operational table or column it depends on. Then go ask the team that owns those tables whether they know your dashboard exists. The gap between “tables you depend on” and “dependencies they know about” is your source-schema-coupling exposure, and it is usually most of the list.

Third, the boundary map: choose 3 business facts your organization argues about — say “revenue,” “active user,” “failed claim.” For each, write down which single store is the system of record and whether it should be the operational or analytical plane. Where you can't decide, or where two stores both claim it, you have found a boundary that was never drawn. Don't resolve it yet — chapters 3, 10, and 16 give you the tools. Just notice that an AI asked to “define active user” will answer confidently in 4 seconds and skip the only hard part, which is deciding who owns the answer and where it lives.

Connections

Backward, this chapter cashes a cheque chapter 1 wrote: the “two planes” it sketched now have their physics, and its replica-as-warehouse failure is revealed as two couplings (schema and workload) that this chapter separates. The data platform of chapter 1 exists, in large part, to build and defend the boundary this chapter draws; the trust it promised depends on being able to say which plane owns which number.

Forward, the analytical plane raises immediately the question chapter 3 answers — raw, clean, trusted — because a projection of operational exhaust is not automatically trustworthy just for being separate. How operational change crosses the boundary is chapter 5 (ingestion) and the change-data-capture it leans on; where the analytical data lands is chapter 9 (lakes, warehouses, lakehouses); how it gets shaped into business meaning is chapter 10 (data modeling); and the failure that haunted this whole chapter — history that moves, timestamps that lie — gets its full treatment in chapter 11 (time, history, and change), which you should now be slightly afraid of. The contract at the boundary, the thing that turns coupling into negotiation, is chapter 12.

Appendix A: Reference Implementations

A.1 — The same query, three weeks apart

One correct query against a current-state operational table, run at month-end and again 3 weeks later. Nothing changes but the calendar, and the answer moves because the rows moved underneath it.

-- Failed claims for a client in June 2026, computed against the live operational table.
-- Run on 2026-06-30 it returns 187. Run on 2026-07-21 it returns 172.
-- No code changed. 15 June claims were reopened and overridden to success in July,
-- and the operational store overwrote their status in place.
SELECT COUNT(*)
FROM cases                          -- current-state table: one row per case, mutated in place
WHERE client_id = :client
  AND status = 'DIGITIZATION_FAILED'
  AND received_at >= '2026-06-01'
  AND received_at <  '2026-07-01';
-- The query asks a historical question ("how many failed in June?")
-- of a store that only knows the present ("how many are failed right now, among June arrivals?").
-- Those are different questions. The store answers the one it can, silently.

The fix is not a better WHERE clause; it is asking the question of a store that kept June's state as of June — an analytical plane fed by transitions, which A.2 sketches.

A.2 — Current state vs an append-only history

The same claim, represented two ways: the operational row that overwrites, and the analytical fact log that appends. Only the second can answer “as of a past instant.”

-- OPERATIONAL (system of record for CURRENT STATE): one row, overwritten on every change.
-- After the July override, the pre-override truth is simply gone.
UPDATE cases
SET status = 'OVERRIDDEN_SUCCESS', updated_at = now()
WHERE case_id = 'C-3391';           -- yesterday's 'DIGITIZATION_FAILED' is now unrecoverable here

-- ANALYTICAL (system of record for HISTORY): append-only, one immutable row per transition.
-- 'As of month-end' is answerable because nothing is ever overwritten.
INSERT INTO case_status_events (case_id, client_id, from_status, to_status, event_at, recorded_at)
VALUES ('C-3391', 'ACME', 'DIGITIZATION_FAILED', 'OVERRIDDEN_SUCCESS',
        '2026-07-18 11:04:00+05:30', now());

-- June's number, reproducibly, from the history:
--   count claims whose LAST transition on or before 2026-06-30 left them in a failed state.
-- Re-run in September and it returns the same value, because the past is not stored as "now".

Note what the analytical shape costs and buys: more rows, an ingestion path to keep it current (chapter 5), and a modeling decision about grain (chapter 10) — in exchange for a June that stays June. The two recorded_at vs event_at timestamps are the seam where chapter 11 will live; that they differ at all is the whole reason history is hard.

Next: Chapter 3 — Raw Data, Clean Data, Trusted Data: why a separate analytical plane, even a clean one, still isn't trustworthy — and what the ladder from raw evidence to certified truth actually has on each rung.