Reference Table

A Reference Table stores one row per business key of simple reference data, such as country codes and names, in a table of the Raw Vault layer, without hash key and without history. Use it for lookup data that many objects need and that does not justify a Hub and a Satellite. After each load, the result view shows the current value of each key.

Sources and relationships

A Reference Table reads Stages, RawVault Composites and Store Entities of a linked Data Store project, in one or more dataflows. It has no relationships, and no other model object has a relationship to it. Vault Out reads it as a source; in a Vault and Mart project, Mart Dimension, Mart Fact and Mart Composite can read it too. Join it in a dataflow by its business key.

Create a Reference Table

• With the wizard: open the menu of a Stage, RawVault Composite or Store Entity, choose Automate and then Create Reference Table. Its steps are Create Model Object, Create Dataflow, Map Selected Business Keys, Map Selected Terms and Add to View. See Create a Model Object with the wizard.
• Manually: create a model object of type Reference Table in the Raw Vault layer, create a dataflow set, add the source model object and map the terms. Mark at least one term as business key.

Loading behavior

The loader updates and inserts:

• A business key that the table does not hold yet is inserted.
• A business key whose row hash BG_RowHash differs from the stored row is updated in place, with the new load timestamp. The old values are not kept.
• A business key that the source no longer delivers stays in the table.

The row hash covers the terms that are not business keys and not Default Terms. See BG_RowHash and How biGENIUS-X builds hash values.

Properties

• Implementation Type: Permanent only.
• Load Caching: empty (default) or Hashing, which stores the hashing view in a table before the loader runs.
• Deduplication Method and Sort Order within Partition, per dataflow.

Foreign Key Mode, Custom Singleton Values and Use Embryo Loader do not apply.

Default Terms

• BG_RowHash: hash of the terms that are not business keys, compared with the stored row.
• BG_LoadTimestamp: the load that inserted or last changed the row.
• BG_SourceSystem: the source system of the row; empty unless you map it.
• BG_DataQuality_ID, BG_ErrorCount and BG_ErrorDescription: hidden terms of the data quality objects.

The business keys form the primary key of the table.

Generated objects

These are the names on Microsoft SQL Server for the Reference Table with_distinct_ref with one dataflow:

• Loaders are stored procedures; the Reference Table and the hashing table are tables; all other objects are views.
• The deduplication view exists only with a Deduplication Method other than None.
• The hashing table and hashing loader exist only with Load Caching Hashing.
• With several dataflows, all objects except the table and the result view exist once per dataflow, with the dataflow name in their names.
• The table has no unknown-member row.

With the default settings, a load runs this chain:

sources -> _Source -> _Hashing -> _Delta -> _Loader
_Loader -> RDV_ReferenceTable_with_distinct_ref -> _Result
_Result -> _Delta (stored rows)

The delta view keeps the source rows whose business key is not in the result view or whose BG_RowHash differs. The loader updates the changed keys and then inserts the new ones, in one transaction. With the optional objects, _Deduplication sits between _Source and _Hashing, and _HashingLoader fills _HashingTable before the delta view reads it.

Example

The Reference Table with_distinct_ref on Microsoft SQL Server reads country codes and names from the Stage EMPLOYEE. COUNTRY_ISO_CODE is the business key and COUNTRY the name. The dataflow has Deduplication Method Distinct, because several employees share a country. The loads deliver these countries:

• Load 1 on 2026-10-28 08:00: CH Switzerland for two employees and DE Germany.
• Load 2 on 2026-10-29 08:00: the same countries and AT Austria.
• Load 3 on 2026-10-31 08:00: the source now names DE Deutschland.

Check the result

Query the result view, here in the schema rv:

SELECT COUNTRY_ISO_CODE, COUNTRY, BG_LoadTimestamp, BG_RowHash
FROM rv.RDV_ReferenceTable_with_distinct_ref_Result
ORDER BY COUNTRY_ISO_CODE;

This is the result after load 3, with BG_RowHash shortened to its first four bytes:

COUNTRY_ISO_CODE  COUNTRY      BG_LoadTimestamp  BG_RowHash
AT                Austria      2026-10-29 08:00  0xE60F2C8A
CH                Switzerland  2026-10-28 08:00  0xDB1812A5
DE                Deutschland  2026-10-31 08:00  0xB570D028

• The deduplication view reduced the two CH rows of load 1 to one.
• Load 3 overwrote Germany with Deutschland and set its load timestamp; the table still holds three rows.

Platform differences

• Snowflake: names are in capitals, and the loader is a stored procedure. The load control task LC_RDV_REFERENCETABLE_<model object name>_TASK calls it. The dataflow properties Use Stream and Warehouse Placeholder Variable Name apply.
• Databricks and Fabric Lakehouse: table and view names are in lowercase, and the loader is the notebook RDV_ReferenceTable_<model object name>_Loader. The properties File Format, Optimization Method, Optimization Method Columns and Use Sequential Dataflow Execution apply.
• Databricks dbt and Snowflake dbt: the Reference Table becomes the dbt models rdv_referencetable_<model object name>_source and rdv_referencetable_<model object name>. The model rdv_referencetable_<model object name>_result exists only when Create Result Views is true; the default on dbt is false. Load Caching 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. Hash Group Size splits the input of BG_RowHash into groups of terms. Partition Clause adds its text to the CREATE TABLE statement. Table Compression, the tablespaces and the statistics properties also apply.

Limitations

• A Reference Table has at least one business key and no relationships. Validation reports an error otherwise; a nullable business key raises a warning.
• A Reference Table keeps no history: changed values overwrite the stored ones.

Related articles

• Previous: Stage, RawVault Composite, Store Entity
• Next: Vault Out, Mart Dimension
• Reference: BG_RowHash, Load Caching (Model Object), Deduplication Method (Dataflow), Hash Group Size (Model Object), Create Result Views (Project)
• See also: Hub, Satellite, How biGENIUS-X builds hash values, Create a Model Object with the wizard, Model a Data Vault Solution