--- title: How biGENIUS-X builds hash values description: The generated loaders calculate hash values to identify business keys and to find changed rows. --- [Skip to content](https://knowledge.bigenius-x.com/how-bigenius-x-builds-hash-values#main-content) [Contact us](https://knowledge.bigenius-x.com/kb-tickets/new?hsLang=en) [Go to Customer Portal](https://knowledge.bigenius-x.com/customer-portal?hsLang=en) [![TRI\_Logo\_biGENIUS\_2020\_RGB\_s](https://knowledge.bigenius-x.com/hs-fs/hubfs/Marketing/TRI_Logo_biGENIUS_2020_RGB_s.png?width=150&height=42&name=TRI_Logo_biGENIUS_2020_RGB_s.png)](https://www.bigenius-x.com/) Open main navigation Close main navigation - [Contact us](https://knowledge.bigenius-x.com/kb-tickets/new) - [Go to Customer Portal](https://knowledge.bigenius-x.com/customer-portal) How can we help you? - There are no suggestions because the search field is empty. 1. [biGENIUS-X Knowledge Base](https://knowledge.bigenius-x.com/?hsLang=en) 2. [Generators](https://knowledge.bigenius-x.com/generators?hsLang=en) 3. [Properties](https://knowledge.bigenius-x.com/generators?hsLang=en#properties) # 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](https://knowledge.bigenius-x.com/hub_hk?hsLang=en) is the hash of the business key terms of a Hub, in the term order of the Hub. - [Link\_HK](https://knowledge.bigenius-x.com/link_hk?hsLang=en) 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](https://knowledge.bigenius-x.com/drivingkeylink_hk?hsLang=en) is the hash of the foreign keys of the relationships marked as driving key. - [PIT\_HK](https://knowledge.bigenius-x.com/pit_hk?hsLang=en) 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: - [BG\_RowHash](https://knowledge.bigenius-x.com/bg_rowhash?hsLang=en) on satellites and Reference Tables in Data Vault. - [BG\_RowHashScd1](https://knowledge.bigenius-x.com/bg_rowhashscd1?hsLang=en) and [BG\_RowHashScd2](https://knowledge.bigenius-x.com/bg_rowhashscd2?hsLang=en) on Store Entities, Entities and Mart Dimensions. ### 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](https://knowledge.bigenius-x.com/hash-trim-project?hsLang=en) set to `true`, leading and trailing spaces are removed. 3. With [Hash Casing](https://knowledge.bigenius-x.com/hash-casing-project?hsLang=en) set to `Upper` or `Lower`, the text is converted to that case. 4. A NULL value is replaced by the [Hash Null Placeholder](https://knowledge.bigenius-x.com/hash-null-placeholder-project?hsLang=en), default `_NULL_`. The placeholder itself is neither trimmed nor converted. 5. The values are joined with the [Hash Concatenation Separator](https://knowledge.bigenius-x.com/hash-concatenation-separator-project?hsLang=en), default `_`. The separator stands only between two values. 6. The [Hash Algorithm](https://knowledge.bigenius-x.com/hash-algorithm-project?hsLang=en) of the project is applied to the joined text. On Oracle, [Hash Group Size](https://knowledge.bigenius-x.com/hash-group-size-model-object?hsLang=en) 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](https://knowledge.bigenius-x.com/generators-release-2.4.0?hsLang=en). ### Related articles - Reference: [Hash Algorithm (Project)](https://knowledge.bigenius-x.com/hash-algorithm-project?hsLang=en), [Hash Concatenation Separator (Project)](https://knowledge.bigenius-x.com/hash-concatenation-separator-project?hsLang=en), [Hash Null Placeholder (Project)](https://knowledge.bigenius-x.com/hash-null-placeholder-project?hsLang=en), [Hash Casing (Project)](https://knowledge.bigenius-x.com/hash-casing-project?hsLang=en), [Hash Trim (Project)](https://knowledge.bigenius-x.com/hash-trim-project?hsLang=en), [Hash Group Size (Model Object)](https://knowledge.bigenius-x.com/hash-group-size-model-object?hsLang=en), [BG\_RowHash](https://knowledge.bigenius-x.com/bg_rowhash?hsLang=en), [BG\_RowHashScd1](https://knowledge.bigenius-x.com/bg_rowhashscd1?hsLang=en), [BG\_RowHashScd2](https://knowledge.bigenius-x.com/bg_rowhashscd2?hsLang=en), [Hub\_HK](https://knowledge.bigenius-x.com/hub_hk?hsLang=en), [Link\_HK](https://knowledge.bigenius-x.com/link_hk?hsLang=en), [Excluded from Delta Detection (Model Object)](https://knowledge.bigenius-x.com/excluded-from-delta-detection-model-object?hsLang=en) - See also: [Change detection and historization](https://knowledge.bigenius-x.com/change-detection-and-historization?hsLang=en), [Delta Detection (Model Object)](https://knowledge.bigenius-x.com/delta-detection-model-object?hsLang=en), [Generators Release - 2.4.0](https://knowledge.bigenius-x.com/generators-release-2.4.0?hsLang=en) - [Getting started](https://knowledge.bigenius-x.com/getting-started?hsLang=en#main-content) - [Guidelines](https://knowledge.bigenius-x.com/getting-started?hsLang=en#guidelines) - [Start working](https://knowledge.bigenius-x.com/getting-started?hsLang=en#start-working) - [Account](https://knowledge.bigenius-x.com/getting-started?hsLang=en#account) - [General overview](https://knowledge.bigenius-x.com/general-overview?hsLang=en) - [Artificial Intelligence](https://knowledge.bigenius-x.com/artificial-intelligence?hsLang=en) - [Application Modules](https://knowledge.bigenius-x.com/application-modules?hsLang=en#main-content) - [Administration](https://knowledge.bigenius-x.com/application-modules?hsLang=en#administration) - [Solutions](https://knowledge.bigenius-x.com/application-modules?hsLang=en#solutions) - [Global Features](https://knowledge.bigenius-x.com/application-modules?hsLang=en#global-features) - [Projects](https://knowledge.bigenius-x.com/application-modules?hsLang=en#projects) - [Branches](https://knowledge.bigenius-x.com/application-modules?hsLang=en#branches) - [Data Connections](https://knowledge.bigenius-x.com/application-modules?hsLang=en#data-connections) - [Dataflow Modeling - Overview](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-overview) - [Dataflow Modeling - Wizard Steps](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-wizard-steps) - [Dataflow Modeling - Terms](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-terms) - [Dataflow Modeling - Term Mapping](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-term-mapping) - [Dataflow Modeling - Relationships](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-relationships) - [Dataflow Modeling - Data Quality](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-data-quality) - [Dataflow Modeling - Indexes](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-indexes) - [Relationship Modeling](https://knowledge.bigenius-x.com/application-modules?hsLang=en#relationship-modeling) - [Generate Artifacts](https://knowledge.bigenius-x.com/application-modules?hsLang=en#generate-artifacts) - [Project Settings](https://knowledge.bigenius-x.com/application-modules?hsLang=en#project-settings) - [Data Marketplace](https://knowledge.bigenius-x.com/application-modules?hsLang=en#data-marketplace) - [Generators](https://knowledge.bigenius-x.com/generators?hsLang=en#main-content) - [Fabric Warehouse](https://knowledge.bigenius-x.com/generators?hsLang=en#fabric-warehouse) - [Fabric Lakehouse](https://knowledge.bigenius-x.com/generators?hsLang=en#fabric-lakehouse) - [Databricks](https://knowledge.bigenius-x.com/generators?hsLang=en#databricks) - [Snowflake](https://knowledge.bigenius-x.com/generators?hsLang=en#snowflake) - [Microsoft SQL Server](https://knowledge.bigenius-x.com/generators?hsLang=en#microsoft-sql-server) - [Artifacts](https://knowledge.bigenius-x.com/generators?hsLang=en#artifacts) - [Replace Placeholders](https://knowledge.bigenius-x.com/generators?hsLang=en#replace-placeholders) - [Target solution environment](https://knowledge.bigenius-x.com/generators?hsLang=en#target-solution-environment) - [Deployment](https://knowledge.bigenius-x.com/generators?hsLang=en#deployment) - [Deployment with an Azuze DevOps pipeline](https://knowledge.bigenius-x.com/generators?hsLang=en#deployment-with-an-azuze-devops-pipeline) - [Delta Deployment](https://knowledge.bigenius-x.com/generators?hsLang=en#delta-deployment) - [Load control environment](https://knowledge.bigenius-x.com/generators?hsLang=en#load-control-environment) - [Load data with a native load control](https://knowledge.bigenius-x.com/generators?hsLang=en#load-data-with-a-native-load-control) - [Load data with Apache Airflow](https://knowledge.bigenius-x.com/generators?hsLang=en#load-data-with-apache-airflow) - [Model Object Type](https://knowledge.bigenius-x.com/generators?hsLang=en#model-object-type) - [Properties](https://knowledge.bigenius-x.com/generators?hsLang=en#properties) - [Default Terms](https://knowledge.bigenius-x.com/generators?hsLang=en#default-terms) - [Discovery application](https://knowledge.bigenius-x.com/discovery-application?hsLang=en#main-content) - [Discovery configurations](https://knowledge.bigenius-x.com/discovery-application?hsLang=en#discovery-configurations) - [Best practices](https://knowledge.bigenius-x.com/best-practices?hsLang=en#main-content) - [Modeling Approaches](https://knowledge.bigenius-x.com/best-practices?hsLang=en#modeling-approaches) - [Use Cases](https://knowledge.bigenius-x.com/best-practices?hsLang=en#use-cases) - [Business Rules](https://knowledge.bigenius-x.com/best-practices?hsLang=en#business-rules) - [Data Quality Rules](https://knowledge.bigenius-x.com/best-practices?hsLang=en#data-quality-rules) - [FAQs](https://knowledge.bigenius-x.com/faqs?hsLang=en) - [Product Release Notes](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#main-content) - [SaaS Application](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#saas-application) - [Discovery application](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#discovery-application) - [Generators](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#generators) - [Legal Documents](https://knowledge.bigenius-x.com/legal-documents?hsLang=en#main-content) - [Current legal docs](https://knowledge.bigenius-x.com/legal-documents?hsLang=en#current-legal-docs) - [Software Product and Limits](https://knowledge.bigenius-x.com/legal-documents?hsLang=en#software-product-and-limits) - [Contact us](https://knowledge.bigenius-x.com/kb-tickets/new?hsLang=en) - [Customer Portal](https://knowledge.bigenius-x.com/customer-portal) - [Sign in](https://knowledge.bigenius-x.com/_hcms/mem/login) [![biGENIUS logo](https://knowledge.bigenius-x.com/hubfs/TRI_Logo_biGENIUS_2020_RGB-1.svg "biGENIUS logo")](https://www.bigenius-x.com) All rights reserved © 2025, biGENIUS AG