Developing a data warehouse is a lengthy and serious matter.
Much in the life of a project depends on how well the object model and database structure are planned from the outset.
The common approach has been, and remains, various combinations of the 'star' schema with the third normal form. Generally, the principle is: source data — 3NF, data marts — star. This time-tested approach, supported by numerous studies, is the first (and often only) thought that comes to an experienced DWH specialist's mind when considering how an analytical warehouse should look.
On the other hand, businesses in general and client requirements in particular tend to change rapidly, while data grows both 'deeper' and 'broader'. Here, the main drawback of the star schema emerges — its limitations. flexibility.
And if in your quiet and cozy life as a DWH developer suddenly:
- the task arose to 'quickly put something together, and we’ll see later';
- a rapidly developing project emerged, with the integration of new sources and a reshaped business model at least once a week;
- a client appeared who has no idea what the system should look like or what functions it should perform in the end but is open to experimentation and gradually refining the desired outcome while moving closer to it;
- the project manager popped in with the joyful news: 'And now we have Agile!'.
Or if you're simply curious about other ways to build warehouses — welcome under the cut!
What does 'flexibility' mean?
To begin, let's determine what properties a system must have to be called 'flexible'.
It’s worth noting that the described properties should pertain specifically to network management system and CI/CD becomes full-fledged configuration versioning., not to the process of its development. Therefore, if you wanted to read about Agile as a development methodology, it would be better to refer to other articles. For instance, there are many interesting materials available here on Habr (like and and ).
This does not mean that the development process and the structure of the Data Warehouse (DW) are unrelated. In general, developing a flexible architecture data warehouse using Agile should be significantly easier. However, in practice, options often occur with Agile development of classical Data Warehouses following Kimball and DataVault methodologies — through a waterfall approach, rather than the fortunate coincidence of flexibility in both of its forms on a single project.
So, what capabilities should a flexible data warehouse possess? Three main points can be highlighted:
- Early delivery and rapid refinement — this means that ideally the first business result (for instance, the first working reports) should be achieved as early as possible, that is, before the entire system has been fully designed and implemented. At the same time, each subsequent refinement should also take as little time as possible.
- Iterative refinement — this means that ideally, each subsequent refinement should not affect already functioning functionality. This aspect often becomes the biggest nightmare on large projects — sooner or later, individual objects start to accumulate so many connections that it becomes easier to completely replicate the logic in a copy alongside than to add a field to an existing table. And if you are surprised that analyzing the impact of a refinement on existing objects can take longer than the refinement itself — you probably have not yet worked with large data warehouses in banking or telecom.
- Constant adaptation to changing business requirements — the overall object structure should be designed not just with potential expansion in mind but also with the expectation that the direction of this next expansion could not have even crossed your mind at the design stage.
And yes, compliance with all these requirements in a single system is possible (of course, in certain cases and with some caveats).
Below, I will explore the two most popular methodologies for flexible data warehouse design — Anchor model and Data VaultMany brilliant techniques remain outside the scope, such as EAV, 6NF (in its pure form), and anything related to NoSQL solutions—not because they are inferior, and not even because the article risks becoming the size of an average dissertation. It’s simply that all this pertains to a somewhat different class of solutions—either to techniques that you can apply in specific cases, regardless of the overall architecture of your project (like EAV), or to fundamentally different paradigms of data storage (such as graph databases and other NoSQL options).
Problems of the “classical” approach and their solutions in agile methodologies
By the “classical” approach, I mean the old reliable star schema (regardless of the specific implementation of the underlying layers, forgive me, followers of Kimball, Inmon, and CDM).
1. Rigid cardinality of relationships
The basis of this model is a clear separation of data into dimensions (Dimension) and facts (Fact). And this, damn it, makes sense—because data analysis in the overwhelming majority of cases boils down to the analysis of specific numeric indicators (facts) across specific dimensions.
At the same time, relationships between objects are established in the form of links between tables via foreign keys. This looks quite natural, but it immediately leads to the first limitation of flexibility— the rigid definition of relationship cardinality..
This means that at the design stage of the tables, you must precisely determine whether each pair of related objects can relate as many-to-many, or only one-to-many, and "in which direction." The primary key in one of the tables and the foreign key in another directly depend on this. Changing this relationship when new requirements arise will likely lead to a reworking of the database.
For example, when designing the object “cash receipt,” you relied on solemn assurances from the sales department that the possibility of one promotion would apply to multiple receipt items (but not vice versa):
Then, after some time, colleagues introduced a new marketing strategy where multiple promotions could apply simultaneously to the same item.And now you need to revise the tables, isolating the relationship into a separate object.
(All derivative objects where the join check is on promo now also require modifications).
Connections in Data Vault and Anchor Model
Avoiding such a situation turned out to be quite simple: don't trust the sales department for this, just store all connections initially in separate tables and process them as many-to-many.
This approach was proposed by Dan Linstedt as part of the paradigm Data Vault and fully supported by Lars Rönnbäck downward API support (simultaneously with this in in the Anchor Model.
As a result, we get the first distinctive feature of flexible methodologies:
Connections between objects are not stored in the attributes of parent entities, but represent a separate type of objects.
In Data Vault Such link tables are called Link, then moving this task to the section Anchor Model — Tie. At first glance, they seem very similar, although their differences are not exhausted by their names (which will be discussed below). In both architectures, link tables can connect any number of entities (not necessarily 2).
This seemingly redundant structure provides significant flexibility during modifications. This structure becomes tolerant not only to changes in the cardinalities of existing connections but also to adding new ones — if now a check line also has a reference to the cashier who punched it, the emergence of such a link becomes just an addition to the existing tables without affecting any existing objects and processes.
2. Data Duplication
The second problem addressed by flexible architectures is less obvious and is primarily characteristic of SCD2-type measurements (slowly changing dimensions of the second type), although not limited to them.
In a classic warehouse, a dimension typically represents a table that contains a surrogate key (as the PK) as well as a set of business keys and attributes in separate columns.
If the dimension supports versioning, the standard set of fields is supplemented with time bounds for the version's validity, and several versions appear in the warehouse for one row in the source (one for each change in versioned attributes).
If a dimension contains at least one frequently changing versioned attribute, the number of versions for that dimension can be substantial (even if the other attributes are non-versioned or never change), and if there are multiple such attributes, the number of versions may grow exponentially with their quantity. Such a dimension can occupy a significant amount of disk space, even though most of the data it contains are merely duplicates of the values of immutable attributes from other rows.
At the same time, denormalization is often applied, denormalization — some attributes are intentionally stored as values rather than references to a directory or another dimension. This approach speeds up data access by reducing the number of joins when accessing the dimension.
As a rule, this leads to the same information being stored simultaneously in multiple places. For example, information about the region of residence and customer category affiliation can be stored simultaneously in the dimensions 'Customer', and the facts 'Purchase', 'Delivery', and 'Calls to the Call Center', as well as in the linking table 'Customer — Customer Manager'.
Overall, the above applies to ordinary (non-versioned) dimensions as well, but in versioned ones, it may have a different scale: the emergence of a new version of an object (especially retroactively) does not just lead to updating all related tables, but to a cascading emergence of new versions of related objects — when Table 1 is used in the construction of Table 2, and Table 2 is used in the construction of Table 3, etc. Even if none of the attributes of Table 1 participate in the construction of Table 3 (but other attributes of Table 2, obtained from other sources, do), versioned updates of this structure will at the very least lead to additional overhead costs and at most to unnecessary versions in Table 3, which is completely 'unrelated' here, and so on down the chain.
3. Non-linear complexity of modifications
At the same time, each new view built based on another increases the number of places where data can 'diverge' when changes are made in ETL. This, in turn, leads to an increase in the complexity (and duration) of each subsequent modification.
If the above applies to systems with rarely modified ETL processes, it is possible to live in such a paradigm — it is sufficient to simply ensure that new changes are correctly reflected in all related objects. However, if changes occur frequently, the likelihood of inadvertently 'missing' several connections significantly increases.
Moreover, considering that a 'versioned' ETL is considerably more complex than a 'non-versioned' one, avoiding errors during frequent modifications becomes quite challenging.
Storing objects and attributes in Data Vault and Anchor Model
The approach proposed by the authors of flexible architectures can be summarized as follows:
It is necessary to separate what changes from what remains unchanged. In other words, store keys separately from attributes.
At the same time, it is important not to confuse non-versioned attributes with unchangeable: the former does not store the history of its changes but can change (for instance, when correcting input errors or receiving new data), while the latter never changes.
Opinions on what can be considered unchangeable in Data Vault and Anchor models vary.
From an architectural perspective Data Vault, unchangeable can be considered the entire set of keys — natural (e.g., organization INN, product code in the source system, etc.) and surrogate. At the same time, other attributes can be divided into groups by source and/or frequency of changes and for each group, maintain a separate table with an independent set of versions.
In the paradigm of Anchor Model , only the surrogate key of the entity is considered unchangeable. Everything else (including natural keys) is merely a specific case of its attributes. Moreover, all attributes are by default independent of each other , thus aseparate table must be created for each attribute. Tables containing entity keys are called.
In Data Vault Hubs. Hubs always contain a fixed set of fields:Natural entity keys
- Surrogate key
- Link to the source
- Time of record addition
- Records in Hubs
are never changed and do not have versions. never change and have no versionsExternally, hubs resemble ID-map tables used in some systems for generating surrogates. However, in Data Vault, it is recommended to use a hash of the set of business keys instead of an integer sequence as surrogates. This approach facilitates loading relationships and attributes from sources (there's no need to join on the hub to obtain a surrogate; it's enough to calculate the hash of the natural key), but it may introduce other issues (related, for example, to collisions, case sensitivity, and non-printable characters in string keys, etc.), which is why it's not universally accepted.
All other entity attributes are stored in special tables called Satellites (Satellit). A single hub can have multiple satellites that store different sets of attributes.
The distribution of attributes across satellites is based on the principle of joint evolution — one satellite may hold non-versioned attributes (for example, birth date and SNILS for individuals), another may store rarely changing versioned attributes (such as last name and passport number), and a third may contain frequently changing ones (like delivery address, category, last order date, etc.). Versioning is managed at the level of individual satellites rather than the entity as a whole; thus, it is advisable to distribute attributes in such a way that the overlap of versions within a single satellite is minimized (which reduces the total number of stored versions).
Additionally, to optimize the data loading process, attributes obtained from different sources are often extracted into separate satellites.
Satellites are linked to the Hub via foreign key (which corresponds to a cardinality of one-to-many). This means that multiple attribute values (for example, several contact phone numbers for a single client) are inherently supported by this architecture.
In Anchor Model tables that store keys are referred to as Anchors (Anchor). And they store:
- Only surrogate keys
- Time of record addition
- Records in Hubs
Natural keys, from the perspective of the Anchor Model, are considered ordinary attributes. This option may seem more complex to understand, but it allows for much greater flexibility in identifying an object.
For instance, if data about the same entity can come from different systems, each using its own natural key, this can lead to quite bulky constructions of several hubs (one for each source plus a unifying master version) in Data Vault. In the Anchor model, however, the natural key of each source is included in its attribute and can be used during loading independently of all others.
But here lies a tricky point: if attributes from various systems are combined in one entity, there are likely certain merging rules, according to which the system must understand that records from different sources correspond to one instance of the entity.
In Data Vault These rules will most likely determine the formation of the surrogate hub of the master entity and will not affect the Hubs that store the natural keys of the sources and their original attributes. If at any point the merging rules change (or an update of the attributes used for merging comes), it will be enough to reformat the surrogate hubs.
In In the Anchor model , such an entity will most likely be stored in a single anchor. This means that all attributes, regardless of the source they come from, will be tied to the same surrogate. Separating mistakenly merged records and generally tracking the relevance of merging in such a system can turn out to be significantly more difficult, especially if the rules are quite complex and frequently change, and the same attribute can be obtained from different sources (although it is certainly possible, since each version of the attribute retains a link to its source).
In any case, if your system anticipates the implementation of functionality for deduplication, merging of records, and other MDM elements, it is worth paying particular attention to the aspects of storing natural keys in flexible methodologies. The more cumbersome Data Vault structure may unexpectedly prove to be safer in terms of merging errors.
The Anchor model also provides for an additional type of object called Knot , essentially this is a special degenerate form of an anchor., which can contain only one attribute. Nodes are intended to hold flat reference data (e.g., gender, marital status, customer service category, etc.). Unlike Anchors, a Node does not have related attribute tables., and its only attribute (name) is always stored in the same table with the key. Nodes are linked to Anchors through relationship tables (Tie), just as anchors are linked to each other.
There is no consensus on the use of Nodes. For instance, , who actively promotes the use of the Anchor model in Russia, believes (not without reason) that no reference data can be definitively claimed to be recommendations only static and single-level, so it is better to use a full Anchor for all objects from the start.
Another important difference between Data Vault and the Anchor model lies in the presence of attributes for relationships.:
In Data Vault Relationships are just as complete objects as Hubs, and they can have their own attributes.. In In the Anchor model Relationships are used solely to connect Anchors and cannot have their own attributes.. This distinction results in significantly different modeling approaches for facts, which will be discussed further.
Fact Storage
Prior to this, we mostly discussed dimension modeling. The situation with facts is somewhat less clear-cut.
In Data Vault A typical object for storing facts is a Link, in the Satellites of which physical measures are aggregated.
This approach seems intuitively understandable. It provides straightforward access to analyzed metrics and generally resembles a traditional fact table (only the metrics are stored not in the table itself, but in a 'neighboring' one). However, there are pitfalls: one of the typical enhancements to the model—extending the fact key—necessitates adding a new foreign key to the Link.. This, in turn, 'breaks' modularity and potentially necessitates modifications to other objects.
In In the Anchor model A Link cannot have its own attributes, so this approach will not work—all attributes and measures must be tied to one specific anchor. The conclusion is simple— each fact also needs its own anchor.For part of what we perceive as facts, this may seem natural — for example, the fact of a purchase is nicely reduced to the object 'order' or 'receipt', a website visit translates to a session, and so on. But there are also facts for which finding such a natural 'object-carrier' is not so straightforward — for instance, stock levels in warehouses at the start of each day.
Consequently, there are no issues with modularity when expanding the fact key in the Anchor model (it is sufficient just to add a new Relation to the corresponding Anchor), but designing the model for displaying facts is less straightforward; 'artificial' Anchors can emerge that do not clearly represent the business object model.
How flexibility is achieved
The resulting structure in both cases contains significantly more tables, than traditional measurements. But it can occupy significantly less disk space with the same set of versioned attributes as traditional measurements. There is, of course, no magic here — it all comes down to normalization. By distributing attributes across Satellites (in Data Vault) or separate tables (Anchor Model), we reduce (or completely eliminate) the duplication of the values of some attributes when others are changed..
For Data Vault The gain will depend on how attributes are distributed across Satellites, and for In the Anchor model — it is practically directly proportional to the average number of versions per measurement object.
However, the gain in space occupied is important, but not the main advantage of separate storage of attributes. Along with the separate storage of relationships, this approach makes the warehouse a modular construct. This means that the addition of both individual attributes and entirely new subject areas in such a model appears as an extension over the existing set of objects without altering them. And this is precisely what makes the described methodologies flexible.
This also resembles the transition from unit production to mass production — if in the traditional approach each table of the model is unique and requires individual attention, in flexible methodologies, it is already a set of standard 'parts'. On one hand, the number of tables increases, and the processes of data loading and retrieval seem more complex. On the other hand, they become standardized. Thus, they can be automated and managed by metadata. The question of 'how will we lay it out?' which could have taken a significant portion of the design work for modifications, is simply not an issue anymore (just like the question of how changes to the model will affect the working processes).
This doesn’t mean that analysts are completely unnecessary in such a system — someone still needs to work through a set of objects with attributes and figure out where and how to load everything. However, the volume of work, as well as the likelihood and cost of errors, are significantly reduced. Both during the analysis phase and when developing ETL, which can largely boil down to editing metadata.
The dark side
Everything described above makes both approaches truly flexible, technological, and suitable for iterative refinement. Of course, there is also a 'barrel of tar,' which I think you are already suspecting.
The decomposition of data underlying the modularity of flexible architectures leads to an increase in the number of tables and, consequently, overhead for joins during queries. To simply retrieve all the measurement attributes, a single select is sufficient in a classic warehouse, while a flexible architecture will require a whole series of joins. Moreover, while these joins can be pre-written for reports, analysts accustomed to writing SQL by hand will face double the burden.
There are several facts that ease this situation:
When working with large dimensions, almost never are all its attributes used simultaneously. This means that there may be fewer joins than it seems at first glance. In Data Vault, the expected frequency of co-use can also be taken into account when distributing attributes across satellites. Meanwhile, the Hubs or Anchors are primarily needed for generating and mapping surrogates during the loading phase and are rarely used in queries (especially in the case of Anchors).
All joins are by key. Moreover, a more "compressed" way of storing data reduces the overhead for scanning tables where necessary (e.g., when filtering by attribute value). This may result in querying a normalized database with many joins being faster than scanning a single large dimension with many versions per row.
For example, in the article, there is a detailed comparative performance test of the Anchor model against a single table query.
Much depends on the engine. Many modern platforms have internal mechanisms for optimizing joins. For instance, MS SQL and Oracle can "skip" joins to tables if their data is not used anywhere except in other joins and does not affect the final selection (table/join elimination), while MPP Vertica has, according to colleagues from Avito, Furthermore, there are
specific techniques that facilitate data access (both in terms of query performance and for end users). For example,Point-In-Time tables in Data Vault or special table functions in the Anchor model. The essence of the discussed flexible architectures lies in the modularity of their "construction."
Total
It is this property that allows:
After some initial setup related to deploying metadata and writing basic ETL algorithms,
- to quickly deliver the first results to the customer in the form of a couple of reports containing data from just a few source objects. It is not necessary to fully think through (even at a high level) the entire object model for this. The data model can start to work (and provide benefits) with just 2-3 objects, and then
- gradually grow (regarding the Anchor model, Nikolai made a Most modifications, including expanding the subject area and adding new sources,
- do not affect existing functionality and do not carry the risk of breaking anything that already works. does not affect existing functionality and does not pose a risk of breaking something that is already working.
- Thanks to the decomposition into standard elements, ETL processes in such systems appear uniform, their writing is amenable to algorithmization and ultimately, automation.
The price of such flexibility is performance. This doesn't mean that achieving acceptable performance with such models is impossible. Most often, you may just need to put in more effort and attention to detail to achieve the desired metrics.
Applications
Entity types Data Vault
Learn more about Data Vault:
Types of entities Anchor Model
Learn more about Anchor Model:
Summary table of common features and differences of the discussed approaches:
Source: habr.com
