PIT

A PIT stores, for each business key of a Hub or Link and each point in time, which version of each of its satellites was valid. Use it to join several satellites of the same Hub or Link at a common point in time, for example in a Current View, a History View or a mart. After each load, the PIT table holds one row per key and per start of a satellite version.

Sources and relationships

A PIT has relationships to Satellites or Link Satellites that all belong to the same Hub or Link; validation checks this rule. It has exactly one dataflow, whose dataflow set contains these satellites and can also contain the Hub or Link. Each relationship adds two terms to the PIT, which the dataflow maps from the satellite:

• DVPIT_ForeignKey_Identity, named <satellite name>_<role>_HK, from the Hub_HK or Link_HK of the satellite.
• DVPIT_ForeignKey_LoadDate, named <role>_BG_ValidFromTimestamp, from its BG_ValidFromTimestamp.

<satellite name> is the name of the satellite and <role> the role of the relationship. Current View, History View, Business Current View, Business History View, Business Driving Key Link and Vault Out can read a PIT.

Create the model object

• With the wizard: open the menu of a Hub and choose Automate and then Create PIT from Hub. On a Link, Driving Key Link, Hierarchical Link or Same As Link, choose Create PIT from Link. The steps are Create Model Object, Select Relationship, Create Dataflow and Add to View. See Create a Model Object with the wizard.
• Manually: create a model object of type PIT in the Raw Vault layer, add a relationship to each satellite, create a dataflow set, add the satellites and map the two terms of each relationship.

Loading behavior

• The source view collects every combination of key and BG_ValidFromTimestamp that occurs in any related satellite table. The unknown-member rows of the satellites are left out.
• For each satellite, it takes the latest version whose BG_ValidFromTimestamp is not later than that point in time. A satellite without such a version contributes the key of zero bytes and 1900-01-01, which point to its unknown-member row.
• With Implementation Type Permanent, the delta view keeps the rows whose key and BG_ValidFromTimestamp are not in the table yet, and the loader inserts them. With Cached, the loader empties the table and inserts all rows of the source view.
• The result view adds BG_ValidToTimestamp: the next point in time of the same key, or the End of Universe.

Properties

• Implementation Type: Permanent (default) or Cached, see Loading behavior.
• Foreign Key Mode: whether the table gets a foreign key to each satellite table, on the two terms of the relationship. With Create Disabled, the default, they are created and disabled.
• Increment Management Type: Auto (default) or User, for an incremental load. See Configure an incremental load. Available since Generator 2.2.

The project properties Persist Validity and Create Result Views also apply.

Default Terms

• PIT_HK: the key of the Hub or Link that the satellites belong to. With BG_ValidFromTimestamp it forms the primary key.
• BG_ValidFromTimestamp: the point in time.
• BG_ValidToTimestamp: the next point in time of the same key.
• BG_LoadTimestamp: the load that inserted the row.
• DVPIT_ForeignKey_Identity and DVPIT_ForeignKey_LoadDate: one pair per relationship, see Sources and relationships.

Generated objects

These are the names on Microsoft SQL Server for a PIT with the default settings. <model object name> stands for the name of the model object:

• The loader is a stored procedure, the PIT table is a table, and the other objects are views.
• The delta view exists only with Implementation Type Permanent.
• The table has an index on the two terms of each relationship.
• The result view exists only with Create Result Views; without it, Persist Validity must be true.
• The table has no unknown-member row.

With the default settings, a load runs this chain after the satellites are loaded:

satellite tables and result views -> _Source -> _Delta -> _Loader
_Loader -> RDV_PIT_<model object name> -> _Result

The source view reads the satellite tables for the points in time and their result views for the valid versions. The delta view compares the source view with the PIT table. The loader inserts the new rows with the load timestamp as BG_LoadTimestamp.

Example

The PIT customer_based_on_hub on Microsoft SQL Server belongs to the Hub load_customer_hub. It has relationships to two Satellites of this Hub: load_customer_sat with the role CUSTOMER holds the customer name, and load_caching_hashing with the role CUSTOMER_Hashing holds the address. Persist Validity is false. The loads deliver these customers:

