ModelingGENERALSOURCE-SPECIFICSIMPLIFIED

Operational vs Analytical Models

Normalised, transaction-oriented schemas and fact/dimension, query-oriented schemas solve different problems. Neither is a degraded version of the other.

What actually happens How to build it Can I trust it?▶ Run the lab

Who needs this, what one row is, and why the obvious build breaks

Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.

The question

Why is the schema that is correct for an application the wrong one for analytics, and what exactly changes between them?

Who needs this

On the operational side, a request handler that must insert an order and its lines atomically in a few milliseconds. On the analytical side, an analyst scanning two years of those orders. Both are legitimate consumers with opposite requirements, and a schema tuned for either will disappoint the other.

What one row is

Operational rows are at entity grain — one customer, one order, one line — and represent current state. Analytical rows are at process-event grain — one order as it happened, one balance on one day — and represent something that occurred. The same word, orders, means a different unit on each side.

The obvious build

Assume the analytical model is the operational model with fewer constraints: same tables, same keys, indexes dropped, maybe a couple of columns denormalised. Copy nightly, point the BI tool at it, done. This is genuinely a fine first step and many companies never need more.

Why it breaks

The application enforces correctness with foreign keys, unique constraints and transactions. The copy has none of them, so a partial load produces orphan rows that no constraint rejects and every join silently drops (Database Constraints).

How it breaks with real data

The application enforces correctness with foreign keys, unique constraints and transactions. The copy has none of them, so a partial load produces orphan rows that no constraint rejects and every join silently drops (Database Constraints).
The operational schema is designed around UPDATE — an order row mutates through pending, paid, shipped. The analytical question is about the transition, and the transitions were never stored (Event vs Snapshot Modeling).
Normalisation puts each attribute in exactly one place, which is right for writes and means a single analytical question crosses eight tables. Query cost is now dominated by joins that exist to protect a write path that is not being exercised (Joins: INNER, LEFT, RIGHT, FULL, CROSS, SELF).
The application refactors a table for a performance reason that has nothing to do with analytics, and every dashboard breaks, because consumers were coupled to an internal schema that nobody promised would be stable (Data Contracts).
Soft-deleted rows, test tenants, and rows the application filters in code but not in the database all arrive in the copy. The analytical numbers are higher than the product's own admin panel and nobody can say by how much (Multi-Tenancy).

Source→Ingestion→Raw→Transformation→Validation→Storage Model→Serving→Consumer→Observability

What is actually happening

Operational schemas optimise for write correctness under concurrency. Normalisation removes update anomalies — a customer's address stored once cannot disagree with itself — and constraints plus transactions make invalid intermediate states unobservable (Transactions and ACID).
Analytical schemas optimise for read comprehensibility and scan efficiency. There is one writer, running on a schedule, so update anomalies are prevented by the pipeline rather than by the schema, which frees the schema to be redundant where redundancy helps the reader (Denormalization on Purpose).
The access patterns are almost exactly opposite. Operational: point lookups and small ranges by primary key, on current state, at high concurrency. Analytical: full or partitioned scans of large ranges across history, aggregating, at low concurrency and high per-query cost (OLTP vs OLAP).
Because of that, the physical layouts diverge too. Row storage puts an entire entity contiguously, which is what a point lookup wants; columnar storage puts one column's values contiguously, which is what an aggregate over one column across millions of rows wants (Row vs Column Storage).
The deepest difference is about time. An operational system is entitled to forget: it holds current state and, through MVCC, only enough recent history to serve open transactions (MVCC: Multi-Version Concurrency Control). An analytical system is asked what was true in March, which means somebody must have decided in March to record it.

The same business, two shapes

Draw the two schemas next to each other and the difference stops being abstract. On the operational side, entities connected by foreign keys, each attribute in exactly one place, every relationship traversable in both directions. On the analytical side, one central table of measurements and a ring of descriptive tables around it, one join deep.

The operational shape exists so that an UPDATE to a customer's address touches one row. The analytical shape exists so that "revenue by region by month" touches two tables and scans two columns. Each shape is the direct consequence of the operation it is optimised for.

