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

How biGENIUS-X builds hash values

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

The generated loaders calculate hash values to identify business keys and to find changed rows. This article explains which terms go into a hash, how their values are prepared and joined, and what a change of the hash settings does. Read it before you change Hash Algorithm or another setting of the Hashing group.

Stage projects calculate no hash values.

Key hashes and row hashes

A key hash identifies a business key:

  • Hub_HK is the hash of the business key terms of a Hub, in the term order of the Hub.
  • Link_HK is the hash of the terms marked as business keys of a Link, in its term order. Usually these are the foreign keys to the Hubs; further business keys of the Link are included.
  • DrivingKeyLink_HK is the hash of the foreign keys of the relationships marked as driving key.
  • PIT_HK is not calculated. A PIT copies the key of the Hub or Link that its satellites belong to.

A foreign key to a Hub or Link is hashed from the business keys of that object, in its term order, so that it matches the stored key.

A row hash covers the descriptive terms of a row. The loader compares it with the stored value of the current version to find changed rows:

How the input is built

Each term value goes through these steps:

  1. A value that is not a string is converted to text: NVARCHAR(4000) on Microsoft SQL Server, VARCHAR(4000) on Fabric Warehouse, VARCHAR on Snowflake and String on Databricks and Fabric Lakehouse. Oracle converts dates with TO_CHAR(..., 'YYYYMMDDHH24MISS'), timestamps with the format 'YYYYMMDDHH24MISSFF9' and other values to VARCHAR2(4000).
  2. With Hash Trim set to true, leading and trailing spaces are removed.
  3. With Hash Casing set to Upper or Lower, the text is converted to that case.
  4. A NULL value is replaced by the Hash Null Placeholder, default _NULL_. The placeholder itself is neither trimmed nor converted.
  5. The values are joined with the Hash Concatenation Separator, default _. The separator stands only between two values.
  6. The Hash Algorithm of the project is applied to the joined text.

On Oracle, Hash Group Size can split a row hash into group hashes. Key hashes are never split.

Example

A Store Entity on Microsoft SQL Server has the SCD1 terms CUSTOMER, CUSTOMER_TYPE and CUSTOMER_SINCE, all hash settings at their defaults. The generated Hashing view calculates BG_RowHashScd1 as follows:

CAST(HASHBYTES('SHA1', CAST('' AS VARCHAR(MAX))
    + ISNULL([CUSTOMER], N'_NULL_')
    + N'_' + ISNULL([CUSTOMER_TYPE], N'_NULL_')
    + N'_' + ISNULL(CAST([CUSTOMER_SINCE] AS NVARCHAR(4000)),
        N'_NULL_'))
    AS VARBINARY(20)) AS [BG_RowHashScd1]

Two rows give these inputs and values; the second has no CUSTOMER_SINCE:

Input text                       BG_RowHashScd1
Contoso Ltd_Business_2019-03-01  0x899BA91004D0E112E456E7CA74BBA6E6A2A241EB
Fabrikam AG_Business__NULL_      0xC30414DA639E2317F6C462216ED968E86A505BD5

Values that hash alike

These inputs produce the same hash value, because they give the same text:

  • A NULL value and a value equal to the Hash Null Placeholder.
  • Values that contain the separator and join to the same text, for example x_y and z in one row and x and y_z in another, both x_y_z.
  • With Hash Trim or Hash Casing, values that differ only in leading or trailing spaces or in letter case.
  • On Oracle, an empty string and NULL, because Oracle stores an empty string as NULL. With Hash Trim, a value of spaces only is also NULL on Oracle.

Without Hash Trim and Hash Casing, a change in trailing spaces or in letter case creates a new version on Microsoft SQL Server, even where a comparison with = treats the values as equal.

The same data can give different hash values on different platforms. Microsoft SQL Server hashes Unicode text, Oracle the text in the database character set. The business key 1 has the SHA1 key hash 0xE91FE173… on Microsoft SQL Server and 356A192B… on Oracle.

Changing the hash settings

Stored hash values are not recalculated. On Microsoft SQL Server, a new separator gives every Store Entity and satellite row with two or more inputs a new version on the next load. A new algorithm also changes every key hash, and the Hub load then fails on the unique constraint of its business key.

Changing Hash Algorithm, Hash Concatenation Separator, Hash Null Placeholder, Hash Casing or Hash Trim changes the hash values that the generated loaders calculate. On objects that already hold data, the stored hash keys no longer match the new ones, and the next load treats the affected rows as changed. Change these settings before the first load, or plan a full reload of all objects of the project.

Release differences

  • Generator 2.2: Hash Concatenation Separator, Hash Null Placeholder, Hash Casing and Hash Trim are available. Their defaults reproduce the input of earlier releases, so hash values stay the same.
  • Generator 2.2: Databricks and Fabric Lakehouse store hash values as binary. Up to Generator 2.0 they were string.
  • Since Generator 2.3, Microsoft SQL Server and Fabric Warehouse hash the complete input.
  • 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.

Related articles