• Load 1 on 2026-10-28 08:00: customer 1 Contoso Ltd at Bahnhofstrasse 1 and customer 2 Fabrikam AG.
• Load 2 on 2026-10-29 08:00: customer 1 is renamed to Contoso Group, and customer 3 Northwind arrives.
• Load 3 on 2026-10-30 08:00: customer 1 moves to Hardturmstrasse 100.
• Load 4 on 2026-10-31 08:00: no change.

Check the result

Query the result view with the business key of the Hub, here in the schema p2:

SELECT h.CUSTOMER_ID, p.BG_ValidFromTimestamp, p.BG_ValidToTimestamp,
       p.CUSTOMER_BG_ValidFromTimestamp,
       p.CUSTOMER_Hashing_BG_ValidFromTimestamp
FROM p2.RDV_PIT_customer_based_on_hub_Result AS p
JOIN p2.RDV_HUB_load_customer_hub_Result AS h
  ON h.Hub_HK = p.PIT_HK
ORDER BY h.CUSTOMER_ID, p.BG_ValidFromTimestamp;

This is the result after load 4, with the column names and timestamps shortened:

ID  ValidFrom         ValidTo           CUSTOMER          CUSTOMER_Hashing
1   2026-10-28 08:00  2026-10-29 08:00  2026-10-28 08:00  2026-10-28 08:00
1   2026-10-29 08:00  2026-10-30 08:00  2026-10-29 08:00  2026-10-28 08:00
1   2026-10-30 08:00  9999-12-31 00:00  2026-10-29 08:00  2026-10-30 08:00
2   2026-10-28 08:00  9999-12-31 00:00  2026-10-28 08:00  2026-10-28 08:00
3   2026-10-29 08:00  9999-12-31 00:00  2026-10-29 08:00  2026-10-29 08:00

• Customer 1 has three points in time: the first load, the new name and the new address. At 2026-10-29 the PIT references the new name version and still the first address version.
• Load 4 changed no satellite and inserted no PIT row.

Platform differences

• Persist Validity is true by default on Snowflake, Snowflake dbt, Databricks, Databricks dbt, Fabric Lakehouse and Fabric Warehouse, and false on Microsoft SQL Server and Oracle. With true, the table stores BG_ValidToTimestamp, and the loader sets it.
• Snowflake: names are in capitals, and the loader is a stored procedure. A load control task calls it. The dataflow property Warehouse Placeholder Variable Name applies.
• Databricks and Fabric Lakehouse: table and view names are in lowercase, and the loader is the notebook RDV_PIT_<model object name>_Loader. The properties File Format, Optimization Method and Optimization Method Columns apply. Fabric Lakehouse allows only Foreign Key Mode None.
• Databricks dbt and Snowflake dbt: the PIT becomes the dbt models rdv_pit_<model object name>_source and rdv_pit_<model object name>. The model rdv_pit_<model object name>_result exists only when Create Result Views is true; the default on dbt is false. Increment Management Type is not available.
• Fabric Warehouse: the same objects as on Microsoft SQL Server, deployed by notebooks.
• Oracle: names are in capitals, and the loader is a PL/SQL package. Partition Clause adds its text to the CREATE TABLE statement. Table Compression, the tablespaces and the statistics properties also apply.
• Foreign Key Mode Create Enforced exists only on Microsoft SQL Server and Oracle.

Limitations

• All related satellites must belong to the same Hub or Link. See Relationships from PIT (Business Rule).
• A PIT has exactly one dataflow. A relationship to the Hub or Link itself raises a validation error.
• A Current View or History View that maps the DrivingKeyLink_HK of a Driving Key Link cannot read a PIT.

Related articles

• Previous: Satellite, Link Satellite
• Next: Current View, History View
• Reference: PIT_HK, DVPIT_ForeignKey_Identity, DVPIT_ForeignKey_LoadDate, Persist Validity (Project), Create Result Views (Project), Foreign Key Mode (Model Object), Relationships from PIT (Business Rule)
• See also: Business PIT, Model a Data Vault Solution, Create a Model Object with the wizard