Notice what disappears in the translation. order_status_history, if it exists at all on the left, is a table the application maintains reluctantly. On the right it becomes either a fact attribute, a set of timestamps on an accumulating snapshot, or a separate event fact — and which one you pick determines whether "how long did orders take to ship last quarter" is a query or a project.

Operational entities on the left, analytical facts and dimensions on the right

UserLLMAgentToolDataDecisionHumanGuardrail

What each side optimises, line by line

GENERALThe distinctions hold across engines, but the physical consequences vary: a columnar warehouse punishes SELECT * and rewards narrow scans, while a row-store replica used for analytics shows none of that behaviour and instead shows lock and buffer-pool pressure against the operational workload.

The table below is the one to keep. Almost every argument about whether the warehouse "should" look like production resolves into a row of it, and most of those arguments end once both parties agree which column they are optimising.

The row that surprises people is concurrency. Operational schemas are shaped by the need for many writers to proceed without blocking each other; analytical schemas have exactly one writer, running on a schedule, which removes an entire class of constraint from the design space. Redundancy is dangerous when many writers can update the same fact independently and merely costly when one process rebuilds everything.

The row that matters most is history. It is the only row where one side simply cannot do what the other does, no matter how it is tuned.

Concern	Operational model	Analytical model
Optimised for	Correct writes under concurrency, low-latency point access	Comprehensible queries and efficient scans over history
Redundancy	Minimised — the same attribute in two places can disagree	Accepted deliberately — one writer means it cannot disagree with itself
Typical access	A few rows by primary key, thousands of times a second	Millions of rows, a few columns, a few times an hour
Writers	Many, concurrent, each touching a little	One, scheduled, rebuilding or appending a lot
Integrity	Enforced by constraints and transactions at commit	Asserted by tests after load — declarations are often not enforced
Time	Current state; past values are overwritten	History is the product; past values must have been captured deliberately
Grain	One entity per row, by definition	A declared choice — one order, one line, one user-day — and the whole model depends on it
Schema change	Owned by the application team, driven by product needs	Owned by the data team, driven by consumer contracts, and downstream of the other one
Cost driver	Transactions per second, index maintenance, lock contention	Bytes scanned, bytes shuffled, bytes retained, reprocessing

The translation, in SQL

The translation is not a copy with renames. It makes three decisions, and each of them is a modelling commitment: what one row of the fact will be, which descriptive attributes become dimension columns, and whether those attributes are versioned.

The query below shows the same business question expressed against each side. The operational version is long, correct, and answers "revenue by the country the customer lives in *now*". The analytical version is short, correct, and answers "revenue by the country the customer lived in *at the time of the order*" — and those are different questions that produce different numbers for the same period.

Neither number is wrong. What is wrong is a platform where both are produced, both are labelled "revenue by country", and nothing in either query says which one it is. That is the gap the analytical model exists to close, and closing it costs a valid_from/valid_to predicate that the operational schema has nowhere to put (SCD Type 2 in Practice).

One question, both schemas

1-- OPERATIONAL SCHEMA: normalised, current state only
2SELECT a.country,
3       DATE_TRUNC('month', o.created_at) AS month,
4       SUM(oi.quantity * oi.unit_price)  AS revenue
5FROM   orders          o
6JOIN   customers       c  ON c.id = o.customer_id
7JOIN   addresses       a  ON a.id = c.billing_address_id   -- current address
8JOIN   order_items     oi ON oi.order_id = o.id
9WHERE  o.status <> 'cancelled'
10GROUP  BY 1, 2;
11-- Correct, and it answers: revenue grouped by where the customer lives TODAY.
12-- Re-run it next month after someone moves and March changes.
13 
14-- ANALYTICAL SCHEMA: fact at order grain, versioned customer dimension
15SELECT c.country,
16       d.year_month,
17       SUM(f.revenue) AS revenue
18FROM   fct_orders   f
19JOIN   dim_date     d ON d.date_key = f.date_key
20JOIN   dim_customer c ON c.customer_key = f.customer_key   -- surrogate key
21WHERE  f.order_status <> 'cancelled'
22GROUP  BY 1, 2;
23-- Also correct, and it answers a DIFFERENT question: revenue grouped by
24-- where the customer lived WHEN THE ORDER WAS PLACED, because the fact was
25-- loaded with the customer_key of the version current at that moment.
26-- Re-run it next year and March is unchanged.

