Skip to main content
search
0
CartClose Cart
All Posts By

Tim Kirschke

Tim Kirschke is a Managing BI Consultant and Head of Internal Development at Scalefree. With a background in Applied Mathematics, he specializes in architecting auditable data solutions using Microsoft Fabric, Snowflake, and dbt. A dbt Certified Architect and CDVP2, Tim has led major warehouse implementations and conducts strategic workshops on data automation and enablement.

In Data Tools, dbt blog article, Intermediate

Unify Your Understanding of Data with the dbt Semantic Layer

dbt_semantic_layer_lineage

dbt Semantic Layer

The bigger a corporation gets, the more data is available, and more and more users want to use this data. In a traditional data warehouse (DWH) environment, the DWH typically provides a consumption layer consisting of various information marts, which are then loaded into multiple business intelligence (BI) tools. In there, business users transform and aggregate the data to calculate KPIs and finally make business decisions. 

This, as it turns out, is easier said than done. To derive KPIs out of the data, business users need to have a common understanding of the data provided by the DWH. The information to understand the data is typically hard to find and not accessible in a single place. 

Ultimately, this might lead to multiple departments having different understandings of the same data and deriving different interpretations of the same KPI. Now, it’s very likely that the worst case scenario happens, the trust in your data fades out. This is where a unified semantic layer can help!

From Raw Data To Semantic Layer – With Turbovault And Dbt Cloud

Data Vault is vital for businesses due to its adaptability and scalability in managing dynamic data environments. Its hub-and-spoke architecture ensures traceability and agility, enabling quick adaptation to changing requirements and diverse data sources.

Join our webinar and learn about how to use dbt Cloud with Turbovault and a data modeling tool to implement Data Vault in your organization. Additionally, we will have a look at the dbt Semantic Layer.

Watch Webinar Recording

In this article:

Components of the dbt Semantic Layer

The dbt Semantic Layer helps simplify the definition and usage of critical business metrics in your dbt project. Metric definitions are centralized to allow consistent self-service usage for all data teams. 

By shifting metric definitions from the BI layer into the modeling layer, business users from different units can be confident that they use the same metric definition, no matter which tool they use. In case a metric definition changes over time, the changes will be applied everywhere it’s used and therefore consistency is enforced. 

To create a unified semantic layer inside your dbt project, the following steps are necessary:

  1. Draft a semantic model
    • To implement a semantic model, a model needs to be drafted first. This should happen via a collaboration between the technical and business teams, to identify core business concepts and how they relate to each other.
  2. Create dbt models that match your semantic model
    • Each object of your semantic model should be turned into a dbt model 1:1. While creating them, put extra work into aligning column names with naming standards and correctly developing the loading logics.
  3. Create new .yml files in the metrics folder
    • Everything related to the dbt Semantic Layer needs to be located in a new folder called “metrics”. In there, .yml files are used to define and configure your semantic models. We recommend creating one .yml file per semantic model.
  4. Define entities
    • In contrast to the name, entities in the semantic model roughly describe columns of semantic models. Entities can be of four different types: Primary, Unique, Foreign, or Natural. Every model needs to have a primary entity, and one entity can be just one column or a SQL expression transforming a column.
  5. Define dimensions
    • A dimension in the dbt Semantic Layer can be seen as different ways to look at your model, i.e., group the data by a specific attribute. Every dimension needs to be tied to a primary entity, which is used for the grouping. A good example is a date column which enables you to group your data by day, month, or year.
  6. Define measures
    • Measures represent aggregations applied to specific columns in your data model. Measures can be used in other measures to calculate more complex ones, and can be defined with various parameters that help create executable SQL code for calculation.
  7. Define metrics
    • Metrics represent the language of the business users. They can be of various types, which represent different kinds of calculations. Some examples include Conversion metrics, Cumulative metrics, Derived metrics, and Ratio metrics. They are always based on measures and represent the last building block of the semantic layer.

 

This is how the semantic layer is reflected in your dbt lineage:

dbt_semantic_layer_lineage

Consuming the dbt Semantic Layer

