Skip to main content
search
0
CartClose Cart
In Data Vault

Data Vault 2.0: Best of Breed from Data Warehousing and Data Lakes

Image of the structure from Data Vault

There are two competing approaches to data analytics available in the industry, and most professionals at least tend to one or the other as the preferred tool of choice: data warehousing vs data lake. This article sheds light on the differences between both approaches and how Data Vault 2.0 provides a best of breed solution that integrates the advantages of both approaches into a unified concept.

About Data Warehousing

Data Warehousing is the traditional enterprise solution for providing reliable information to decision makers at every level of the organization. Data warehouse solutions (but also data lakes) are based on a data model, which is traditionally defined on the basis of either the information requirement in a bottom-up approach or in a top-down approach based on an integrated enterprise information model.

In any case, the traditional data warehouse is based on a concept called “schema-on-write” where the data model is established when loading the data into the data warehouse. This often leads to non-agile data processing, as this data model often requires modifications in order to cope with changes in the business.

About Data Lakes

Data lakes, on the other hand, are based on the “schema-on-read” concept. Instead of modeling the enterprise or fitting the incoming dataset into a target information model, the data is first and foremost stored on the data lake as delivered without any modelling applied. 

While traditional data warehousing often leads to overmodeling and non-agile data analytics, the data lake approach often leads to the direct opposite: to unmanaged data and inconsistent information results.

The Best of Breed

Both approaches are on the extreme ends of the data analytics space and used throughout the years with mixed results. With the emergence of the Data Vault 2.0 concept, a third option is available to industry professionals to build data analytics platforms. 

Data Vault 2.0 is a best of breed between traditional data warehousing and data lakes: for example, there is a data model to manage the data and business logic as in traditional data warehousing, but it follows a schema-on-write approach as in data lakes. 

The Data Vault 2.0 architecture comprises multiple layers:

Image of the structure from Data Vault

The first layer is the staging area: it is used to extract the data from the source systems. The next layer is the Raw Data Vault. This layer is still functionally oriented as the staging layer, but integrates and versionizes the data. To achieve this, the incoming source data model is broken down into smaller components: business keys (stored in hubs), relationships between business keys (stored in links) and descriptive data (captured by satellites). 

 

The Business Vault is the next layer, but only sparsely modelled: only where business logic is required to deliver useful information, a Business Vault entity is put in place. The Business Vault bridges the gap between the target information model (as in the next layer) and the actual raw data. Often, the raw data doesn’t meet the expectations of the business regarding data quality, completeness, or content and thus needs to be adjusted. Business logic is used to fill the gap.

The final layer is the information mart layer where the information model is produced to deliver the final information in the desired format, e.g., a dimensional star schema. This model is used by the business user either directly in ad-hoc queries or using business intelligence tools such as dashboarding or reporting software.

The first layers until the Raw Data Vault are still functionally oriented because the model is still derived either directly from the source system (as in the staging area) or by breaking down the incoming data model into smaller, normalized components, as in the Raw Data Vault. The target schema is only applied at the latest layer, the information mart layer. This is when the desired information model is applied. Because the information mart is often virtualized using SQL views, the target schema is actually applied during query time. Queries against the view layers are merged with the SQL statements inside the view layer and run against the materialized tables in the Raw Data Vault, the actual data. Therefore, the schema-on-read concept is used in Data Vault 2.0.

Data Vault 2.0 also preserves the agility: the concept has demonstrated in many projects that it is easy to extend over time when either the source system structures change, the business rules change or the information models need to be adjusted. In addition, it is easy to add new data sources, additional business logic and additional information artifacts to the data warehouse. 

On top of that, the Data Vault 2.0 model is typically integrated with a data lake: the above diagram shows the use of a data lake for staging purposes, which is the recommended “hybrid architecture” for new projects at Scalefree. But the data lake can also be used to capture semi-structured or unstructured data for the enterprise data warehouse or to deliver unstructured information marts.

With all that in mind, the Data Vault 2.0 concept has been established itself as a best of breed approach between the traditional data warehouse and the data lake. Organizations of all sizes use is to build data analytics platforms to deliver useful information to their decision makers.

In Data Vault Friday

Managed Self-service Monitoring in Data Vault

Watch the Video

