--- title: Change detection and historization description: The generated loaders compare each source row with the current version of its key and decide whether to insert a version, update one or do nothing. --- [Skip to content](https://knowledge.bigenius-x.com/change-detection-and-historization#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) # Change detection and historization **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 compare each source row with the current version of its key and decide whether to insert a version, update one or do nothing. This article explains how the comparison works and how historization types, multi-version loads and Implementation Type change it. Read it before you choose Delta Detection, Historization Type or Excluded from Delta Detection. ### How a row is compared Store Entity, Entity and Mart Dimension use the model-object property [Delta Detection](https://knowledge.bigenius-x.com/delta-detection-model-object?hsLang=en): - `Hash Value Comparison` (default): the loader compares [BG\_RowHashScd1](https://knowledge.bigenius-x.com/bg_rowhashscd1?hsLang=en) and [BG\_RowHashScd2](https://knowledge.bigenius-x.com/bg_rowhashscd2?hsLang=en) of the source row with the stored values. - `Column Comparison`: the loader compares every SCD1 and SCD2 term column by column. Nullable columns are compared NULL-safe. The object has no row-hash columns. Data Vault satellites always compare [BG\_RowHash](https://knowledge.bigenius-x.com/bg_rowhash?hsLang=en). [Excluded from Delta Detection](https://knowledge.bigenius-x.com/excluded-from-delta-detection-model-object?hsLang=en) removes selected terms from it. [How biGENIUS-X builds hash values](https://knowledge.bigenius-x.com/how-bigenius-x-builds-hash-values?hsLang=en) describes the hash input. The two methods can disagree. A hash changes with every character, so a trailing space or a change in letter case is a change. A column comparison uses the comparison rules of the platform. On Microsoft SQL Server with a case-insensitive collation, values that differ only in letter case or in a trailing space compare as equal. ### What each historization type does Each term of a Store Entity, Entity or Mart Dimension has a [Historization Type](https://knowledge.bigenius-x.com/historization-type-term-and-relationship?hsLang=en): - SCD2: a changed value closes the current version and starts a new one. - SCD1: a changed value overwrites the term in every version of the key. It starts no version. - SCD0: the value of the first version is kept, also when it is NULL and later loads deliver a value. One object can mix the three types. When SCD1 and SCD2 values change in the same load, the loader starts a new version and overwrites the SCD1 term in all versions. ### Several versions in one load With a [multi-version load](https://knowledge.bigenius-x.com/configure-a-multi-version-load?hsLang=en), a source delivers several versions of a key. The loader merges consecutive versions with equal values into one version. A delivered version that equals the stored current version starts no version. ### Unknown-member rows Deployment inserts one unknown-member row into Data Vault Hubs, Links and Satellites and into Mart Dimensions. A Mart Fact row without a matching dimension row references it. [Custom Singleton Values](https://knowledge.bigenius-x.com/custom-singleton-values-model-object?hsLang=en) sets its values. ### Cached and Permanent [Implementation Type](https://knowledge.bigenius-x.com/implementation-type-model-object?hsLang=en) decides whether a comparison happens at all. `Permanent` compares with the stored rows and keeps the history. `Cached` empties the table and reloads it on every load, without a comparison. A Cached Mart Dimension therefore keeps only the history its sources deliver, and each load assigns new surrogate keys. Store Entity and Entity allow only `Permanent`. ### Example A Store Entity on Microsoft SQL Server has the SCD1 term `CUSTOMER`, the SCD0 term `CUSTOMER_TYPE` and the SCD2 term `ADDRESS_LINE1`. It is deployed twice, once with each Delta Detection value. Three loads deliver customer 1: ``` Load CUSTOMER CUSTOMER_TYPE ADDRESS_LINE1 10:00 Contoso Ltd Business Main Street 1 11:00 Contoso Group Enterprise Main Street 3 12:00 Contoso Group Enterprise Main Street 3 (trailing space) ``` The result view with Hash Value Comparison shows three versions. The first version of a key starts at the [Begin of Universe](https://knowledge.bigenius-x.com/begin-of-universe-project?hsLang=en), here 1900-01-01: ``` CUSTOMER TYPE ADDRESS_LINE1 ValidFrom ValidTo Contoso Group Business 'Main Street 1' 1900-01-01 11:00 Contoso Group Business 'Main Street 3' 11:00 12:00 Contoso Group Business 'Main Street 3 ' 12:00 9999-12-31 ``` With Column Comparison, the load at 12:00 changes nothing, and the second version stays current: ``` CUSTOMER TYPE ADDRESS_LINE1 ValidFrom ValidTo Contoso Group Business 'Main Street 1' 1900-01-01 11:00 Contoso Group Business 'Main Street 3' 11:00 9999-12-31 ``` In both, `CUSTOMER` shows the new name in every version, and `CUSTOMER_TYPE` keeps `Business`. ### Related articles - Reference: [Delta Detection (Model Object)](https://knowledge.bigenius-x.com/delta-detection-model-object?hsLang=en), [Historization Type (Term and Relationship)](https://knowledge.bigenius-x.com/historization-type-term-and-relationship?hsLang=en), [Excluded from Delta Detection (Model Object)](https://knowledge.bigenius-x.com/excluded-from-delta-detection-model-object?hsLang=en), [Implementation Type (Model Object)](https://knowledge.bigenius-x.com/implementation-type-model-object?hsLang=en), [Custom Singleton Values (Model Object)](https://knowledge.bigenius-x.com/custom-singleton-values-model-object?hsLang=en), [BG\_ValidToTimestamp](https://knowledge.bigenius-x.com/bg_validtotimestamp?hsLang=en) - See also: [How biGENIUS-X builds hash values](https://knowledge.bigenius-x.com/how-bigenius-x-builds-hash-values?hsLang=en), [Configure a multi-version load](https://knowledge.bigenius-x.com/configure-a-multi-version-load?hsLang=en), [Mart Dimension](https://knowledge.bigenius-x.com/create-a-mart-dimension?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