Once your dbt project has a semantic layer defined, it can be opened to data consumers. The dbt Semantic Layer allows various BI tools to directly connect to your dbt Cloud project and integrate metrics, measures, and filters directly into the tool of choice.

The following tools are already natively supported: 

  • Tableau
  • Google Sheets
  • Microsoft Excel
  • Hex
  • Klipfolio PowerMetrics
  • Lightdash
  • Mode
  • Push.ai
  • Steep

Other tools can be integrated with custom integrations, as long as they support generic JDBC connections and are compatible with Arrow Flight SQL.

Conclusion

The dbt Semantic Layer can help regain trust in your data warehouse. By moving calculations back from the business users into the data model, a common definition of business KPIs is created. 

Although there is some additional setup required, implementing a semantic layer can highly improve the value generated by your data. Integrating it into the BI tools of your business users even simplifies the way your data is consumed. 

If you want to know more about the dbt Semantic Layer and learn how it fits into a Data Vault 2.0 powered Data Warehouse, make sure to join our next webinar on August 13th!

In Data Tools, dbt webinar, Intermediate

Scale Up your Data Vault Project – with dbt Mesh

dbt Mesh - data mesh solution

dbt Mesh

Learn how dbt Mesh enhances Data Vault projects within dbt Cloud by facilitating a more efficient data mesh architecture. The larger a data warehouse project grows, the more people begin to rely and work with the data provided. This work could be consuming the data, applying business rules, modeling facts and dimensions, or other typical tasks in a data environment. In a large organization, all these users might be scattered across different divisions, and the data they are working with might belong to different business domains. At some point, the entire organization faces the challenge of data sharing and governance guidelines, which might prohibit users of the sales department from accessing data from the finance department. A data mesh offers a solution that helps organizations to deal with these challenges. If you want to learn more about the data mesh, check our recent blog article about Data Vault and data mesh here!

We also have a webinar on exactly this specific subject. Don’t miss it and watch the recording for free!

Data Mesh Support bei dbt Cloud

Many organizations struggle with introducing a Data Mesh approach into the Data Vault landscape. In this webinar, we will dive into dbt Mesh, and how to leverage it in a Data Vault project.

Watch Webinar Recording

In this article:

What is dbt Mesh?

Dbt Mesh is a recently added feature that makes dbt Cloud work more efficiently with a data mesh approach. The already familiar {{ ref() }} function is no longer limited to models within one dbt project, instead it can refer to models of other dbt projects.

Why would I want to refer to other dbt projects?

Imagine a big organization that uses dbt Cloud for their Data Vault implementation. The project might have 400 sources defined, 2000 models implemented, and is used actively by 30 developers. Out of these 30 developers, there might be 5 people specifically working on the Business Data Vault and Information Mart layer for finance-related objects. Another 5 developers are working on the same layers but for sales-related objects.

At some point, you might want to avoid finance people messing around with the sales-related dbt models, so a data mesh architecture is to be implemented. This would allow the organization to define policies regarding data sharing, data ownership, and other governance measures.

With dbt Mesh, both the Sales and the Finance team would get their own dbt project. Since both should be based on the same Raw Data Vault, an additional foundational dbt project is created exclusively for staging and Raw Data Vault objects. Both domain-specific dbt projects, sales and finance, can now refer to Raw Vault objects inside the foundational dbt project, avoiding actually physically replicating the data.

dbt Mesh - data mesh solution

How can I leverage dbt Mesh in a Data Vault powered Data Mesh?

Define Data Contracts

Dbt models, or groups of models, can now be configured to have data contracts. Inside the already familiar .yml files, models can now be set to be publicly available (within an organization), data owners can be enforced, and table schemas can be locked.

Create a Foundational dbt project

In a Data Mesh architecture, the most common way to implement Data Vault 2.0, is to have a commonly shared Raw Vault as a foundation, and both Business Vault and Information Marts are divided by business domains. In dbt Mesh, this would reflect in a foundational dbt project, that includes all staging and Raw Data Vault objects. Only the Raw Data Vault objects would be configured to be accessible by other dbt projects, since the staging models should not be used outside of Raw Data Vault models.

Add domain-level dbt projects