In our ongoing Data Vault Friday series, our CEO Michael Olschimke engages with a pertinent question posed by our audience.

“Why is monitoring important in Managed Self-Service BI?”

In this insightful video, Michael delves into one of his favorite topics – managed self-service BI. He expounds on the significance and role of monitoring within the context of managed self-service BI scenarios. Drawing from his expertise, Michael provides a comprehensive exploration of the uses, values, and critical importance of monitoring tools in ensuring the effectiveness and efficiency of managed self-service BI implementations.

For those keen on optimizing their self-service BI initiatives and understanding the practical applications of monitoring, this video offers valuable insights and considerations.

In Data Vault Friday

Modelling Currencies in Non-Historized Links in Data Vault (PART 3)

Watch the Video

In our continuous Data Vault Friday series, our CEO Michael Olschimke delves into a compelling question presented by a member of our audience.

“Is it a good idea to create a HUB for an ‘opposite side Account’? Or perhaps, should we take it a step further and explore the possibility of merging the ‘opposite side Account’ with HUB_ACCOUNT? If so, how do we handle the diversity of IBAN formats across different countries? Is it practical to have accounts from all over the world within our HUB_ACCOUNT?”

In this informative video, Michael addresses the intricacies of data modeling, specifically concerning the creation of a HUB for the ‘opposite side Account.’ He discusses the potential advantages and challenges associated with merging this entity with HUB_ACCOUNT. Furthermore, Michael offers insights into navigating the complexities of different IBAN formats in a global context, weighing the considerations of including accounts from diverse geographical regions within a unified HUB.

For those grappling with decisions around global data integration and HUB design, Michael’s responses in this video offer valuable guidance and considerations.

In Data Vault Friday

Ghost Records in Data Vault

Watch the Video

In our ongoing Data Vault Friday series, our CEO Michael Olschimke addresses an interesting question raised by a member of our audience.

“We wonder if a ghost record in a satellite should have NULL values in the descriptive fields or not. What is the advantage of non-NULL values?”

In this insightful video, Michael explores the nuances of handling ghost records in satellites, specifically focusing on whether descriptive fields should contain NULL values or default values. He provides a comprehensive analysis of the pros and cons associated with each solution, shedding light on the advantages and considerations that come with choosing either non-NULL values or default values for descriptive data in ghost records.

For those grappling with the challenges of managing ghost records within the Data Vault framework, this video offers valuable insights and considerations to inform decision-making.

In Data Vault Friday

LINK Record Source in Data Vault

Watch the Video

In our continuous Data Vault Friday series, our CEO Michael Olschimke takes on a thoughtful question posed by a member of our audience.

“LINK table has only one ‘Record Source’ element to capture the source system value. In case the LINK generates the key relationship between two hubs which are sourced from different systems (I have not encountered any such but I can foresee this situation), What would be the RECORD SOURCE Value?”

In this enlightening video, Michael delves into the fundamental modeling principles of link entities within the Data Vault framework. Specifically, he addresses the scenario where a LINK table establishes key relationships between two hubs originating from distinct systems. Michael provides insights into determining the appropriate “Record Source” value in such situations, offering guidance on maintaining clarity and accuracy in data lineage.

For those navigating the nuances of link table modeling and considering potential cross-system relationships, this video provides valuable insights and best practices.

In Intermediate

How to Successfully Manage Data Vault 2.0 Using dbt

Watch the Webinar

How to Successfully Manage Data Vault 2.0 Using dbt

More and more companies are making the big step towards a modern data architecture. As they deal with the components, they can’t actually stumble upon dbt and Data Vault. Among a lot of features “automation” is one, that they have totally in common. So why not combine these worlds?

Hear firsthand from dbt Labs and Scalefree how to successfully manage Data Vault 2.0 using dbt!

Highlights

– Learn how dbt will move your data platform to the next level and how the dbt Cloud product will simplify your code development and project management.

– Is dbt x Data Vault the right fit for your company? We will talk about the considerations you should make when starting your project.

Watch Webinar Recording

Webinar Agenda

1. Dbt Snapshots
2. Dbt Incremental Models
3. Incremental Satellites in Data Vault
4. Conclusion

In Data Tools

Data Vault 2.0 with DBT – Part 2

Data Vault 2.0 with dbt

