05 Jun How AutomateDV™ uses PITs and Bridges to enhance your Data Vault

How AutomateDV™ uses PITs and Bridges to enhance your Data Vault

Point-In-Time (PIT) and Bridge tables are available in AutomateDV™ (formerly known as dbtvault) to enhance the performance of your Data Vault. Point-In-Time (PIT) and the Bridge tables sit in the Business Vault, acting as performance enhancing interfaces between the information (Data Marts) and the Raw Vault. Although both have similar structures, they achieve different things. The PITs identify valid data on specific dates, whereas Bridges identify valid relationships on specific dates. In this blog, we’ll evaluate what they are, and how to use PITs and Bridges to enhance your Data Vault with AutomateDV.

Point-In-Time (PIT) Tables

Each Hub in Data Vault 2.0 has descriptive data residing in several Satellites – it’s common for each Satellite with a different source and/ or a rate of update. One Satellite may be updated yearly, another monthly, and another daily.

This creates a significant difference in the size of the Satellite – resulting in serious inefficiencies when querying against the suite of Satellites (e.g., a sales monthly report.).

To avoid the cost query, you can build a PIT table that records the state of each Satellite at points-in-time. Thus, instead of searching for the relevant records every time a business report is run, you can speed up the process by building the PIT table once and extracting the relevant keys from it prior to running business reports.

Bridge Tables

Bridge Table identifies relationships between different entities on a point-in-time basis. This means that instead of looking at the actual data as per a PIT table, the bridge looks at the validity of the relationship between different hubs at specific moments.

For example, if your customer places an order containing products A and B (on 1 June 2023), this can now be considered a relationship between the customer, the order, and the product Hub. If your customer changes their mind and removes product A from the order a day later, a new relationship is created. The previous relationship becomes inactive, and the new relationship becomes active.

To avoid the tedious task of searching through the data trying to identify the active relationships for each day, you can loop through it once at the beginning of your business report and save the relevant keys of the active relationships for each date in a bridge table.

Summary

In summary, the value of the PIT and Bridge tables resides in the fact that they store the coordinates of the relevant records for a specific time window until the cycle of business reports finishes. When the next cycle of business reports starts, the PITs and Bridges are rebuilt for the updated time window. They will facilitate the creation of the new set of business reports.

The inclusion of this much requested functionality helps to make AutomateDV™ a complete solution to real-world business problems and is an exciting development for the product. Get started with building your own Data Vault with AutomateDV™ (formerly known as dbtvault) now by clicking here.