Point-In-Time Tables

Point-in-Time or PIT tables serve as query assistance tables in data vault modeling to facilitate easier querying of historical data.

Satellite tables store changes in attributes over time along with the effective date of the change, thus ensuring that the entire history of changes is preserved. However, this can make querying historical data complex, especially when dealing with multiple Satellite tables.

A PIT table is created for a specific Hub or Link and their associated Satellites. It provides a snapshot of the data at a specific point in time by consolidating loaded dates from Satellite tables, essentially creating a flattened, denormalized view of the data. It contains loaded dates and corresponding hash keys from each Satellite that corresponds best with the snapshot date. The PIT table is populated by querying Satellite tables and determining the state of each attribute at the specified point in time.

Use-Case

1. To add a PIT table to a data vault, right-click on the Hub or Link consisting of more than one Satellites and select the Create Point-in-Time entity option from the context menu.

If there are less than two Satellites linked to a Hub or Link, the Create Point-in-Time entity option will be disabled.

2. Once a PIT table is added to an entity, it will appear automatically connected to the related entity and Satellites via ghost relationships. These relationships are indicated by blue, dash-dotted lines. These are not actual relationships and simply represent a linkage between the PIT table, Satellites, and the Hub or Link.

3. To configure the PIT entity’s properties, either double-click the entity header, or right-click the entity header and select Properties from the context menu.

4. The PIT entity’s properties window provides the same options as it does for a general entity. On the Entity Properties screen, you can view and edit the table name and schema, but you cannot edit the entity type as doing so would alter the properties of the PIT table, such as entity roles

5. To proceed to the Layout Builder, Click Next.

In the layout of this PIT table, you will notice a few foreign keys, such as Employees_Key, EmployeesSatellite1_LoadedDate, etc. Each of these foreign keys represents a relationship between the PIT table, one of the Satellite tables, and the related Hub or Link.

For example, the Employees_Key represents the primary key of Employees Hub, the EmployeesSatellite1_LoadedDate represents one of the primary keys of EmployeesSatellite1 Satellite, etc.

The LoadedDate field represents a snapshot date of the PIT table, and it forms the primary key of the table along with the hash key related to the Hub or Link.

Note: You cannot edit field names and properties of the PIT table as this would create conflict between the layout of the PIT table and its related Satellites and Hub/Link which would result in erroneous loads.

If there is a change in the properties of the Hub or Link related to the PIT table, it is necessary to refresh the layout of the PIT table before forward engineering it to a database.

6. To refresh the PIT table layout, right-click the PIT table and select the Refresh satellite fields option from the context menu.

7. You can populate a PIT table using the DVTableLoad object and loading its related Satellites.

8. To view the PIT table data, right-click the PIT table and select Database Table -> View Table Data from the context menu. If one of the Satellites is not loaded, the PIT table stores NULL values for the loaded date column of that Satellite.

Once all the Satellites are loaded and the PIT table is completely populated, the data would look like this:

9. Additionally, when loading the Satellites, you can monitor the loading of the PIT table from the Job Progress window.

This concludes working with PIT tables in Astera Data Stack.