Based on the foundational Raw Vault dbt project, each domain team can now work in their own dbt project. They access the Raw Data Vault via the (extended) {{ ref() }} function and don’t have to worry about maintaining these Raw Vault objects. Additionally, they can define which of their artifacts might be useful for other domains, these can be shared via their own data contracts.

Distribute Responsibilities

Typically, a power user does not create Hubs, Links, and Satellites. And it’s not their responsibility to ensure a reliable Raw Data Vault to build transformations on. Therefore, it is important to define responsibilities within each dbt project. Especially objects that are shared outside of one project should always have data contracts and defined owners. This ensures that users of these shared objects can rely on it.

Conclusion

All in all, dbt Mesh offers a fantastic way to properly implement a true data mesh approach. It is especially relevant, when different business domains of one organization are working together in dbt to create trustable deliverables. In most scenarios, it makes sense to already start using dbt Mesh, although your project might not be too big yet. Having clear responsibilities and data contracts always helps maintain trust and transparency for your data!

In dbt webinar, Intermediate

Enterprise Data Transformations with Turbovault and dbt Cloud

ENTERPRISE DATA TRANSFORMATIONS WITH TURBOVAULT AND DBT CLOUD FEBRUARY 27TH dbt Labs SCALEFREE
Watch Webinar Recording

Webinar Summary

Data Vault is vital for businesses due to its adaptability and scalability in managing dynamic data environments. Its hub-and-spoke architecture ensures traceability and agility, enabling quick adaptation to changing requirements and diverse data sources.

Come and join our upcoming webinar and learn about how to use dbt Cloud with Turbovault and a data modeling tool to implement data vault in your organization.

In this webinar you will

  • Receive a detailed 90-minute “show-and-tell” walkthrough of an end-to-end Data Vault implementation using cutting-edge tools
  • Explore the seamless integration of Ellie.ai, Turbovault4dbt, and Datavault4dbt for enhanced data modeling and automation
  • Understand the practical aspects of implementing a Data Vault without the need for a pre-configured demo environment.

Webinar Details

  • Date: 27th February
  • Time: 14:00 – 15:45 PM CET

Webinar Agenda

  1. Introduction to the Power Trio: dbt Cloud, Turbovault, and Data Modeling Tools
  2. 90-Minute “Show-and-Tell” Walkthrough of an End-to-End Data Vault Implementation
    • Using Ellie.ai for ER Model, Turbovault4dbt for dbt Automation, and Datavault4dbt for DV Model Generation
  3. Insights into Data Vault Implementation in Medium and Large Sized Companies
  4. Q&A Session with Industry Experts

In Partnership With

dbt labs
DataVault4dbt Powered by Scalefree
In Data Warehouse

Monitoring of a Data Vault-Powered EDW

Introduction

In this newsletter, we’ll discuss an overview of the possible ways to monitor your solution within an Enterprise Data Warehouse (EDW) built using Data Vault 2.0.
The monitoring approaches below focus on giving an overview of different possibilities to grant insights into the performance of your data warehouse solution. The goal is to gain useful information about the technical data of your EDW and use them to track errors, find bottlenecks inside loading and transformation processes, and overall boost the performance of your data warehouse.

In a webinar session dived deeper into this topic of technical testing and monitoring methods for a Data Vault powered EDW. It covered all EDW layers and provided performance insights with a focus on modeling metrics vaults and marts, emphasizing source data.

Watch the recording here for free!

What to expect

You will receive an overview of different metrics and types of metadata to monitor your solution. One step further, the modeling approach for capturing and later analyzing the data is covered for different layers of your EDW, starting with the classical metrics vault and ending with the metrics marts. Additionally, different areas of monitoring are covered, focusing on potential challenges inside your solution. The main focus of this newsletter is how to provide metadata and therefore gain useful insights.

Modeling Metadata

Four instances are covered, which hold useful technical data. These are the metrics vault and the metrics mart built on top of it, the meta mart, and lastly the error mart. Each of these have their own use, although the modeling and data source may resemble one another. The similarities and differences between the modeling approaches of these mostly technical tables and their data source are explained in the below sections.

