Bridge tables serve as query assistance tables to facilitate the handling of complex many-to-many relationships and hierarchical data structures in a data vault.
Relationships between business objects are represented using Links, which store the associations between Hubs.
However, when querying data from Satellites, for example, the number of joins involving multiple Hubs and Links can increase which can impact query performance negatively.
A bridge table provides a way to navigate relationships in a data vault by becoming an intermediate table that connects multiple Hubs and their associated Links.
It contains hash keys from the Hubs and Links it spans and also stores business keys of the Hubs. The bridge table is populated by querying the relevant Hubs and Links to determine the relationships that need to be represented. Once it has all the relationships, the bridge table reduces the number of joins required to query the Satellites, thereby improving query performance.
To add a bridge table to a data vault, select the Hubs you want the bridge table to cover, then right-click on any one of the selected Hubs and select the Create DV Bridge option from the context menu.
Note: If the selected Hubs are not directly connected to each other through common Links, the bridge table will not be created. This is because bridge tables can only store direct relationships between Hubs.
Once a bridge table is added, it will appear connected to the related Hubs and Links by means of ghost relationships. These are not actual relationships and simply represent a linkage between the bridge table, Hubs, and Links.
To configure the bridge table, right-click the table’s header and select Properties from the context menu or double-click the table header.
A configuration window will open. On the first screen, you can view and edit the table name and schema, but you cannot edit the entity type because doing so would alter the properties of the bridge table, such as entity roles.
Click Next to proceed to the Layout Builder.
In the layout of this bridge table, you will notice a few foreign keys, such as CustomersHub_Customers_Key, OrdersHub_Orders_Key, etc. Each of these foreign keys represents a relationship between the bridge table and the related Hub. For example, the CustomersHub_Customers_Key represents the primary key of Customers Hub.
The Snapshot field represents the snapshot date of the bridge table, and it forms the primary key of the table along with the incoming hash keys from the related Hubs and Links.
Note: You cannot edit the field names and properties of the bridge table as this would cause conflict between the layouts of the bridge table and its related Hubs and Links. This would further result in erroneous loads.
If there is a change in the properties of the Hub or Link related to the bridge table, it is necessary to refresh the layout of the bridge table before forward-engineering it to a database.
To refresh the bridge table layout, right-click the bridge table header and select the Refresh Bridge Table option from the context menu.
You can populate a bridge table by loading its related Links. This can be done through the Data Vault Table Loader object.
To view the bridge table data, right-click the bridge table header and select Database Table -> View Table Data from the context menu.
Note: The bridge table will not be populated if even one of the Links is not loaded. This is because bridge tables incorporate inner joins to load.
Once all the Links are loaded, the data in the bridge table will look like this:
Additionally, when the Links are loading, you can monitor the loading of the bridge table in the Job progress window.
This concludes using bridge tables in Astera Data Stack.
