Tim Kirschke

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.

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!

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:

• Stages
• Hubs
• Links
• Satellites
• PITs
• Snapshot Control Tables

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:

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!

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

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.

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!