The second query has no date predicate on the dimension and still gets point-in-time correctness. That is the whole payoff of surrogate keys plus SCD2: the "as at" logic is resolved once, at load time, instead of by every analyst in every query (Surrogate Keys).

Product detail — verify current documentation

Some engines let you declare primary and foreign keys on analytical tables and use them for join elimination or aggregate rewriting without enforcing them, and a few now offer enforced constraints on certain table types. Whether your engine enforces, ignores or merely trusts a declaration changes what your model can rely on — verify current documentation rather than assuming.

How to build it

Most important first.

Treat the operational schema as a source, not as a model. Land it, stage it, and let consumers depend on your curated models rather than on the application's tables — that boundary is what makes an upstream refactor a staging change instead of an incident (Model Layering).
Re-express entities as facts and dimensions deliberately. An orders table becomes a fact at a declared grain plus keys into dimensions; a customers table becomes a dimension with an explicit history policy (Fact Tables, Dimension Tables).
Reconstruct the transitions the operational schema discards, from the change log rather than from the table. CDC gives you the updates the table has forgotten, which is often the only way to build history retroactively (Change Data Capture).
Re-implement, in the transformation, the invariants the operational database enforced for free: uniqueness on the business key, referential integrity against dimensions, and the application-level filters that live in code rather than in the schema (Data Tests).
Ask the application team which columns are internal implementation and which are business meaning. That conversation costs an hour and prevents the class of breakage where an internal enum gains a value (Enum Evolution: The New Value That Broke Old Clients).

What this actually promises

Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.

The operational database guarantees atomicity, referential integrity and constraint satisfaction at commit. Those guarantees are local to that system and none of them survive the copy unless you rebuild them (Transactions and ACID).
The analytical model guarantees only what its tests assert. Its foreign keys are usually not enforced at all — most analytical engines accept the declaration and do not check it — so referential integrity is a test, not a constraint.
Neither side guarantees that the *meaning* of a column is stable. An operational rename is a schema event you can detect; an operational reinterpretation of the same column is not (Semantic Changes).
The operational model guarantees current state is correct. It explicitly does not guarantee that any past state is retrievable, and reading it as though it did is the single most common analytical error (Slowly Changing Dimensions).

Can I trust it?

A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.

The check that would catch this

Reconcile a closed period end to end: count rows and sum a monetary column in the operational source, compare against the analytical fact table, and alert on any divergence. This is the check that spans the whole translation and catches missing rows, duplicates and filter mismatches at once (Reconciliation).
Add a constraint-parity test: for every uniqueness and foreign-key constraint the operational schema enforces, assert the analytical equivalent. The absence of enforcement is the point — you are reproducing by test what the source got by construction.
Both miss the filters that live in application code. If the product hides orders from internal test accounts in a service layer and the database does not, both sides are internally consistent and the analytical number is still wrong (Business Validation).

Freshness

The operational model is the freshest possible answer by definition — it is the state itself. Every analytical model trades some of that freshness for isolation, history and query shape.
How much freshness is lost is a pipeline decision, not a modelling one. A dimensional model fed by streaming CDC can be minutes behind; the same model fed by a nightly extract is a day behind. The shape is identical (Batch vs Streaming Ingestion).
Freshness expectations differ by consumer, and the mismatch is a support burden rather than a technical one: an operations team used to the admin panel will read a dashboard as if it were live unless the model says otherwise (The Freshness SLO).

When the schema or meaning changes

The operational schema evolves for operational reasons and will do so without telling you. Design for that as steady state: land raw tolerantly, fail loudly at the point of first assumption, and keep the analytical contract separate from the source shape (Schema Evolution).
The translation layer is where compatibility is bought. A source column rename should cost a one-line change in staging and be invisible to every consumer; if it is not, the boundary is in the wrong place (Backward Compatibility).
A change of operational *modelling* — splitting one table into two, moving an attribute to a related entity — is the expensive case, because the analytical model may need history stitched across both shapes to keep periods comparable (Expand and Contract Migrations).

