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

BG_Sequence

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

BG_Sequence distinguishes the rows of a Multi-Active Satellite that are valid at the same time for one Hub key. It is part of the primary key, so each active row of a Hub key needs its own value.

Objects that carry it

Multi-Active Satellite. Every Multi-Active Satellite gets the term when it is created.

Value

You map BG_Sequence in Manage Term Mappings, either to a source term or to an expression. Validation reports error ID-10007 when it is not mapped. The mapped value must be unique per Hub key within one load, for example the ID of a phone number or a row number per Hub key:

ROW_NUMBER() OVER (PARTITION BY [EMPLOYEE_ID]
    ORDER BY [PHONE_NUMBER])

The primary key of the table consists of Hub_HK, BG_ValidFromTimestamp and BG_Sequence.

The loader treats the active rows of a Hub key as one set. When the row of a sequence is new, changed or missing in the load, all active rows of that Hub key are closed and the complete new set is inserted. Hub keys without a difference are not touched. BG_MAS_HubDifferenceFound marks the affected Hub keys.

Data type

  • Microsoft SQL Server, Fabric Warehouse: int
  • Oracle: NUMBER
  • Snowflake, Snowflake dbt: INTEGER
  • Databricks, Databricks dbt, Fabric Lakehouse: integer

Example

A Multi-Active Satellite on Microsoft SQL Server holds the phone numbers of employees, with BG_Sequence mapped to PHONENUMBER_ID. In the second load, number 101 of employee 7 changes and number 202 of employee 8 is missing. Employee 9 is unchanged:

Employee  BG_Sequence  PHONE_NUMBER      Valid from  Valid to
7         101          +41 79 100 00 01  10:00       11:00
7         101          +41 79 100 00 09  11:00       9999-12-31
7         102          +41 44 100 00 02  10:00       11:00
7         102          +41 44 100 00 02  11:00       9999-12-31
8         201          +41 79 200 00 01  10:00       11:00
8         201          +41 79 200 00 01  11:00       9999-12-31
8         202          +41 44 200 00 02  10:00       11:00
9         301          +41 79 300 00 01  10:00       9999-12-31

The rows come from the Result view, which adds BG_ValidToTimestamp to the table rows. Employee stands for the Hub key, and the times are on 2026-10-27. Employees 7 and 8 get a complete new set at 11:00; employee 9 keeps its row from 10:00.

Release differences

  • Generator 2.4: a new, changed or missing sequence replaces the whole set of the Hub key. Up to Generator 2.3 each sequence had its own timeline, and a missing sequence stayed current.

Related articles