In this example, there are multiple satellites on each hub and link included in the diagram. This is a very common situation for data warehouse solutions, because they integrate data from multiple source systems. However, this situation increases the complexity when querying the data out of the Raw Data Vault. The problem arises because the changes to the business objects stored in the source systems don’t happen at the same time. Instead, a business object, such as a passenger, is updated in the Domestic Flight system at a given time, then updated in the International Flight system, etc. Note that the PIT table is already attached to the hubs, as indicated by the ribbon. Tables 6.1, 6.2, 6.3 and 6.4 show data views of the updates to the passenger hub and related satellites.

The example shows two passengers and the changes to their data over time. For example, Amy Miller probably married a Mr. Freeman and subsequently changed her name to Amy Freeman (see Table 6.2). She also changed her preferred dish over the time, from vegetarian food to meat and back to vegetarian food (see Table 6.3). Both passengers moved (or at least gave changing addresses) over time (see Table 6.4).

Changes came in at various times, not related to each other. Most updates would be added when bookings were performed, but they did not affect all operational systems at the same time. And as a consequence, a change did not affect all satellites. Instead, it affected only the satellite that was supposed to cover the change, an advantage that we have already discussed in Chapter 4.

In addition, satellites do not cover all changes that occur in a source system. Instead, the changes or deltas captured by the satellites are only as frequent as the feeds that supply them. Satellites track only those changes that are delivered to the data warehouse. If the warehouse needs to keep records of all changes on the source system, the source system needs to supply an audit log, such as the one produced by Microsoft SQL Server 2014 with Change Data Capture (CDC) turned on. The other option is to provide the data in real-time using an Enterprise Service Bus (ESB) or Message Queue (MQ). If these options are not used, only those changes included in the source extracts are loaded. For example, if the batch loads run every six hours, then the deltas in the satellites will only track the record as it stands every six hours.

When building an information mart from this raw data, querying the passenger state on a given date, e.g. January 5th, 2006, becomes complicated: the query should return the customer data as it was active according to the data warehouse delta process on the selected date. It requires OUTER JOIN queries with complex time range handling involved to achieve this goal. With more than three satellites on a hub or link, this becomes complicated and also slow. The better approach is to use equal-join queries for retrieving the data from the Raw Data Vault. To achieve this, a special entity type is used in Data Vault modeling: point-in-time tables (PIT) and a set of ghost records in satellite tables attached to fixed primary keys.

This entity is introduced to a Data Vault model whenever the query performance is too low for a given hub or link and surrounding satellites. In the model, point-in-time tables are added to each hub or link where the PIT table should be calculated. Because the data in a PIT table is system-computed and is not originating from a source system, the data is not to be audited. The purpose of this table is to provide performance only.

Unlike PIT tables, which span across multiple satellites of a hub or link, a bridge table spans across multiple hubs and links. By doing so, it is similar to a specialized link table. It doesn’t contain any information from satellites, primarily because the table width would become too large. Figure 6.5 shows a logical model of a bridge table in Data Vault 2.0.

In the above scenario, there is Passenger data linked with the Sales Agent via the Booking link. Satellites are used for most hubs and links in this example. The Passenger Bridge doesn’t span over Flight and Airline. The model doesn’t show the business keys or any other attributes that are part of the Data Vault entities. The bridge table acts as a higher-level fact-less fact table and contains hash keys from the hubs and links it spans.

Another performance improvement can be achieved by adding computations to the bridge table that take much time. This is especially important when creating virtual information marts on the Data Vault because these require some computing, which slows down the access to the virtualized information mart entities. Using bridge tables, the query performance can be drastically improved.

Bridge tables are not required to have the same grain as the links that they are covering. In these cases, the bridge table might contain aggregated values that are added to the structure and loaded using GROUP BY statements. The resulting bridge table has a higher grain than the links that are included in the table. By doing so, the bridge table becomes very similar to an exploration link which has been covered in Chapter 5, Intermediate Data Vault Modeling.

Note that loading the bridge table will cause the Cartesian product of all business keys involved in associated hubs. Therefore, the query should be limited by a WHERE clause in the statement. Instead of loading all possible combinations, the statement should focus on the grain required for the fact table, that is, based on the bridge. If there are multiple fact tables with different grains in the information mart, there should be multiple bridge tables (at least one bridge table per grain definition).

We have introduced the hub in Chapter 4 as a unique list of business keys, identifying objects that are used in business. However, there are more keys and codes in enterprise data that don’t necessarily qualify as business keys because they don’t reference business objects. For example, ISO country codes, such as USA for the United States or DEU for Germany, are codes that are used in business, but the countries themselves are not used as business objects within the organization. Instead, they are used as descriptive reference data that delineate a specific state of information. In the case of country codes, the ISO code could describe the country where a sale had taken place. This description usually includes the official name of the country and some other more descriptive information, such as the continent or the capital. Often, this reference data is not controlled by the organization, but by an external body. On the other hand, the very same country code could be a business key in another organization, such as the United Nations Organization (UNO).

Reference data is not purely descriptive. It lives in the context of other information. The country information without the context of the sales transaction would be of no value to the business. Or to rephrase this statement: what is the value to the business of an unused list of country codes with their corresponding official names? It’s zero. But if the country code is used in other data, such as the sales transactions, it provides value by adding additional descriptive data to the business. But they don’t qualify as business keys because they are not used by business objects; therefore, they usually don’t go into hub structures.

This is where reference tables come into play. Reference tables are used to store information that is commonly used to set up context and describe other business keys. In many cases, these are standard codes and descriptions or classifications of information.

The next sections describe some options for reference tables.