Comparisons

Data warehouse vs system of record

A warehouse organizes data for analysis; a system of record governs the accepted state used for a defined decision.

The distinction

A data warehouse integrates data for reporting and analysis. It gives analysts a stable model for measures, dimensions, trends, and comparisons across operational sources. Historical snapshots and slowly changing dimensions can preserve important changes for analysis, provided their definitions are explicit. A warehouse may contain curated facts, but a query result does not automatically show why a source value was accepted or how a disputed value was adjudicated. Refresh cadence and transformations also mean warehouse state can differ from the operational present.

A system of record defines the accepted state for a particular subject and purpose, including how revisions and exceptions are governed. It is suited to transactions, case decisions, and reviews that need one traceable answer at a stated time. This does not make it a replacement for broad analytical modeling: a single governed record may be too detailed or too narrow for trend analysis. The boundary should be explicit, especially when a warehouse-derived metric is used in an operational decision or when a recorded value feeds a historical report.

By dimension

Dimension	Data warehouse	System of record
Primary question	What patterns appear across many records?	What state stands for this subject?
Unit of work	Aggregates, dimensions, and analytical datasets.	Governed subject values and revisions.
Freshness	Depends on ingestion and refresh cadence.	Depends on the record's update and review policy.
History	Can retain analytic snapshots and changing dimensions.	Can retain decision and evidence history.
Conflict	Transformations may normalize or exclude source disagreements.	Disagreements need a disposition or unresolved status.
Evidence	Lineage may lead back to source datasets.	A consequential value should have a reviewable basis.
Main risk	An analytic view can be mistaken for current authority.	A narrow record can be misused as an analytical model.

Choose Data warehouse when

Teams need trends, cohorts, and aggregate reporting.
Data from several systems must be compared on common definitions.
A fixed reporting period matters more than immediate state.

Choose System of record when

An operational action needs an accepted current value.
A reviewer must trace a specific decision and its revisions.
Disputed source facts need explicit governance.

Using both

Feed the warehouse from governed records with effective dates and lineage; preserve the warehouse's transformation and refresh context when reporting those values.

A worked example

A fictional finance team reports quarterly revenue from a warehouse. The warehouse is loaded nightly from the billing system, and a transformation assigns each invoice to a region using a mapping table. Last quarter's figure is quoted in a board paper.

Two months later a customer's region is corrected in the source. The warehouse picks up the change at the next load and, because the mapping table is current, restates last quarter's regional split without any notice. The board paper no longer matches the warehouse. A system of record would have stored the correction as a new fact with its effective date and the date it was entered, so that the question "what did we report to the board on that day" has a fixed answer, and "what is the best current view of that quarter" has another. Both are legitimate, and they must not be confused.

Where each one fails

Warehouses fail on history that is not designed. Slowly changing dimension techniques, such as adding a new row when an attribute changes, preserve some history, but only for the attributes and tables where someone chose to apply them. Transformations hide decisions, and when a number is questioned, reconstructing why it is what it is can take days. The warehouse is also one step removed from the sources, so it can be internally consistent and wrong.

A system of record fails by narrowness. It does not replace a model built for aggregation across many dimensions, and loading a record into analytic tools without its dates and evidence loses what made it valuable. If it takes on every analytical need, it becomes slow to change and hard to govern.

Questions to settle before choosing

Is the question about patterns across many records, or about one value on one date? Who is the audience, and will they challenge a number? Do restatements need to be visible? How long must a past report be reproducible? Which system owns the definition of each measure? Many organizations need both, and the choice is about which one is allowed to answer which kind of question.

How the two meet in a review

The warehouse should be fed by the record, carrying effective dates and lineage with each value, and its reports should state the as-of and recorded-as-of dates they were produced against. When a published figure is challenged, the reviewer asks the record what was known on the report date and asks the warehouse how the figure was computed from it. Kimball's description of the type 2 change, which adds a new row with effective dates rather than overwriting, is a well-known pattern for part of this, and the bitemporal approach extends it.

Related terms

References

Found an error on this page? See corrections for what counts as one, how to report it and the dated log.

Enquiry

Which do you run today: Data warehouse or System of record?

Describe how your organization handles this now and one entity where it falls short. We reply with where a first record would sit alongside it and what it would need from you.

Open a structured enquiry Contact and what happens next

Or write to [email protected] in your own words.