Data Vault 2.0 with dbt

In the first part of this blog series we introduced you to dbt. Now let’s take a look at how you can implement Data Vault 2.0 with dbt and what advantages this offers. If you don’t know the first part yet, you can read it here.

In this article:

 

Dbt Models

dbt provides the ability to create models and generate and execute SQL dynamically out of these models. This allows you to write your data transformations in models using SQL and reusable macros powered by Jinja2 to run your data pipelines in a clean and efficient way. However, the most important part for the Data Vault use case is the ability to define and use those macros.

But first we should clarify how models work in dbt.

This tool handles the compilation and execution of models written using SQL and the Jinja macro language. Each model consists of exactly one SQL SELECT statement. Jinja code is translated into SQL during compilation.

Data Vault 2.0 with dbt

This figure shows a simple model in dbt. A big advantage of Jinja is the possibility to generate sql programmatically, for example with loops and conditions. Also, through the ref() functions, dbt understands the relationships between the models and builds a dependency graph. This ensures that the models are built in the correct order and that the data lineage is documented. A lineage graph may look like this:

Data Vault 2.0 with dbt

The materialization of models can be defined at different configuration levels. This allows fast prototyping with views and switching to materialized tables if necessary for performance reasons.

Data Vault 2.0 and Macros

But how can we implement Data Vault 2.0 with dbt? The most important part for using Data Vault 2.0 is the ability to define and use macros. Macros can be called in models and then generate into that macro additional SQL or even the entire SQL code.

For example, you could create a macro to generate a hub that gets the source/staging model as an input parameter, as well as the specification of the columns for the business key(s), load date and record source. The sql code for the hub is then generated dynamically from this. The advantage of this is that a change to the macro would directly affect each individual hub, which greatly improves maintainability.

At this point, you also gain huge benefits from the active open-source community around dbt. There are many open-source packages with which it can be extended.

There are also already some packages that are perfect for using Data Vault 2.0 with dbt. 

For example, our own open-source package DataVault4dbt, developed and actively maintained here at Scalefree, provides a comprehensive set of dbt macros to translate a Data Vault model “on paper” into actual tables and views—such as Hubs, Links, Satellites, and more. The package is actively used in real-world projects and helps enforce best practices for a modern, audit-friendly Data Vault 2.0 implementation.

To explore all features and macro parameters in detail, check out the documentation.

The only thing you need in your model, for example for a hub, is just one macro call:

{%-

hub(src_pk, src_nk, src_ldts, src_source, source_model) 

-%}

With the parameters of the macro call you define the source table where the columns can be found (source_model) and the column names for the hash-key (src_pk), business key(s) (src_nk), load date (src_ldts) and the record source (src_source) column. When the model and the macro(s) defined in the model are executed, the SQL gets compiled and processed on the database system.

The metadata needed can for example be defined in variables with jinja directly in the model:

What you can also see is that this tool provides different options for the materialization. The incremental materialization for example, will load an entity as a table on an incremental basis.

When the model is executed, dbt generates the whole sql out of the macro and decides how the records are loaded. If the hub table does not exist, yet it is created and all records are loaded, if it already exists, the table is loaded incrementally. 

For people who tried or managed to implement a Data Vault with “vanilla” SQL, you will realise that this is a real game-changer. The team can now focus entirely on the Data Vault design itself. Once the metadata is identified, dbt, along with your macros, can take care of the entire logic.

Openly available packages can add basic Data Vault 2.0 principles to dbt and therefore allow users to quick-dive into Data Vault implementation. Dbt’s general openness allows you to adjust all macros, for example, to your company or project specific Data Vault flavor to meet your technical and business requirements. 

It is important to note that many of the currently available dbt packages for implementing Data Vault 2.0 deviate from the official standards in some details. Our own open-source package DataVault4dbt, developed and actively maintained here at Scalefree, addresses this gap by supporting all core Data Vault 2.0 entities and the latest best practices. The package is already used in real-world projects to help ensure a modern and audit-friendly implementation.

Conclusion

Integrating Data Vault 2.0 with dbt streamlines the data warehousing process by leveraging dbt’s capabilities to define models and macros, enabling efficient and dynamic SQL generation. This approach allows teams to focus on Data Vault design, with dbt handling the underlying logic.

