Skip to content
Contact us Go to Customer Portal
  • There are no suggestions because the search field is empty.

BG_Fact_ForeignKey_Identity

Applies to
Platform: Microsoft SQL Server · Snowflake · Databricks · Fabric Lakehouse · Fabric Warehouse
Architecture: Dimensional

BG_Fact_ForeignKey_Identity is the reference from a Fact to an Entity: the surrogate key of the related Entity row. The loader looks it up from the business key of the relationship.

Objects that carry it

Fact. The term is created for each relationship from the Fact to an Entity, not when the Fact itself is created. Its name has the form <referenced model object>_<role>_SK.

Value

The Lookup view of the Fact joins the Entity on the foreign-key terms of the relationship and takes its BG_Entity_Identity. When the Entity is historized, the Fact needs a mapped BG_EffectiveTimestamp to select the version that is valid at that time. Without the mapping, the lookup selects the current version. A business key without a match gets -1, the key of the unknown-member row.

Data type

  • Microsoft SQL Server, Fabric Warehouse: int
  • Snowflake: INTEGER
  • Databricks, Fabric Lakehouse: long

Example

A Fact on Microsoft SQL Server references the SCD2 Entity common_loyaltycard_scd2. Card 500 has version 1 until 11:00 and version 2 from 11:00. BG_EffectiveTimestamp is mapped:

TRANSACTION_ID  LOYALTYCARD_ID  BG_EffectiveTimestamp  ..._SK
1               500             2026-10-27 10:30       1
2               500             2026-10-27 11:00       2
4               999             2026-10-27 11:00       -1

The column ..._SK is common_loyaltycard_scd2_common_loyaltycard_scd2_SK.

Related articles