Metrics Vault

The metrics vault is used to catch defined metrics, which originate from ETL/ELT data flows. It’s not a mandatory component of Data Vault, however when implemented, your solution will gain several advantages from technical capabilities, such as:

  • Error inspection
  • Root cause analysis
  • Performance metrics

In terms of data flows, it focuses on identifying errors, as well as granting insights into the origins of these failures, in order to take actions to prevent these errors from happening again in the future.

The metrics themselves might originate from different sources. These sources may be the data platform, the ETL/ELT, or ESW job orchestration tool you are using. For this reason, we recommend modeling these data sources using the same patterns and standards you use to produce the Raw Data Vault, this will provide a scalable solution to capture and process your metrics.

Metrics Mart

Inside the metrics vault, the data is modeled and optimized for storage, scalability, and flexibility. To analyze the gathered data, the model is needed to fit the end-user’s requirements. For this matter, the metrics mart is part of the information delivery layer. As the metrics vault is built exclusively to provide data for the metrics mart, the standards of the Data Vault 2.0 architecture have been followed with this approach. The metrics mart is a special-purpose Information Mart which is sourced mainly from the metrics vault. It can be assumed that both materialization of marts as tables and virtualization as views can be considered and performed. The virtualization approach may be preferred, as deployment speed benefits from this decision.

As mentioned above, the main data source for the metrics mart is the metrics vault, although it’s not limited to it. In some cases, the business may decide to couple business objects with metrics to track the performance of specific business objects. This also helps to further boost performance.

The data model of the metrics mart is not fixed and varies due to the needs of the end-user. Therefore, a dimensional model could be the best solution, but it doesn’t have to be. In some cases, the data originates from the tools themselves, in such cases, the original structure of these tools could fulfill the purpose of analyzing the data more efficiently.

Meta Mart

Metadata is stored directly in the meta mart. These marts don’t source from the raw data vault, thus there is no such thing as a meta vault as a source for the marts to virtualize them. Therefore, the meta mart is materialized as tables. The model is similar to other information marts. For this matter, the business requirements imply the exact model of the meta mart, as its main function is to provide data in such a way that businesses can make the most use of it. In some cases, it might be a Dimensional Model, in other cases, it could be modeled in third-normal form.

Error Mart

The error mart is another information mart. Therefore, it also relies on the business as to how to exactly implement the model, although in practice most of them are modeled using a dimensional model. The goal of the error mart is to catch errors, so-called “ugly data”. This data is rejected by your transformation tools, for example rejected data rows from your soft rules. As soft rules change over time, so do your error marts. These errors don’t source from the raw data vault, resulting in the absence of an Error Vault. The data for the error marts can come from a variety of sources, although most of it originates from the ETL / ELT engine.

Managing Metadata

As the section above has described how to model specified information marts and what the main purpose of those are, this section focuses on the details of “what to load”. For this matter, a closer look at general data itself, its origin, and use case is taken at this point.

Business Metadata

Business Metadata is defined as metadata with meaning for the business. Although, it is not completely defined who is responsible for this data, as it depends on the business itself, the data warehouse team is responsible for the management of the data. The meta mart covers this type of metadata.

Some metadata you should consider tracking include the following:

  • Business column names
    As the business may decide to use abbreviations in prefixes or suffixes, which need to be tracked in order to be more coherent for the business.
  • Business definitions
    As these definitions are the foundation for analyzing data in later processes. It should be a business description of the different attributes and tables. It is also an indicator for the necessity of the attribute or table. If the business can’t provide information about the column/attribute or table, it should be considered as to whether it is necessary within an information mart or if it needs to be redefined within the business.
  • Ontologies and taxonomies
    As it provides information about the business object behind the source data, such as the classification of the business object, the relationship to other objects or hierarchies.
  • Physical table and column names
    As they provide information about the relation between the business object names and the names of the physical tables and attributes. This information is used to associate the data and the business objects.
  • Record source
    As it describes the different record sources in business terms. Therefore, its goal is not to provide a reference to technical instances such as databases. Furthermore, it aims to describe them in a manner that the business meaning behind those sources is provided.
  • Table specification
    As they describe the purpose of the source table and data as well as the column names and keys.
  • Exception-handling rules
    As they provide a list of potential technical issues, such as potential errors or data quality issues and how the ETL / ELT process handles these errors.
  • Source system business definitions
    As they describe the business meaning of source attributes.
  • Business rules
    As they generate new data based on business terms. Developers need some understanding of these rules and transformations in order to comprehend the requirements. As business metadata, the business rules are described in a way that the business can understand these. It should be considered to divide between hard rules and soft rules.

