- Published on
The Design of Analytical Data Storage Systems
- Authors
- Name
- Patrik Lager
- The Three Challenges
- The Solution: Layered Architecture
- Data according to System (DAS)
- Data according to Business (DAB)
- Why Common Modeling Approaches Struggle at DAB
- Ensemble Modeling
- Data according to Requirements (DAR)
- Conformity and the Limits of Discipline
- What the Architecture Achieves Together
When we set out to build an analytical data storage system, we face a problem that does not exist in operational software development. An operational system supports specific business processes with well-defined use cases that rarely change. Its design can be optimized for those interactions.
Analytical systems are fundamentally different. Their purpose is to store data that supports analytical data products - reports, dashboards, models, explorations - most of which do not exist yet when the system is built. We are building for unknown requirements.
This matters because humans gather knowledge sequentially. An answered question expands understanding, which surfaces new questions we could not have anticipated. A data product that starts simple can quickly become complex, and the system must accommodate this growth without breaking.
There is a second consequence, and it is what makes analytical systems the harder case. They are changed not only from the outside - when a business process shifts and the system must follow - but from the inside, by their own use. Every question the system answers expands what its users want to ask next, so it continuously generates its own modification requests. The system's own success is the engine that keeps the requirements coming.
The sequential nature of learning has a direct architectural consequence. We cannot design the final system upfront because the people who will use it have not yet formed the questions that will drive its evolution. We need architecture that treats change as the normal state throughout the system's life cycle.
Independent of naming (Data Lake, Lakehouse, Data Warehouse), technology, or delivery pattern, these systems share the same fundamental challenges. If we do not address them with the right architecture and design patterns, the system becomes increasingly difficult to maintain as it grows.
The Three Challenges
Three challenges define the difficulty of building analytical data storage systems. They are closely related: dependencies amplify the impact of changes, and uncontrolled changes multiply complexity.
Dependencies
Analytical systems typically have multiple data sources and multiple consumers building different data products. One data product may need data from several sources; one source may feed several data products. This creates many-to-many dependencies between source and consumption.
Point-to-point integrations - connecting source data directly to data products - create direct dependencies. Any source-side change cascades through every data product that uses it. As the system grows, this web of dependencies grows with it. Each source and data product represents an investment of money and effort, not only to build but to maintain. If a source system gets replaced and the cascade breaks enough data products, the cost of rebuilding can exceed the cost of starting over.
We are not building one data product at a time. We are building a system that must support the creation of many future data products over a life cycle where changes are inevitable. Every source added and every data product built needs to strengthen the system, not make it more fragile.
Changes
Analytical data storage systems are in constant change throughout their life cycle. Changes can be external - source systems change, get replaced, or new ones appear; legislation affects what data we can store and how - or internal: new entities, new relationships, new attributes, new data products, even technology migrations.
The sequential nature of human learning drives much of this internal change. Because the final shape of a data product is rarely known when we start building it, the system must support iterative development.
Whether a change has a small or large impact is not inherent to the change itself - it depends on the architecture and design patterns we choose. A source system replacement can be contained to a single transformation, or it can break every data product. That difference is architectural.
Complexity
A diverse IT landscape, multiple ways of representing, segmenting, aggregating, and calculating data, and the inherent uncertainty about final designs - all of this makes it easy to end up with extremely complex code and data structures. When code becomes so complex that nobody dares touch it, or when new features take so long that users build their own solutions, the system has failed in practice.
The challenge is supporting many data products, each with its own requirements, without letting the complexity of one contaminate the others. When we consider that each data product may itself grow iteratively - one answered question spawning the next - the need for controlled complexity becomes even more pressing.
The Solution: Layered Architecture
The architectural answer to these challenges is to separate concerns into three conceptual layers. They may be implemented as more than three physical layers, but their purpose remains:
- Data according to System (DAS) - data as received from source systems
- Data according to Business (DAB) - data integrated and modeled around business concepts
- Data according to Requirements (DAR) - data shaped for specific analytical use cases
These layers break dependencies, contain changes, and decompose complexity into manageable modules.
Data according to System (DAS)
DAS is where the system takes control of incoming data. Data arrives - through batch files, Kafka topics, API calls - and is stored in its raw format. Sometimes we make it more manageable for the next layer: unnesting JSON into table structures, persisting streaming topics into historized tables. But the representation remains source-system oriented.
This layer often manifests as two physical layers: a raw landing zone and a staging area where data is unpacked into tabular structures for downstream processing.
Two things matter here. First, metadata: registering what data enters the system, when, and how, giving us traceability from producers to consumers across all layers. Second, data quality: DAS is where we detect and profile quality issues as early as possible. The philosophy is forgiving ingestion - accept all data as it arrives - combined with strict unpacking, where structure and quality are enforced through data contracts at the staging boundary. Problems are surfaced early rather than discovered downstream.
DAS does not solve the challenges on its own, but it is necessary infrastructure: the point where data enters the analytical system's control and becomes available for integration. Without a stable, well-documented DAS layer, the business-oriented modeling in DAB has no reliable foundation. DAS also provides reloadability - if transformation logic in DAB changes, we can reprocess from DAS without going back to source systems.
Data according to Business (DAB)
DAB is where the fundamental challenges of dependencies, changes, and complexity are addressed. If any single layer determines whether the system succeeds or fails over time, it is this one.
The Core Principle
The design principle for this layer comes from Bill Inmon's definition in Building the Data Warehouse (1990). Data in this layer should be:
- Subject Oriented - organized around business concepts, not source systems
- Integrated - semantically and structurally source-system agnostic
- Time-variant - historized, so we can track how data has changed over time
- Non-volatile - data is never deleted or updated; every version is retained, ensuring full traceability and auditability. This means maintaining both historized records and current-state views, rather than overwriting previous values
In practice, tables and columns follow a business vocabulary, not a system vocabulary. The DAB layer is structurally and semantically independent of any specific source system.
How This Breaks Dependencies
All data entering from DAS must be mapped and transformed into DAB's business-oriented structures. The rule is that all data in DAR must come from DAB. If that rule is followed, we break the dependency between source and consumption.
When something changes on the source side, we redo or create new transformations from DAS to DAB. Data products in DAR remain unaffected because they consume business-oriented data, not source-system data. The change is contained to one place rather than cascading through every data product. This is Macro Agility - the ability to handle large changes in a controlled fashion.
There are additional benefits. Developers building new data products do not need to understand every source system - only the business-oriented data in DAB, which accelerates development as more context is added over time. We also gain reusability: integration logic is written once in DAS-to-DAB, not repeated for each data product. This reduces complexity and improves consistency - if each data product wrote its own integration logic, the risk of inconsistent numbers would be significant.
Reusability Compounds Over Time
That reusability is not a fixed benefit. It grows over the life cycle, and the way it grows changes what the layered architecture is for. To see it, read the layers in the direction a new requirement actually travels - from the requirement in DAR, down toward DAB and the sources - rather than in the direction data flows.
Early in a platform's life, almost every new data product needs fresh DAS-to-DAB integration, because the business concepts it depends on have not been modeled yet. But each integration done is integration that never has to be done again. Once Customer, Order, and Contract are modeled in DAB, the next requirement that leans on them is served by recombining concepts that already exist, not by reaching back to source. The engineer building it works against the business model, not the source systems behind it.
The share of requirements that need new integration therefore falls as coverage of the business grows, and with it the part of the system any given change has to touch. The locus of change contracts in stages. Early on, a new requirement may touch all three layers: a new source, its integration into DAB, and the DAR product on top. Later, with the source already integrated, it touches only DAB and DAR. In a mature platform, most new questions are answered by a change confined to DAR alone, the rest of the system left untouched.
This reframes what the layered architecture is. It is not only damage control against cascading change; it is an investment that appreciates. Every source integrated enlarges the substrate the next requirement can draw on, so the platform becomes cheaper to extend the more of the business it has already integrated - the opposite of the usual trajectory, in which systems grow more expensive to change as they age.
If this principle is so effective, why have implementations failed? There are multiple reasons, but from an implementation perspective, a clear one stands out: the choice of physical data model in this layer.
The Trade-offs of Layered Architecture
Layered architecture is not free. It introduces upfront design work, requires organizational discipline to maintain layer boundaries, and can feel like unnecessary overhead when the first data product just needs data from one source. For an early-stage company with a single source and no immediate plans for cross-source analysis, starting simpler and migrating later can seem reasonable.
The problem is knowing when to migrate. When the platform is built around point-to-point solutions and the team is in the middle of a backlog, rebuilding according to these principles is a massive investment - and everything invested in the point-to-point solution is effectively wasted. Starting with the right principles from day one may feel like overkill, but it builds experience with the architecture and establishes the right processes from the start. Using a declarative approach reduces the overhead further.
Why Common Modeling Approaches Struggle at DAB
The DAB layer is the focal point of change in the architecture. As the system grows, new context and data are continuously added. We need a modeling approach that supports this growth without becoming rigid or driving complexity.
Star Schema at DAB
Star Schema modeling rose in popularity in the late 1990s and remains widely used. It works well for specific data products in DAR, where its query-friendly structure is a genuine advantage. But it is not optimal for DAB, and the reason comes down to how it handles change.
Star Schema builds broad tables - dimensions with many columns. Any broad table creates problems in a layer that serves as the source for all downstream data products.
Consider adding a new attribute to a dimension in DAB for one specific data product. Multiple other data products use that same dimension but are not interested in the new attribute. Because we changed the loading code, we need to regression test all data products to verify we have not introduced side effects. We have built a dependency on structure and transformation logic that should not exist.
The broader the table, the more complex the transformation code. When loading thirty attributes into one table, the transformation logic for each often has its own joins and conditions. Left Joins and Full Outer Joins accumulate - certain joins only applicable to some attributes, but all required to produce the single broad table. Over time, these pipelines become so complex that nobody wants to touch them. Time to market degrades until the system becomes unmanageable.
3NF at DAB
Third Normal Form is sometimes considered for DAB. While 3NF provides better normalization than Star Schema, it shares a similar challenge: the hierarchical nature of 3NF, where foreign keys cascade through table structures, means that changing the granularity of a relationship can cascade through larger areas of the data model. The rework becomes significant - not only in table definitions but in rewriting transformation code, reloading data, and rewriting all DAR data products to match the changed DAB model.
What we need is non-destructive change: the ability to add new structures and code without modifying what already exists.
Ensemble Modeling
In the late 1990s and early 2000s, several practitioners independently recognized that conventional modeling techniques were not suited to how analytical systems actually change. Three approaches emerged:
- Data Vault by Dan Linstedt
- Focal Framework by Per Olof Eriksson
- Anchor Modeling by Olle Regardt and Lars Ronnback
These share enough in their fundamental approach that Hans Hultgren grouped them under the term Ensemble Modeling in 2014. They all solve the same problem: how to handle continuous change in a modular, non-destructive way.
Three Building Blocks
Ensemble Modeling decomposes the data model into three types of objects, each directly addressing the challenges described earlier:
Core Business Concept - represents an entity and how it is identified. These objects contain only identification and are immutable. They hold no history, no descriptive data, and never need to change once created - the most fundamental representation of an entity is separated from everything else, creating a stable focal point that subsequent additions build on without modifying.
Description - holds descriptive data about a Core Business Concept. Each Description has a direct relationship to one Core Business Concept and maintains its own history. Crucially, each Description is independent: adding a new one does not touch existing Descriptions or their loading pipelines. In contrast with Star Schema, where adding a new attribute means modifying the existing dimension and its loading logic, Ensemble Modeling adds a new Description object alongside existing ones. The transformation code handles only that attribute - a single, focused pipeline.
Association - holds relationships between Core Business Concepts. There are no foreign keys between Core Business Concept objects; relationships are always resolved through Associations. They handle all cardinality types and maintain their own history. Entities are structurally independent within the model, and relationships can be added or extended without touching the entities they connect.
Why This Solves the Challenges
The independence between objects is what makes the approach work. We can model an entity fully without resolving any of its relationships to other entities, and add those relationships later through Associations - without modifying existing structure or code.
Because structures and their associated pipelines are independent, we can load and catch up history without affecting other pipelines. We could implement a new entity, load it with three years of historical data, and later add an Association to an existing entity - all without touching the rest of the model.
This is Micro Agility: the ability to handle small, continuous changes - new attributes, new entities, new relationships - in a modular way that never requires modifying existing structures or code. Combined with the Macro Agility from the layered architecture, the system handles both types of change throughout its life cycle.
A Worked Example
Consider a single new requirement: the business wants to start recording each customer's preferred contact channel.
In a Star Schema DAB, this is a new column on the Customer dimension. It requires an ALTER to a table already in use, a change to the loading job that populates it, and a decision about the history that predates the new column - three modifications to structure and code that other data products already depend on, followed by regression testing to confirm nothing downstream broke.
In Ensemble Modeling, it is one new Description object attached to the Customer Core Business Concept, with its own focused pipeline. Nothing that already exists is touched. The other data products do not see the addition, because their structures and loading logic are unchanged. There is no regression surface, because there was no modification.
The difference is not the effort of this one change. It is that the Star Schema approach has coupled the new attribute to everything that shares the dimension, while Ensemble Modeling has kept it isolated. Repeated across the thousands of such changes a platform absorbs over its life cycle, that difference is what separates a system that stays maintainable from one that does not.
One caveat is worth stating precisely, because the three approaches are often treated as interchangeable on this point and they are not. The additive path - expressing every change as a new structure - is guaranteed to different degrees. Anchor Modeling and the Focal Framework are strict by construction: a new attribute is necessarily a new structure, because there is no broad table to alter. Data Vault is the looser case. Because it groups descriptive attributes into satellites, the same new attribute can be added either as a new satellite, which is additive, or as a column on an existing satellite, which is a modification - the Star Schema problem reappearing inside the supposedly additive layer. Data Vault permits the additive path but does not compel it; whether the model stays additive is left to the modeler rather than guaranteed by the technique.
The Trade-offs
Ensemble Modeling introduces its own costs. The granular structure means significantly more tables than Star Schema or 3NF. Querying DAB directly requires joining across many objects, making it less convenient for ad-hoc exploration (though this is what DAR is for). There is a learning curve: the approach is less intuitive for practitioners accustomed to broad tables, and tooling support varies. Teams should expect an investment in training before the benefits fully materialize. Many automation tools have emerged to address this, and declarative approaches are making it even easier to build with these techniques.
The trade-off is structural simplicity of individual pipelines against a larger number of objects. In practice, the simplicity of each pipeline makes the overall system more maintainable than fewer but more complex pipelines, especially as the system grows.
Data according to Requirements (DAR)
DAR is where we build data products from the integrated data in DAB. There is no prescribed modeling technique or semantic rule. The goal is to deliver direct business value and make data as accessible as possible.
Star Schema, Activity Schema, One Big Table - whatever suits the analytical need and visualization tooling. This is where Star Schema belongs: its query-friendly structure and intuitive design are genuine strengths for making data accessible to business users and BI tools. The broad tables that are problematic in DAB are perfectly appropriate in DAR, where each data product controls its own structures.
Design Principles for DAR
A few rules govern this layer:
Data comes from DAB. All data products in DAR source from the business layer. This preserves the dependency-breaking architecture - if a DAR product reaches back to DAS or a source system, the cascade-of-change protection is bypassed.
No cross-dependencies between data products. Each data product owns its physical structures, and those structures are not reused by other data products. You can have five Customer dimensions in DAR - one for each data product. While this may seem counterproductive, the reasoning is the same cascade-of-change logic: if one data product wants to add context to its Customer dimension, it should not affect the others. Each data product has its own life cycle and rate of change - coupling them reintroduces the dependency problem at the consumption layer.
This runs against the instinct to build conformed dimensions - a single shared Customer dimension that every data product consumes, precisely so that they agree. That instinct is right about the goal and wrong about the mechanism. Consistency does not require a shared physical structure; it requires a shared definition. In this architecture the definition lives in DAB, where Customer is integrated and resolved exactly once. Every per-product Customer dimension in DAR is built from that single definition, so the five dimensions agree on the facts because they descend from the same source of meaning, not because they are the same table. The rule, stated compactly: reuse the definition, never reuse the instance. The conformed dimension buys consistency by sharing structure and pays for it in coupling; with a real integration layer beneath it, that price is unnecessary, because consistency has already been secured upstream where meaning is defined.
Optimize for the consumer. DAR is the place to denormalize, aggregate, pre-calculate - whatever makes the data product effective for its users. The integration complexity has been handled in DAB; DAR focuses purely on serving the requirement.
Trade-offs in DAR
The no-cross-dependency rule means some transformation logic is duplicated across data products. This is a conscious trade-off: we accept some duplication to maintain independence. We can accept this because building an analytical table in DAR is straightforward - all integration logic has been handled in DAB. The alternative - shared structures that create dependencies - reintroduces the very problem the architecture solves.
There is also an organizational dimension. When data products are independent, teams can own and evolve them without coordination overhead. When they share structures, every change requires cross-team negotiation - a bottleneck that slows delivery and frustrates everyone involved.
Conformity and the Limits of Discipline
There is a prior condition underneath everything described so far, and it is where most implementations actually fail. A layered architecture only breaks dependencies if every consumer genuinely routes through DAB instead of reaching past it to a convenient source. An additive model only stays additive if every engineer expresses the next change as a new structure rather than quietly editing an existing one. The architecture names the conforming move; it does not, by itself, perform it.
What keeps a system maintainable is conformity - the same patterns, the same boundaries, the same rules applied the same way every time. A platform whose conventions are followed consistently stays simple enough to reason about; one riddled with one-off exceptions does not, regardless of how sound its architecture looked on the diagram. The gradual loss of maintainability that every long-lived platform risks is, described precisely, what an accumulation of non-conforming exceptions looks like over time.
In most platforms, that conformity is upheld by human discipline: conventions documented in a wiki, layer boundaries maintained by agreement, integration logic kept consistent because reviewers catch deviations and the longest-tenured engineer remembers how each source was handled. This is real, and for a while it works. But it is expensive, uneven, and it erodes. Discipline depends on attention, attention is scarce under deadline pressure, and the knowledge that sustains it lives in individuals who eventually leave. Any engineer, on any deadline afternoon, can introduce the single special case that the next engineer builds on. A standard that exists only as collective good will is a standard with a half-life.
The resolution is not more discipline but less dependence on it. A declarative, metadata-driven approach changes who upholds conformity. The engineer describes what the model should contain - the entities, the identities, the relationships, the meaning - and the conforming structures and loading code are generated from that description, consistent by construction. Consistency stops being something each person must remember to uphold and becomes a property the system cannot casually violate, because no one is hand-writing the code in which a violation would occur. This does not remove the difficult work; it relocates it. The human still owns the judgment of what the data means. What the structure takes over is the mechanical fidelity of applying that judgment everywhere, the same way, throughout the system's life cycle.
What the Architecture Achieves Together
Each layer serves a distinct purpose. DAS takes control of data as it enters the system. DAB breaks the dependency between source and consumption through business modeling, creating Macro Agility for large changes and, with Ensemble Modeling, Micro Agility for continuous small changes. DAR delivers business value through consumption-optimized structures that are independent of each other.
The three challenges are not eliminated; they are contained. Dependencies are broken at the DAB boundary. Changes are localized: large changes to the DAS-to-DAB transformation, small changes to independent Ensemble Modeling objects. Complexity is decomposed: integration logic in DAB, requirement-specific logic in DAR, each pipeline focused on a single concern.
One limit deserves emphasis, because it is easily forgotten once the structure is in place. Structure enforces the consistent application of business definitions; it does not produce the definitions. Deciding what an entity is, when two records refer to the same instance, whether two attributes carry the same meaning - this is human judgment, and neither separation of concerns nor code generation makes it for us. A system that enforces a coherent structure over incoherent definitions produces coherent-looking incoherence, which is more dangerous than visible mess because it is trusted. The effort saved on mechanism is meant to be redirected to meaning, not removed.
No architecture removes the fundamental difficulty of building systems for unknown future requirements. What this architecture does is ensure that the system can grow and change without each addition making the next one harder. The principles behind this architecture date to the 1990s, but they remain relevant because the challenges have not changed. Source systems still change. Requirements still evolve. Complexity still compounds. The specific technologies will continue to change, but the need to separate what systems send us, what the business means by it, and what users require from it - that need is structural and logical, not technological.