DVPIT_ForeignKey_Identity

DVPIT_ForeignKey_Identity is the reference from a PIT row to one of its satellites: the key of the satellite version valid at that point in time. Together with DVPIT_ForeignKey_LoadDate it lets a query join the PIT to exactly one satellite version.

Objects that carry it

PIT and Business PIT. The term is created for each relationship from the PIT to a satellite, not when the PIT itself is created. Its name in the example below is load_customer_sat_CUSTOMER_HK, for the satellite load_customer_sat and the role CUSTOMER.

Value

The PIT Source view joins each satellite on the PIT key and takes the latest satellite version whose BG_ValidFromTimestamp is not later than the point in time of the PIT row. The term holds the Hub_HK or Link_HK of that version, so it equals PIT_HK when a version exists. When the satellite has no version yet at that point, the term holds the unknown-member key of zero bytes.

The PIT table has an index on this term and the matching DVPIT_ForeignKey_LoadDate for each satellite.

Data type

The data type must match the length of the selected Hash Algorithm.

Example

A PIT on Microsoft SQL Server has the satellites load_customer_sat (role CUSTOMER) and load_caching_hashing (role CUSTOMER_Hashing). Customer 941 has a new customer type at 11:00 and an unchanged address. Both PIT rows reference the satellite key of customer 941 (hash values shortened):

PIT_HK        BG_ValidFromTimestamp  load_customer_sat_CUSTOMER_HK
0xD2CD1706... 2026-10-27 10:00       0xD2CD1706...
0xD2CD1706... 2026-10-27 11:00       0xD2CD1706...

Release differences

• Generator 2.4: Snowflake uses SHA2-256 with BINARY(32) by default. Up to Generator 2.3 the default was HASH with a numeric type, see Generators Release - 2.4.0.
• Generator 2.2: Databricks and Fabric Lakehouse store hash values as binary. Up to Generator 2.0 they were string.

Related articles

• Reference: PIT_HK, DVPIT_ForeignKey_LoadDate, Hub_HK, Hash Algorithm (Project)
• See also: Create a PIT, Create a Business PIT