Technical Metadata

As business metadata serves the business the most, the data warehouse team is most likely to benefit from technical metadata, which is also stored in the meta mart. For this reason, most of technical metadata originates from technical components, resulting in the following examples:

  • Source systems
    As it provides a technical description of the source systems, such as database names or flat file locations.
  • Data models
    These are not typical metadata, although they are a helpful asset. They provide information about relationships between the used tables and could be presented in a graphical form.
  • Data definitions
    As they describe the data in a technical way. This includes information about the table name, column name, data type, constraints, or default values.
  • Business rules
    Business rules are also a part of technical metadata. Here, specifically, the technical definitions of these rules are considered, as they need to be implemented in the ETL / ELT tools later on. Hard rules and soft rules should be separated, just as in the business metadata.
  • Volumetrics
    As they describe the evolution of data loads, table sizes, and growth patterns in order to estimate the workload of the data warehouse.
  • Ontologies and taxonomies
    Technical metadata also should provide information about ontologies and taxonomies, like abbreviations of terms and attributes, relationships, business key designations, parents, and hierarchies as well as re-defines.
  • Data quality
    This metadata provides information about data quality metrics, which may include standardization of source data.

Process Execution Metadata

Process execution metadata is not provided by the business or source applications like business metadata and technical metadata, but furthermore is generated by the data warehouse team to provide insights into the ETL / ELT processes. The main goal is to provide information for a better understanding of the data warehouse performance and thus potentially increasing this performance. Most of the data comes from the ETL / ELT tool you are working with, delivering the following metadata:

  • Control flow metadata
    As a control flow executes at least one data flow, it provides data about processes and data lineage of all elements of the data warehouse.
  • Data flow metadata
    As they provide data about the data flows themselves. This includes the data volumes and rejected rows of a data flow / data transformation.
  • Package metadata
    The package metadata contains a summary of the information about running a package, which usually executes a control flow.
  • Process metadata
    Most packages are executed by a scheduling application. The process metadata provides data about the process that has started the package.

The process execution metadata should be stored in the metrics vault and kept separated from the business metadata and technical metadata.

Conclusion

In this newsletter, we provided an overview of monitoring options for your Data Vault-powered EDW.
We covered different types of information marts, including their modeling approach, their data sources, and usage for businesses and their data warehouse team.
We learned about different types of metadata, their source and which purpose they serve. For this matter, we divide between business metadata, that describes your data in terms, that business can make the most use out of it, technical metadata, that holds technical information about the processed data, and process execution metadata, which provides information about your data processes and ETL / ELT pipelines.
Finally, this newsletter provides information about how and what metadata you can process to monitor your EDW according to the standards of Data Vault 2.0.

We will go even deeper in our webinar. Make sure to watch the recording for free! We are looking forward to seeing you there.

In Data Tools, Intermediate

Bring Your Data Vault Automation to the Next Level with DataVault4coalesce

SALESFORCE SOLUTIONS

Data Vault Automation with DataVault4coalesce

A cooperation created DataVault4coalesce, an open source extension package for coalesce.io. In a previous webinar, we explored coalesce.io, a new competitor in the highly contested market of data warehouse automation tools.

coalesce

Level up your Data Vault automation – with DataVault4coalesce

Coalesce is a modern, column-aware data warehouse automation tool. In this webinar, you will learn how Scalefree’s latest publication brings best practices out of the Data Vault world into your coalesce.io experience. This includes data loading patterns, data vault related features, and more! All embedded into easy-to-use UI options to make use of Coalesce’s unique configurable user interface. Tune in to see DataVault4coalesce in action!