Our open-source package DataVault4dbt brings these advantages to real-world projects, offering a reliable and standards-aligned way to build Hubs, Links, Satellites, and more.

In Data Vault Friday

Modelling Currencies in Non-Historized Links in Data Vault (PART 2)

Watch the Video

As part of our ongoing Data Vault Friday series, our CEO Michael Olschimke engages with a thought-provoking question posed by a member of our audience.

“Is it a good idea to create a HUB for an ‘opposite side Account’? Or maybe we should go even further and try to merge the ‘opposite side Account’ with HUB_ACCOUNT? If yes, what about different IBAN formats in different countries? Do we really want to have accounts from all over the world in our HUB_ACCOUNT?”

In this informative video, Michael delves into the intricacies of data modeling and hub creation, specifically addressing the concept of incorporating an “opposite side Account” into the HUB structure. He explores the potential benefits and challenges associated with merging these entities and offers insights into managing the diversity of IBAN formats across different countries.

For those navigating the complexities of global data integration and considering the structure of their HUB_ACCOUNT, this video provides valuable guidance and practical considerations.

In Solutions

Implementing GDPR in Data Warehousing

Solutions

Implementing GDPR

In the realm of data warehousing, whether it be Data Vault 2.0 or traditional approaches like Kimball and Inmon, data is stored and processed across multiple layers. The intricacies of privacy, particularly the application of security measures and the concept of the “right to be forgotten,” permeate every layer housing personal data.

For privacy implementation, the primary objective is the removal of Personally Identifiable Information (PII) data from each layer. This meticulous process aims to extract PII data, leaving non-PII data intact. In the ideal scenario, this ensures a reduction in consumer data proportionate to the removed PII data.

The General Data Protection Regulation (GDPR) casts a significant influence on data warehouse projects, introducing stringent requirements for data processing and storage. This impact spans across security considerations, determining who has access to what data, and privacy mandates, addressing the right to be forgotten.

ACCESS THE SOLUTION
In Data Vault Friday

Modelling Currencies in Non-Historized Links in Data Vault (PART 1)

Watch the Video

As part of our ongoing Data Vault Friday series, our CEO Michael Olschimke engages with a thought-provoking question posed by a member of our audience.

“Is it a good idea to create a HUB for an ‘opposite side Account’? Or maybe we should go even further and try to merge the ‘opposite side Account’ with HUB_ACCOUNT? If yes, what about different IBAN formats in different countries? Do we really want to have accounts from all over the world in our HUB_ACCOUNT?”

In this informative video, Michael tackles the complexities surrounding the creation of a HUB for the “opposite side Account” and explores the possibility of merging it with HUB_ACCOUNT. Delving into the practical considerations of accommodating different IBAN formats across countries, he provides nuanced answers and considerations to guide the decision-making process.

For those grappling with the challenges of data modeling in the context of global account structures, this video offers valuable insights and practical solutions.

In Data Vault Friday

Load Date vs Snapshot Date in Data Vault

Watch the Video

In our continuous Data Vault Friday series, our CEO Michael Olschimke delves into a pertinent question posed by our audience about the use of a Snapshot Date.

“What is the purpose of the Snapshot Date and how does it relate to the load date?”

In this enlightening video, Michael explores the significance of both the load date (TS) and the snapshot date (TS) within a well-designed Data Vault 2.0 architecture. Acknowledging their crucial roles as timelines, he provides a clear and insightful explanation of how these dates function and interrelate in the context of Data Vault 2.0.

Understanding the nuances of these temporal elements is key to optimizing data management within the Data Vault framework, and Michael’s explanation offers valuable insights for both newcomers and experienced practitioners in the field.

In Data Vault, Intermediate

What’s New in Data Vault?

Data Vault 2.0

Attendees of our Data Vault 2.0 trainings often ask us what the difference between the book “Building a Scalable Data Warehouse with Data Vault 2.0” and our current consulting practice is. To give a definitive answer, at least for now, we have written this article.

What’s new in Data Vault?

