BG_EffectiveTimestamp

Applies to
Platform: Microsoft SQL Server · Snowflake · Databricks · Databricks dbt · Fabric Lakehouse · Fabric Warehouse · Oracle
Architecture: Dimensional · Data Mart

BG_EffectiveTimestamp is the point in time at which a row looks up a historized related object. Map it to the business time of the row, so that the row references the version that was valid then.

Objects that carry it

Entity and Fact in Dimensional, Mart Fact in Data Mart. Every object of these types gets the term when it is created. It is part of the Source view and the Lookup view; the loader does not store it in the table.

Value

You map BG_EffectiveTimestamp in Manage Term Mappings, for example to a transaction time. Without a mapping, the Source view uses the current time of the platform:

Microsoft SQL Server: SYSDATETIMEOFFSET()
Fabric Warehouse: SYSDATETIMEOFFSET() for Entity and Fact, SYSDATETIME() for Mart Fact
Oracle: SYSTIMESTAMP
Snowflake: CURRENT_TIMESTAMP for Entity and Fact, current_timestamp() for Mart Fact
Databricks, Databricks dbt, Fabric Lakehouse: current_timestamp()

The Lookup view joins a historized related object with this condition:

BG_EffectiveTimestamp >= BG_ValidFromTimestamp
AND BG_EffectiveTimestamp < BG_ValidToTimestamp

A value equal to the start of a version selects that version. A value equal to the end of a version selects the next version. A row without a matching version gets the foreign key -1, the key of the unknown-member row.

When the term is not mapped and the object has a relationship to a historized object, validation shows warning ID-10028: relations are resolved to the latest version.

Data type

Platform	Type
Microsoft SQL Server	`datetimeoffset`
Fabric Warehouse	`datetime2(6)`
Oracle	`TIMESTAMP`
Snowflake	`TIMESTAMP_TZ`
Databricks, Databricks dbt, Fabric Lakehouse	`timestamp`

Example

A Fact on Microsoft SQL Server references an SCD2 Entity of loyalty cards. Card 500 has version 1 until 11:00 UTC and version 2 from 11:00. Two Facts read the same rows: one maps BG_EffectiveTimestamp, the other does not.

LOYALTYCARD_ID  BG_EffectiveTimestamp  SK (mapped)  SK (not mapped)
500             2026-10-27 10:30       1            2
500             2026-10-27 11:00       2            2
500             2026-10-27 12:00       2            2
999             2026-10-27 11:00       -1           -1

SK is the foreign key common_loyaltycard_scd2_common_loyaltycard_scd2_SK of the Fact; version 1 has the key 1, version 2 the key 2. Card 999 does not exist. The times are UTC.

Reference: BG_ValidFromTimestamp, BG_ValidToTimestamp, BG_Fact_ForeignKey_Identity, BG_MartFact_ForeignKey_Identity, Effective Timestamp mapped (Business Rule)
See also: Entity, Fact, Mart Fact