Watch Webinar Part 1Watch Webinar Part 2

In this article:

And everyone who watched that webinar might remember that at the end, we announced an even closer relationship between coalesce.io and Scalefree and a commitment to bring Scalefree’s best practices into coalesce.io!

For those who didn’t watch the webinar, you can find it here.

Recap: What is Coalesce?

coalesce.io is a Data Transformation solution made for Snowflake. When working with Coalescse, you build directed acyclic graphs (DAG) which contain nodes. A node represents any physical database object, like tables or views, or even stages and external tables.

Coalesce itself is built around metadata that stores table and column-level information, which describes the structure inside your data warehouse. This metadata-focused design enables a couple of features that strongly drive towards scalability and manageability. 

All the metadata allows a team to track past, current, and desired states of the data warehouse by deeply integrating it and all the workflows that it brings. Additionally, users can define standardized patterns and templates on column- and table-level.

How can Data Vault jump in here?

These mentioned templates open up the gate to implement Data Vault 2.0 related patterns and best practices. Especially on the table level, it might quickly come to mind that you could try to build a template for a Hub or a Link.

On column level, this could be a repeated transformation which is then managed in only one so-called macro, which makes it very easy to implement changes with low to zero impact. You could think of hash key calculation or virtual load-end-dating here.

And that is exactly what we at Scalefree have done since the webinar last year. Lead developers from coalesce.io sat together with Data Vault experts and developers from Scalefree with one goal: Create something amazing that helps users to automate their Data Vault 2.0 implementation!

Datavault4Coalesce

How fast can I build a Data Vault? Yes!

This cooperation created DataVault4coalesce, an open-source extension package for coalesce.io, which will be available on March 16th! Let’s have a sneak peek at a selection of what users can do with DataVault4coalesce.

The first release of DataVault4coalesce will feature a basic set of Data Vault 2.0 entities:

While providing DDL and DML templates for the entity types mentioned above, DataVault4coalesce makes use of Coalesce’s ability to define the UI for each node type. For stages, this means that users can decide if they want DataVault4coalesce to generate ghost records automatically or not, as shown in the screenshot below:

Datavault4Coalesce

This Data Vault related interface can be found across all node types and allows users to adjust DataVault4coalesce to fit their requirements conveniently!

Conclusion

First of all a bummer, DataVault4coalesce will only be available starting from the 16th of March. But there is no reason to wait that long to dive into coalesce.io itself! Since it is now part of Snowflake Partner Connect, it’s never been easier to get your hands on a fresh coalesce.io environment!

Just sign up for a free Snowflake trial here and initialize your coalesce.io experience within seconds by accessing the Partner Connect portal! Then, you just have to load any desired data into it, and you can start building your data pipelines with coalesce.io. And when the 16th of March finally arrives, you just have to add DataVault4coalesce to your coalesce.io environment – and now you can start to build Data Vault faster than ever!

Also, don’t miss out on this recording, where we will show you DataVault4coalesce in action. Watch it here!

In Intermediate

Coalesce and Data Vault 2.0 – A Perfect Match?

Watch the Webinar

Coalesce and Data Vault 2.0 – A Perfect Match?

This webinar introduces the data warehousing automation tool coalesce.io and how it can be used to create a Data Vault 2.0-powered data warehouse solution. You will see live demonstrations of the tool and the data vault entities.

Learn why Data Vault 2.0 is the perfect choice for date warehouse automation tools like Coalesce and how Coalesce can kickstart your Data Vault 2.0 solution!

Watch Webinar Recording

Webinar Agenda

1. Introduction to Coalesce
2. Demo Session
3. Introduction to Data Vault 2.0
4. Why Coalesce and Data Vault?
5. Demo Session

In Data Tools, Intermediate

Kick-Start Your Data Vault 2.0 Implementation with Datavault4DBT

DataVault4dbt Powered by Scalefree

Datavault4dbt

Scalefree has released datavault4dbt. An open source package, that provides best-practice loading templates for Data Vault 2.0 entities, embedded into the open source data warehouse automation tool dbt.

