--- title: Fact description: A Fact loads measures into a table of the Core layer and replaces the business key of each referenced Entity by that Entity's surrogate key. --- [Skip to content](https://knowledge.bigenius-x.com/fact#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. [Model Object Type](https://knowledge.bigenius-x.com/generators?hsLang=en#model-object-type) # Fact **Applies to** Platform: Microsoft SQL Server · Snowflake · Databricks · Fabric Lakehouse · Fabric Warehouse Architecture: Dimensional A Fact loads measures into a table of the Core layer and replaces the business key of each referenced Entity by that Entity's surrogate key. Use it for transactions and events that you analyze along Entities. After a load, each Fact row holds the surrogate key of the Entity version that was valid at the row's `BG_EffectiveTimestamp`. ### Sources and relationships A Fact reads [Stages](https://knowledge.bigenius-x.com/stage?hsLang=en), [Core Composites](https://knowledge.bigenius-x.com/core-composite?hsLang=en) and [Store Entities](https://knowledge.bigenius-x.com/store-entity?hsLang=en) of a linked Data Store project, in one or more dataflows. - Relationships: a Fact references at least one [Entity](https://knowledge.bigenius-x.com/entity?hsLang=en), each with a role. The foreign key terms of a relationship are mapped from the source and hold the business key of the Entity. - Readers: no Entity, Fact or Core Composite reads a Fact. Mart Composites, Mart Dimensions and Mart Facts read it in a Dimensional and Mart project, or as a linked object in a Data Mart project. ### Create a Fact - With the wizard: open the menu of a Stage, Core Composite or Store Entity, choose **Automate** and then **Create Fact**. The wizard creates the relationships to the selected Entities with their roles and maps the foreign key terms and the measures. See [Create a Model Object with the wizard](https://knowledge.bigenius-x.com/create-a-model-object-with-the-wizard?hsLang=en). - Manually: [create a model object](https://knowledge.bigenius-x.com/create-a-model-object?hsLang=en) of type Fact in the Dimensional column, [create a dataflow set](https://knowledge.bigenius-x.com/create-a-data-flow-set?hsLang=en), [add the source model objects](https://knowledge.bigenius-x.com/add-a-model-object-into-a-data-flow-set?hsLang=en), [add a relationship](https://knowledge.bigenius-x.com/add-a-relationship?hsLang=en) to each Entity and [map the terms](https://knowledge.bigenius-x.com/map-a-source-term-with-a-target-term?hsLang=en), including the foreign key terms. Business keys are optional on a Fact. Deduplication Method `Partition` uses them to keep one row per business key; without business keys, it partitions by all terms and validation reports a warning. ### Entity lookup The lookup view joins the result view of each referenced Entity on the business key: - With `BG_EffectiveTimestamp` mapped, a row gets the version of a historized Entity whose `BG_ValidFromTimestamp` is at or before the effective timestamp and whose `BG_ValidToTimestamp` is after it. - Without the mapping, a row gets the latest version, and validation reports the warning `Relations will be resolved to the latest version in related model object.` - A business key without a matching Entity row gets the surrogate key -1, the unknown-member row of the Entity. [Custom Singleton Values](https://knowledge.bigenius-x.com/custom-singleton-values-model-object?hsLang=en) sets the values of that row, and the rule [Missing Foreign Keys](https://knowledge.bigenius-x.com/missing-foreign-keys-data-quality-rule?hsLang=en) logs such rows. ### Properties - [Fact Load Strategy](https://knowledge.bigenius-x.com/fact-load-strategy-dataflow?hsLang=en), per dataflow: `Truncate Insert` (default) empties the table and reloads all source rows. `Insert` appends rows without emptying the table; with a mapped [`BG_IncrementalFilter`](https://knowledge.bigenius-x.com/bg_incrementalfilter?hsLang=en) it appends only the increment. See [Configure an incremental load](https://knowledge.bigenius-x.com/configure-an-incremental-load?hsLang=en). - [Increment Management Type](https://knowledge.bigenius-x.com/increment-management-type-model-object?hsLang=en): `Auto` (default) or `User`, see [Configure an incremental load](https://knowledge.bigenius-x.com/configure-an-incremental-load?hsLang=en). - [Implementation Type](https://knowledge.bigenius-x.com/implementation-type-model-object?hsLang=en): `FactLoadStrategy` only; the Fact Load Strategy decides the loader. - [Foreign Key Mode](https://knowledge.bigenius-x.com/foreign-key-mode-model-object?hsLang=en): foreign key constraints to the referenced Entities; default `Create Disabled`. - [Deduplication Method](https://knowledge.bigenius-x.com/deduplication-method-dataflow?hsLang=en) and [Sort Order within Partition](https://knowledge.bigenius-x.com/sort-order-within-partition-dataflow?hsLang=en), per dataflow. ### Default Terms - `__SK` ([`BG_Fact_ForeignKey_Identity`](https://knowledge.bigenius-x.com/bg_fact_foreignkey_identity?hsLang=en)): one per relationship, the surrogate key the lookup finds. - [`BG_EffectiveTimestamp`](https://knowledge.bigenius-x.com/bg_effectivetimestamp?hsLang=en): the point in time that selects the Entity version; map it to the event time of the source. - [`BG_LoadTimestamp`](https://knowledge.bigenius-x.com/bg_loadtimestamp?hsLang=en): the load that inserted the row. - [`BG_SourceSystem`](https://knowledge.bigenius-x.com/bg_sourcesystem?hsLang=en): the source system of the row; empty unless you map it. - [`BG_IncrementalFilter`](https://knowledge.bigenius-x.com/bg_incrementalfilter?hsLang=en) and [`BG_ID`](https://knowledge.bigenius-x.com/bg_id?hsLang=en) and the threshold terms: only for an incremental load, see [Configure an incremental load](https://knowledge.bigenius-x.com/configure-an-incremental-load?hsLang=en). - [`BG_DataQuality_ID`](https://knowledge.bigenius-x.com/bg-dataquality-id?hsLang=en), [`BG_ErrorCount`](https://knowledge.bigenius-x.com/bg_errorcount?hsLang=en) and [`BG_ErrorDescription`](https://knowledge.bigenius-x.com/bg_errordescription?hsLang=en): hidden terms of the data quality objects. The Fact table stores the surrogate keys, not the business keys of the Entities. It has no primary key. ### Generated objects These are the names on Microsoft SQL Server for a Fact `sales`: | Role | Generated name | Purpose | | --- | --- | --- | | Source | `CLS_F_sales_Source` | Joins and maps the sources | | Deduplication | `CLS_F_sales_Deduplication` | Removes duplicate rows | | IncrementSource | `CLS_F_sales_IncrementSource` | Filters the increment | | Increment | `CLS_F_sales_Increment` | Stores the thresholds | | Lookup | `COR_F_sales_Lookup` | Finds the Entity surrogate keys | | Loader | `COR_F_sales_TruncateInsertLoader` | Loads the table | | Storage | `COR_F_sales` | Fact table | | Result | `COR_F_sales_Result` | Query view | - `CLS_` objects are in the Cleanse layer, `COR_` objects in the Core layer. The loader is a stored procedure; the Fact table and the increment table are tables; all other objects are views. - Only the loader of the selected Fact Load Strategy exists: `_TruncateInsertLoader` or `_InsertLoader`. - The increment table and increment source view exist only with a mapped `BG_IncrementalFilter`. - The deduplication view exists only with a Deduplication Method other than `None`. Data quality rules add the same Cleanse objects as on an [Entity](https://knowledge.bigenius-x.com/entity?hsLang=en). With the default settings, the dependencies are: ``` sources -> CLS_F_sales_Source -> COR_F_sales_Lookup Entity result views -> COR_F_sales_Lookup COR_F_sales_Lookup -> _TruncateInsertLoader -> COR_F_sales -> _Result ``` ### Example The Fact `sales` on Microsoft SQL Server reads the Stage `salestransaction`. It references the Entity `loyaltycard` with the role `loyaltycard`, so its surrogate key term is `loyaltycard_loyaltycard_SK`. The foreign key term `FK_loyaltycard_LOYALTYCARD_ID` is mapped to `LOYALTYCARD_ID`, and `BG_EffectiveTimestamp` to `TRANSACTION_TIME`. The Fact Load Strategy is `Truncate Insert`. The Entity holds two versions of card 500: surrogate key 1 until 2026-10-29 08:00 and surrogate key 3 from then on, as in the example of [Entity](https://knowledge.bigenius-x.com/entity?hsLang=en). Load 2 of the Fact runs on 2026-10-29 10:00, after the Entity load, and reads these transactions: ``` TRANSACTION_ID LOYALTYCARD_ID TRANSACTION_TIME QUANTITY 1 500 2026-10-28 10:00 1 2 999 2026-10-28 10:30 2 3 500 2026-10-29 09:00 3 ``` ### Check the result Query the result view, here in the schema `cor`: ``` SELECT TRANSACTION_ID, loyaltycard_loyaltycard_SK, QUANTITY, BG_LoadTimestamp FROM cor.COR_F_sales_Result ORDER BY TRANSACTION_ID; ``` This is the result after load 2, with shortened column names and timestamps: ``` TRANSACTION_ID SK QUANTITY LoadTs 1 1 1.000 2026-10-29 10:00 2 -1 2.000 2026-10-29 10:00 3 3 3.000 2026-10-29 10:00 ``` - Transaction 1 happened before the new version and keeps surrogate key 1. - Transaction 3 gets surrogate key 3, the version valid at 09:00 on 2026-10-29. - Card 999 does not exist in the Entity, so transaction 2 references the unknown-member row -1. - `Truncate Insert` reloaded all three rows, so every row has the load timestamp of load 2. ### Platform differences - Foreign Key Mode: Microsoft SQL Server offers `None`, `Create Disabled` and `Create Enforced`. Snowflake, Databricks and Fabric Warehouse offer `None` and `Create Disabled`. Fabric Lakehouse offers `None` only. - Snowflake: names are in capitals. The load control task `LC_COR_F__TRUNCATEINSERTTASK` or `_INSERTTASK` calls the loader. The dataflow properties [Use Stream](https://knowledge.bigenius-x.com/use-stream-model-object?hsLang=en) and [Warehouse Placeholder Variable Name](https://knowledge.bigenius-x.com/warehouse-placeholder-variable-name-dataflow?hsLang=en) apply. - Databricks and Fabric Lakehouse: the loader is the notebook `COR_F__TruncateInsertLoader` or `_InsertLoader`. The properties [File Format](https://knowledge.bigenius-x.com/file-format-model-object?hsLang=en), [Optimization Method](https://knowledge.bigenius-x.com/optimization-method-model-object?hsLang=en), [Optimization Method Columns](https://knowledge.bigenius-x.com/optimization-method-columns-model-object?hsLang=en) and [Use Sequential Dataflow Execution](https://knowledge.bigenius-x.com/use-sequential-dataflow-execution-model-object?hsLang=en) apply. - Fabric Warehouse: the deployment notebooks create the same objects as on Microsoft SQL Server. ### Limitations - A Fact has at least one relationship to an Entity. Every foreign key term must be mapped. - Only `Truncate Insert` and `Insert` are supported. Selecting both values for one dataflow produces a warning; the two loaders must then run in separate execution plans, not in the same one. - An aggregation must be defined in all dataflow sets or in none. ### Release differences - Generator 1.10: `BG_DataQuality_ID` replaces `BG_Cleanse_ID`. - Generator 2.2: Increment Management Type is available. Fabric Warehouse is available. ### Related articles - Previous: [Entity](https://knowledge.bigenius-x.com/entity?hsLang=en), [Stage](https://knowledge.bigenius-x.com/stage?hsLang=en) - Next: [Configure an incremental load](https://knowledge.bigenius-x.com/configure-an-incremental-load?hsLang=en) - Reference: [Fact Load Strategy (Dataflow)](https://knowledge.bigenius-x.com/fact-load-strategy-dataflow?hsLang=en), [Implementation Type (Model Object)](https://knowledge.bigenius-x.com/implementation-type-model-object?hsLang=en), [Foreign Key Mode (Model Object)](https://knowledge.bigenius-x.com/foreign-key-mode-model-object?hsLang=en), [Deduplication Method (Dataflow)](https://knowledge.bigenius-x.com/deduplication-method-dataflow?hsLang=en), [Sort Order within Partition (Dataflow)](https://knowledge.bigenius-x.com/sort-order-within-partition-dataflow?hsLang=en), [Custom Singleton Values (Model Object)](https://knowledge.bigenius-x.com/custom-singleton-values-model-object?hsLang=en), [BG\_Fact\_ForeignKey\_Identity](https://knowledge.bigenius-x.com/bg_fact_foreignkey_identity?hsLang=en), [BG\_EffectiveTimestamp](https://knowledge.bigenius-x.com/bg_effectivetimestamp?hsLang=en), [BG\_IncrementalFilter](https://knowledge.bigenius-x.com/bg_incrementalfilter?hsLang=en), [BG\_DataQuality\_ID](https://knowledge.bigenius-x.com/bg-dataquality-id?hsLang=en) - See also: [Missing Foreign Keys (Data Quality Rule)](https://knowledge.bigenius-x.com/missing-foreign-keys-data-quality-rule?hsLang=en), [Effective Timestamp mapped (Business Rule)](https://knowledge.bigenius-x.com/effective-timestamp-mapped-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), [Create a Model Object with the wizard](https://knowledge.bigenius-x.com/create-a-model-object-with-the-wizard?hsLang=en), [Model a Dimensional Solution](https://knowledge.bigenius-x.com/model-a-dimensional-solution?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) - [Oracle](https://knowledge.bigenius-x.com/generators?hsLang=en#oracle) - [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