This webinar  discusses recent updates in Data Vault 2.0 practices since the publication of the book “Building a Scalable Data Warehouse with Data Vault 2.0.” The key changes include the virtualization of the Load End Date using window functions to enhance efficiency, the adoption of hybrid architectures that integrate data lakes for staging purposes, and the shift of business timelines into the dimensional model for greater flexibility. Additionally, there’s an emphasis on using Snapshot Date Timestamps in Business Vault satellites to simplify business rule development. The article clarifies that these updates are considered enhancements within the existing Data Vault 2.0 framework, referred to as “Data Vault 2.0.1,” rather than constituting a new version like Data Vault 3.0.

Watch webinar recording

In this article:

Data Vault 3.0?

First of all, there is no Data Vault 3.0. We like to refer to the version of Data Vault that we teach and apply in our consulting practice, as “Data Vault 2.0.1”. Therefore, there are only slight differences to the version we used in the book. Some of these changes look big, but they don’t really modify the underlying concept and that is what matters regarding version changes. So, based on the concept, we only see minor changes or enhancements. However, they might have some larger impact on the actual implementation, but that is only due to technology changes.

How to Fit Data Vault 2.0 Between Two Covers

On top of the minor changes to the concept, there is also another factor at play here: when writing the book, we had to scope the book to make it fit between the covers as the publisher had a page limit (which we actually exceeded a bit). Therefore, we did not apply all Data Vault 2.0 concepts in the book: for example, real-time concepts are not covered by the book, and we did not use a data lake or cloud computing in our examples. Instead we only briefly covered these concepts for completeness but focused on on-premise technology which was more used in actual projects back then. In 2012, cloud computing was already available and widely used, but it was easier to tailor the book to more readers by the use of on-premise technologies.

With that in mind, what has changed since the book came out? 

Removal of Load End Date

The most obvious change is the removal of the Load End Date. Well, in our actual projects, we don’t completely remove it from the Data Vault 2.0 model, we just virtualize it by removing the Load End Date from the underlying satellite table and virtually calculating it in a view on top of the satellite table using a window function (typically LEAD, but LAG is also possible). This way we can get rid of the update procedure to maintain the Load End Date (the so-called end-dating process) while preserving the query patterns downstream. Keep in mind that it might also be more efficient to use the window function when loading PIT tables or satellites, and therefore the query layer is actually only for power users when they query the Data Vault model directly. 

That’s actually not an update: even in 2012 (and before) we used these approaches but they didn’t work on SQL Server 2014, which was used in the book as the analytical window function required for this approach is ways too slow. However, it improved in 2016. To get rid of the Load End Date in such scenarios where the window function is too slow or just don’t exist, a new solution has emerged: the use of a snapshot partition in the PIT table. The end of all times is used in the PIT table to refer to the latest delta in each satellite for the delta check. Once the PIT table is available, it can also be used to produce SCD Type 1 dimensions (without history) and therefore the need for the Load End Date (or a fast replacement as described above) only exists in loading.

Hybrid Architecture

The next obvious change is the use of a hybrid architecture where a data lake is used for staging purposes. In the book, we focused on the on-premise Microsoft SQL Server stack that didn’t include a distributed file system. We already recommended to clients to use a data lake for staging purposes, in an architecture we called the “hybrid architecture.” Back then, only a few followed the advice, but today most clients use the hybrid architecture for their data warehouse. We actually consider a relational staging area an anti-pattern and do not recommend it anymore to clients (with some exceptions). 

Multi Temporal Data Vault 2.0

The book describes a temporal PIT (TPIT) for building multi-temporal solutions. While the pattern is still valid and relatively flexible compared to other solutions, today we typically move the business timelines into the dimensional model for highest flexibility. This is discussed in more detail in the Multi-Temporal Data Vault 2.0 class. TPITs are less flexible but have higher performance.

Snapshot Date

Satellites in the Business Vault might use a Snapshot Date Timestamp instead of a Load Date Timestamp. The idea is that, as a general practice, a Business Vault entity should always reuse an existing granularity. It just becomes much easier to develop Business Vault solutions. With the Snapshot date in the primary key of the satellite, this becomes much easier for business rules on the outgoing information granularitiy. This is discussed in more detail in the Data Vault 2.0 Information Delivery class. 

Conclusion

And finally, we also made some mistakes. We are willing (not happy) to admit it in our errata page for the book – this link is directly from our internal Wiki and we promise to keep it updated (however, we didn’t receive additional reports lately).

Close Menu