Datavault4dbt currently supports Snowflake, BigQuery and Exasol and comes with a lot of great features:

  • A Data Vault 2.0 implementation congruent to the original Data Vault 2.0 definition by Dan Linstedt
  • Ready for both Persistent Staging Areas and Transient Staging Areas, due to the allowance of multiple deltas in all macros, without loosing any intermediate changes
  • Creating a centralized, snapshot-based Business interface by using a centralized snapshot table supporting logarithmic logic
  • Optimizing incremental loads by implementing a high-water-mark that also works for entities that are loaded from multiple sources
dbt, Scalefree's partner

Kickstart your Data Vault 2.0 Implementation – with datavault4dbt

This webinar delves datavault4dbt, an open-source package by Scalefree that simplifies Data Vault 2.0 implementation in dbt. It provides best-practice templates for hubs, links, and satellites, ensures compliance with Data Vault standards, and supports flexible staging with optimized incremental loads, you won’t want to miss this webinar.

Watch webinar recording

In this article:

Building a Data Vault 2.0 Solution – made easy

The overall goal of releasing Data Vault 2.0 templates for dbt is to combine our years of experience in creating and loading Data Vault 2.0 solutions into publicly available loading patterns and best practices for everyone to use. Out of this ambition, datavault4dbt, an open source package for dbt was created and will be maintained by the Scalefree expert team. 

The most valuable characteristic of datavault4dbt is that it carnates the original Data Vault 2.0 definition by Dan Linstedt. It represents a fully auditable solution for your Data Vault 2.0 powered Data Warehouse. With a straight-forward, standardized approach, it enables the team to conduct agile development cycles.

By allowing multiple increments per batch while loading each Data Vault entity type, datavault4dbt supports both Persistent and Transient Staging Areas without losing any intermediate changes. These incremental loads are even optimized by implementing a dynamic high-water-mark that even works when loading an entity from multiple sources.

Additionally, datavault4dbt encourages strict naming conventions and standards by implementing a variety of global variables that span across all Data Vault layers and supported Databases. The process of end-dating data is completely virtualized to ensure a modern insert-only approach that avoids updating data.

With all these features, datavault4dbt is the perfect solution for your modern Big Data Enterprise Data Warehouse.

From the Stage over the Spine into the PITs

To achieve all this, we worked hard on creating a solid and universal staging area. All hashkeys and hashdiffs are calculated here and users are given the option to add derived columns, generate prejoins with other stages and add ghost records to their data. All of this highly automated based on parameterized user input. 

Based on staging areas, the Data Vault 2.0 spine can be created. Hubs, Links and Non-Historized Links can be loaded from multiple sources including mapping options to ensure business harmonization. 

This spine is then enriched by Standard Satellites, Non-Historized Satellites, Multi-Active Satellites and/or Record-Tracking Satellites. All of those that require it come with a version 0 for tables and a version 1 for end-dated views. 

Based on the Raw Data Vault, PITs can be created automatically, and their loading is backed by an automated, highly-configurable but optional logarithmic snapshot logic. This logic is included in the Control Snapshot Table, which also comes in two consecutive versions. To wrap the logarithmic snapshot logic up, a post-hook for cleaning up all PITs is included and comes in handy.

DataVault4dbt Powered by Scalefree

Start now and boost your Data Vault experience!

The lines above made you think “Nah, that’s all too good to be true!”? Convince yourself, or give us your highly appreciated feedback by visiting datavault4dbt on Github!

Of course, our future ambitions for datavault4dbt are high and next on our list are a lot of important topics, like:

  • Provide a detailed working example of datavault4dbt
  • Extend and migrate the existing documentation of the package
  • Support more and more databases
  • Add more advanced and specific Data Vault 2.0 entities
  • Develop automated Data Vault related tests
  • Review and implement user feedback and suggestions

Stay tuned for more datavault4dbt content on all our marketing channels!

In Beginner

Data Warehouse Automation – Build or Buy?

Watch the Webinar

Data Warehouse Automation – Build or Buy?

In this webinar, we take a sneak peek into one of the hot topics of modern data warehousing, namely Data Warehouse Automation. We would break down the basics of DW automation & how it has brought about a cultural shift in the realm of a modern Data Warehouse & its architecture.