How to re-run this safely

If the operational source is intact and the transformation is deterministic, the analytical model is fully rebuildable. This is why the analytical side can afford to be opinionated: mistakes are re-runs.
The asymmetry is history. The operational side can be restored from a backup to a point in time; the analytical side can be rebuilt only for the state the source currently holds, so attributes the source overwrote are gone from both (Keeping Raw History: The Recovery Position and the Liability).
Rebuilding from CDC history rather than from the current table is what allows a retroactive history build, and it is bounded by log retention rather than by storage (Retention and Replay).

What can go wrong

Failure modes

A copy of the operational schema that consumers query directly, so every production refactor becomes an analytics incident.
Analytical queries running against the primary database because the replica was "temporarily" unavailable, competing with the application for the same resources (Workload Isolation).
Deleted or anonymised source rows changing a historical report that was previously signed off, because the analytical model derives from current state (Deletion Requests).
The transformation reimplementing an application-level business rule slightly differently, so the dashboard and the product disagree by a few percent permanently (Two Dashboards, Two Numbers).
The mitigation failing: a staging layer that was supposed to insulate consumers, but which passes source column names straight through, so the insulation is nominal.

Misreads

"The analytical schema is denormalised, so normalisation was a mistake." Normalisation is correct for the write path and remains correct there. The two schemas coexist because they answer different questions, and a company runs both (Normalization: 1NF to BCNF).
"A warehouse is just a bigger database." It is a different workload with a different storage layout, a different concurrency model and a different relationship to history. The SQL dialect being familiar hides all of that (The Data Warehouse).
"If we do CDC we get history for free." CDC gives you the *changes*; turning changes into queryable history is a modelling decision you still have to make, and make before the changes age out of retention (What a CDC Event Contains).
"Foreign keys in the warehouse protect us." Most analytical engines accept foreign-key declarations without enforcing them, and some use them only as optimiser hints. Assuming enforcement is how orphan keys reach production (Database Constraints).

Operating it

How you see it in production

Row count parity per entity per day, source versus analytical model, plotted together. Divergence localises to a date, which is most of the debugging (Reconciliation).
A schema-drift signal on the source: column added, removed or retyped since the last run. Detecting it at ingestion is far cheaper than detecting it in a dashboard (CDC and Schema Drift).
Lag between the newest source commit and the newest row in the analytical model, per entity, so "is it stale or is it wrong" is answerable in one glance (Freshness Monitoring).

What changes at 10x and 100x

At 10x, extraction strategy changes before modelling does: full-table snapshots stop fitting in the window and incremental or log-based capture becomes mandatory (Incremental Extraction).
At 100x, the two sides diverge physically as well as logically — partitioning, file layout and clustering dominate analytical cost, and none of those concepts exist in the operational schema (Physical Data Layout).
More operational services means more sources, and the analytical model becomes the only place where an entity exists whole. That elevates conformed dimensions from a nicety to the thing holding the platform together (Dimension Tables).

What drives cost here

The operational side pays for the copy: extraction load, or the replication slot and log retention that CDC requires. That cost lands on the system with the tightest latency budget, which is why it is worth measuring (CDC Failure Modes and the Retention Deadline).
The analytical side pays for storage of history the source does not keep, and for the compute that rebuilds models. Both grow with retention rather than with activity (Storage Lifecycle).
The translation layer itself is usually cheap in compute and expensive in engineering attention. It is where the business rules are re-expressed, and rules are the part that needs review rather than optimisation.

What this approach costs

A separate analytical model costs a second schema to maintain, a translation to keep correct, and a permanent gap between what the product shows and what the dashboard shows. It buys isolation, history, and questions that span sources.
Insulating consumers from the source schema costs a layer and some freshness, and buys the ability to absorb upstream changes without a fleet of broken dashboards.
Rebuilding operational constraints as tests costs runtime on every load and buys detection rather than prevention — a test finds the violation after it is in the table, not instead of it.

Modeling lab — one grain, ten questions

Change an input and watch which number moves — and which one does not. Everything here comes from a model in this repository, not from a measurement.

