--- title: Configure an incremental load description: An incremental load reads only the source rows that arrived since the previous load. --- [Skip to content](https://knowledge.bigenius-x.com/configure-an-incremental-load#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. [Best practices](https://knowledge.bigenius-x.com/best-practices?hsLang=en) 3. [Use Cases](https://knowledge.bigenius-x.com/best-practices?hsLang=en#use-cases) # Configure an incremental load **Applies to** Platform: Microsoft SQL Server · Snowflake · Snowflake dbt · Databricks · Databricks dbt · Fabric Lakehouse · Fabric Warehouse · Oracle An incremental load reads only the source rows that arrived since the previous load. Use it when a full reload of a large or growing source takes too long. After you map the Default Term `BG_IncrementalFilter`, each load stores the highest value it read and the next load starts above it. Snowflake dbt and Databricks dbt filter on `BG_LoadTimestamp` instead, see [Snowflake dbt and Databricks dbt](https://knowledge.bigenius-x.com/configure-an-incremental-load#snowflake-dbt-and-databricks-dbt). The Snowflake Stage generator for files (COPY INTO) does not support incremental loads. ### Prerequisites - A model object of one of these types: - Stage, in every architecture - Data Vault: Satellite, Link Satellite, Multi-Active Satellite, Effectivity Satellite, Effectivity Link Satellite, Non-Historized Satellite, Non-Historized Link Satellite, PIT - Business Vault: Business Satellite, Business Link Satellite, Effectivity Business Satellite, Effectivity Business Link Satellite, Non-Historized Business Satellite, Non-Historized Business Link Satellite, Business PIT - Data Mart: Mart Fact - Dimensional: Fact - The model object has the [Implementation Type](https://knowledge.bigenius-x.com/implementation-type-model-object?hsLang=en) `Cached`, `Permanent` or `FactLoadStrategy`, not `Virtual`. `FactLoadStrategy` is the default of Fact and Mart Fact. - A source term whose value increases with every new or changed row, such as a modification timestamp. ### Configure the increment 1. Open **Manage Terms** of the model object and choose **Edit Term** for `BG_IncrementalFilter`. Every supported model object has this Default Term. 2. Set **DataType** to the data type of the source value, for example `datetimeoffset` on Microsoft SQL Server, and save. ![Edit Term panel of BG\_IncrementalFilter with DataType datetimeoffset](https://knowledge.bigenius-x.com/hs-fs/hubfs/knowledge_base_screenshots/incremental-load-edit-term-datatype.png?width=640&height=290&name=incremental-load-edit-term-datatype.png) 3. Open **Manage Term Mappings**. In every dataflow set, set the **Expression** of `BG_IncrementalFilter` to the source term, for example `s1.TRANSACTION_TIME`. An expression such as `COALESCE(s1.ModifiedDate, s1.OrderDate)` also works. ![Edit Term Mapping panel of BG\_IncrementalFilter with the Expression s1.TRANSACTION\_TIME](https://knowledge.bigenius-x.com/hs-fs/hubfs/knowledge_base_screenshots/incremental-load-term-mapping-expression.png?width=640&height=185&name=incremental-load-term-mapping-expression.png) 4. In **Edit Model Object**, leave **Increment Management Type** at `Auto`, or set it to `User` to [manage the increments yourself](https://knowledge.bigenius-x.com/configure-an-incremental-load#manage-the-increments-yourself). ![Edit Model Object panel of the Stage SALESTRANSACTION with Implementation Type Cached and Increment Management Type Auto](https://knowledge.bigenius-x.com/hs-fs/hubfs/knowledge_base_screenshots/incremental-load-edit-model-object.png?width=620&height=390&name=incremental-load-edit-model-object.png) 5. [Generate the artifacts](https://knowledge.bigenius-x.com/generate-artifacts?hsLang=en), deploy and load as usual. The deployment creates the increment table of the model object. ### How the increment works The increment table is named after the target table with the suffix `_Increment`, in the schema of the target table, for example `STG_ST_SALESTRANSACTION_Increment`. Snowflake and Oracle use upper case, Databricks and Fabric Lakehouse lower case. - Deployment inserts one row per dataflow and dataflow set with `BG_IncrementalFilter` set to NULL. - Each load takes the row with the highest `BG_ID` of its dataflow set. It reads the source rows whose value is above that row's value, or all source rows if the value is NULL. - After reading new rows, the load adds a row with the highest value it read. A load without new rows adds no row. - A source row whose value equals or is below the stored value is not loaded, even when other columns changed. - This also applies to rows that reach the source after a load. Such a row is never read when its value is at or below the value that load stored, for example a second row with the same timestamp. - A source row with a NULL value is read only while the stored value is NULL. - A `Cached` object holds only the rows of the latest load. A `Permanent` object, such as a satellite, keeps the earlier rows. ### Example A Stage `SALESTRANSACTION` on Microsoft SQL Server maps `BG_IncrementalFilter` to `s1.TRANSACTION_TIME`. The source has rows at 10:00, 11:00 and 12:00 on 2026-01-01; rows at 13:00 and 14:00 arrive before the second load. The first load reads three rows, the second two. The row with `BG_ID` 1 comes from the deployment: ``` BG_ID BG_IncrementalFilter BG_DataflowSetName 1 NULL Set1 2 2026-01-01 12:00:00.0000000 +00:00 Set1 3 2026-01-01 14:00:00.0000000 +00:00 Set1 ``` ### Check the result After a load with new source rows, the increment table has a new row with the highest value of the loaded rows: ``` SELECT BG_ID, BG_IncrementalFilter, BG_DataflowSetName FROM stage.STG_ST_SALESTRANSACTION_Increment ORDER BY BG_ID; ``` ### Reload or restart the increment To change where the next load starts, insert a row with the dataflow and dataflow set names of the existing rows. - Reload all source rows: insert a row with `BG_IncrementalFilter` set to NULL. A `Cached` object then holds all of them; a satellite adds only changed versions. - Start after a value: insert a row with that value. The next load reads the rows above it. ``` INSERT INTO stage.STG_ST_SALESTRANSACTION_Increment (BG_LoadTimestamp, BG_IncrementalFilter, BG_DataflowName, BG_DataflowSetName) VALUES (SYSDATETIMEOFFSET(), NULL, 'Dataflow1', 'Set1'); ``` On other platforms, replace `SYSDATETIMEOFFSET()` with their current-timestamp function. On Fabric Warehouse, `BG_ID` is not filled automatically. Set it to the highest `BG_ID` of the table plus 1. With `Auto`, each load needs a row for its dataflow and dataflow set in the increment table. After a truncate, or after all rows of a dataflow set are deleted, the load reads no rows and stores none. Insert a row with a NULL value to load all source rows again. ### Manage the increments yourself Available since Generator 2.2. With Increment Management Type `User`, you set the range of every load. The increment table holds `BG_LowerThreshold`, `BG_UpperThreshold` and `BG_IncrementLoadStatus` instead of `BG_IncrementalFilter`, and it is empty after deployment. Steps 1 to 3 of [Configure the increment](https://knowledge.bigenius-x.com/configure-an-incremental-load#configure-the-increment) still apply. 1. Open **Edit Model Object** and set **Increment Management Type** to `User`. 2. Generate and deploy the artifacts. 3. Before a load, insert one or more ranges per dataflow and dataflow set. The load reads the source rows above `BG_LowerThreshold` and up to and including `BG_UpperThreshold`. NULL means no limit. Both columns have the data type of `BG_IncrementalFilter`. 4. Run the load. It uses the pending range (status NULL) with the lowest `BG_ID` and sets `BG_IncrementLoadStatus` to `done`. Each load processes one range per dataflow set, in the order of `BG_ID`. Use the upper limit of a range as the lower limit of the next, so that no source row is read twice or skipped. A row that reaches the source after its range was loaded is not read. A source row with a NULL value is read only by a range whose limits are both NULL. Without a pending range, the load reads nothing, and a `Cached` object is empty afterwards. For the source rows of the [example](https://knowledge.bigenius-x.com/configure-an-incremental-load#example), these three ranges load the rows in three steps: ``` INSERT INTO stage.STG_ST_SALESTRANSACTION_Increment (BG_LoadTimestamp, BG_DataflowName, BG_DataflowSetName, BG_LowerThreshold, BG_UpperThreshold) VALUES (SYSDATETIMEOFFSET(), 'Dataflow1', 'Set1', NULL, '2026-01-01 11:00:00 +00:00'), (SYSDATETIMEOFFSET(), 'Dataflow1', 'Set1', '2026-01-01 11:00:00 +00:00', '2026-01-01 13:00:00 +00:00'), (SYSDATETIMEOFFSET(), 'Dataflow1', 'Set1', '2026-01-01 13:00:00 +00:00', NULL); ``` The first load reads the rows at 10:00 and 11:00, the second load the rows at 12:00 and 13:00, and the third load the row at 14:00. ### Snowflake dbt and Databricks dbt dbt projects have no increment table and no Increment Management Type. In step 3 of [Configure the increment](https://knowledge.bigenius-x.com/configure-an-incremental-load#configure-the-increment), map `BG_IncrementalFilter` to the source's `BG_LoadTimestamp`. Incremental runs then read only source rows newer than the highest `BG_LoadTimestamp` in the target table. The first run and `--full-refresh` read all rows. - The filter applies to the Data Vault and Business Vault types of the prerequisites. On Databricks dbt, it also applies to Mart Facts with the [Fact Load Strategy](https://knowledge.bigenius-x.com/fact-load-strategy-dataflow?hsLang=en) `Insert`. Stage objects get no filter. - A mapping to any other term adds no filter, and validation shows a warning. - With several dataflows, the highest `BG_LoadTimestamp` in the target table applies to all of them. ### Failure symptoms and recovery In the message, `` and `` stand for the model object that validation reports. #### Validation reports an unsupported data type Message: `Model object '' of type '' has unsupported data type for the term 'BG_IncrementalFilter' defined.` Cause: `BG_IncrementalFilter` still has the data type `unsupported`. Fix: set the data type, see step 2 of [Configure the increment](https://knowledge.bigenius-x.com/configure-an-incremental-load#configure-the-increment), then generate again. #### A load succeeds but reads no rows Cause: with `Auto`, the increment table has no row for the dataflow set, for example after a truncate, or its latest value is above the highest source value. With `User`, no range has the status NULL. Fix: compare the latest increment value with the highest source value. With `Auto`, insert a row with NULL or a lower value; with `User`, insert the next range. #### Changed source rows are not loaded Cause: the value of the mapped term did not increase. Fix: map a term that increases with every change, then [reload the increment](https://knowledge.bigenius-x.com/configure-an-incremental-load#reload-or-restart-the-increment). ### Related articles - Next: [Generate artifacts](https://knowledge.bigenius-x.com/generate-artifacts?hsLang=en) - Reference: [BG\_IncrementalFilter](https://knowledge.bigenius-x.com/bg_incrementalfilter?hsLang=en), [Implementation Type (Model Object)](https://knowledge.bigenius-x.com/implementation-type-model-object?hsLang=en), [Fact Load Strategy (Dataflow)](https://knowledge.bigenius-x.com/fact-load-strategy-dataflow?hsLang=en), [Implementation Type Incremental Filter (Business Rule)](https://knowledge.bigenius-x.com/implementation-type-incremental-filter-business-rule?hsLang=en), [Fact Load Strategy Insert needs Incremental selected (Business Rule)](https://knowledge.bigenius-x.com/fact-load-strategy-insert-needs-incremental-selected-business-rule?hsLang=en) - See also: [Optimize load performance](https://knowledge.bigenius-x.com/optimize-load-performance?hsLang=en), [No delete detection with an incremental load in the source (Business Rule)](https://knowledge.bigenius-x.com/no-delete-detection-with-an-incremental-load-in-the-source-business-rule?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