In this regard, we will also touch upon the often-asked question “Build or buy” along with sharing our experience working with several customers who have benefited immensely from automation and the key lessons we have learned as part of our overall DW automation journey.

This webinar is for anyone who loves Data!

Watch Webinar Recording

Webinar Agenda

1. Understanding Data Warehouse Automation
2. Drivers for Decision ING
3. Automation in Data Vault
4. Anti-Patterns in DV Automation
5. Best Practices

In Intermediate

Speed Up Your Vault with VaultSpeed – Success Through Automation – Part 2

Watch the Webinar

Speed Up Your Vault with Vaultspeed – Success Through Automation

In this Webinar, we take a closer look at Data Warehouse Automation and how easily it can be implemented. First, we will break down the basics of Data Warehouse Automation.

Then we will show how Data Vault 2.0 can contribute to successful Automation on the basis of a sample COVID-19 data set showing vaccine and infection numbers.

Lastly, Vaultspeed will give a demonstration of their tool that implements our suggested model using the data set thus showing the viewers how “simple and easy” VaultSpeed as a Data Warehouse Automation tool is.

This webinar is for everyone who wants to learn about Data Warehouse Automation and a sneak peek into VaultSpeed.

Watch Webinar Recording

Webinar Agenda

1. Understanding Data Warehouse Automation
2. Automation in Data Vault 2.0
3. Usecase
4. Vaultspeed Demo

In Intermediate

Speed Up Your Vault with VaultSpeed – Success Through Automation – Part 1

Watch the Webinar

Speed Up Your Vault with Vaultspeed – Success Through Automation

In this Webinar, we take a closer look at Data Warehouse Automation and how easily it can be implemented. First, we will break down the basics of Data Warehouse Automation.

Then we will show how Data Vault 2.0 can contribute to successful Automation on the basis of a sample COVID-19 data set showing vaccine and infection numbers.

Lastly, Vaultspeed will give a demonstration of their tool that implements our suggested model using the data set thus showing the viewers how “simple and easy” VaultSpeed as a Data Warehouse Automation tool is.

This webinar is for everyone who wants to learn about Data Warehouse Automation and a sneak peek into VaultSpeed.

Watch Webinar Recording

Webinar Agenda

1. Understanding Data Warehouse Automation
2. Automation in Data Vault 2.0
3. Usecase
4. Vaultspeed Demo

In Data Tools

Running Modern ETL-Processes with Framework-Based Tools – Part 2

Managed Self Service BI image

In the last blog post, we introduced Singer, the open-source framework, as a powerful tool for ETL processes. This time, we’d like to discuss how you can implement the framework in your own projects.

How to start working with Singer

Starting a test run is rather simple. First, you need to create a python environment,  for which step-by-step instructions to do so are available online. 

As soon as you’ve done that, it’s time to create your first virtual environment inside python.
Please note before beginning that it’s a best practice to create and use an individual virtual environment for every tap and target. This avoids any conflicts between module requirements for the different modules. 

The next step is to install the tap and target you’ve chosen into their corresponding virtual environment. This installation can be performed very easily using a pip install command. This example command installs the tap-salesforce to the load data from your Salesforce account:
Continue Reading

In Data Tools

Running Modern ETL-Processes with Framework-Based Tools – Part 1

Data Vault 2.0 Information Delivery Class

A big part of every Enterprise Datawarehouse are ETL- or ELT-processes.
In both abbreviations, the letters stand for the same words, only the order in which each process is done changes.
To brush-up on those processes, “E” stands for extraction, “T” for transformation and “L” is for loading.

That said, rather than dive into the benefits of each,  we would like to present a powerful open-source framework to execute the processes instead.

Why use a framework?

Rather than developing individual solutions per source system, using standardized frameworks provides a wide variety of benefits. The main of which we have already mentioned, standardization.
Another benefit, using the same concept for extracting data from different source systems allows your system to become more auditable and reliable.
And when taking into consideration the varied benefits between frameworks, other potential upsides become available as well. Continue Reading

Close Menu