Modeling lab — one grain, ten questions

Pick the grain of the fact table. The questions do not change; what the table can honestly say about them does.

Fact grain

The transaction and its total. The individual products are gone.

Answered

With care

Confidently wrong

Unanswerable

4 of these questions get an answer at this grain that is wrong, and none of them raise an error. That is the whole difficulty: the unanswerable ones announce themselves, and these do not.

Business question	At this grain	Why
What was revenue by country last month? Wants: One order, or one order line — either works, provided the measure is additive at that grain and is not summed twice.	answered	The measure is additive at this grain and each order is counted once.
What is the average order value? Wants: One order. An average over lines answers a different question entirely.	answered	The denominator is orders, which is exactly what one row is.
What was revenue by customer country at the time of each order? Wants: One order, joined to the version of the customer that was current when the order was placed.	WRONG no history	With a Type 1 dimension every historical order is attributed to the customer's current country. A customer moving from Poland to Germany silently rewrites last year's regional reports, and last month's report no longer reproduces.
What is net revenue after refunds? Wants: One order, with refunds either netted into the measure or held as a separate signed fact at the same grain.	answered	Refunds net into the measure, or sit beside it as a signed fact at the same grain.
What was the total account balance on each day last year? Wants: One account-day. A balance is a state, not an event, and cannot be reconstructed by summing transactions unless every transaction since account opening is retained.	WRONG	Summing transactions per day gives the daily change in balance, not the balance. The chart has the right shape and the wrong y-axis.
What is month-three retention by signup cohort? Wants: One user-month of activity, joined to the user's signup month.	WRONG	Users who were active but did not buy are invisible, so retention is understated by exactly the non-buyers.
What share of sessions ended in a purchase? Wants: One session — which requires a session window over events, because no source system emits a session.	unanswerable	No source system emits a session. Without a session window over events there is no denominator to divide by.
Which products are most often bought together? Wants: One order line, with the order key retained so lines can be grouped back into baskets.	unanswerable	The most instructive failure in this lab: the model is not wrong, it is at the wrong resolution, and no query can recover what was aggregated away.
What was yesterday's revenue, asked at 06:00 this morning? Wants: One order, in a period that is not yet closed.	with care	The grain is right and the period is not closed. Orders that happened yesterday and arrive later today are still missing at 06:00.
What was global revenue, across markets that bill in different currencies? Wants: One order, with both the transaction amount and the converted amount stored, plus the rate and the date the rate applied.	WRONG no history	Converting at query time with today's rate makes every historical report change daily. Converting once with no record of the rate makes the number unreproducible. Both pass every type check there is.

answeredThe grain is the thing the question is about.

with careIt works, and there is one specific way to get it wrong.

WRONGIt returns a plausible number that is not the answer, and nothing raises.

unanswerableThe resolution needed was aggregated away. No query recovers it.

SIMPLIFIEDA single fact table against ten questions. A real model has several, and a question that one answers badly another may answer exactly — which is the argument for more than one fact table, not for a finer one.

Where this applies

Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.

GENERALThe split between write-optimised normalised state and read-optimised historical models holds regardless of engine. What varies is how far apart they sit physically — some systems serve both workloads from one store, which changes the operational picture but not the modelling one.
SOURCE-SPECIFICHow much history you can reconstruct retroactively depends entirely on the source: a Postgres WAL or MySQL binlog with sufficient retention lets you rebuild past states, a SaaS API that exposes only current records via REST does not, and a Mongo oplog sits between the two because it may carry only the changed fields.
SIMPLIFIEDPresenting this as two schemas understates it: real platforms have a source schema, a landed raw copy, a staging model and one or more curated models, and the translation is spread across all of them rather than happening at a single boundary.

Where the depth lives

This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.

Databasenormalization denormalization transactions-and-acid mvcc joins relationships-and-keys

Backenddatabase-constraints schema-leakage backend-migrations expand-contract-migration

API Designenum-evolution

Observabilityscan-vs-index-performance join-performance

Domains that do not exist yet

— Distributed Systems owns what a replicated read actually promises, which is what an analytical copy of an operational store inherits when it is fed from a replica rather than from the primary.