--- title: Reduce the volume of the Cleansing Error tables description: By default, the cleansing action logs a line of type Missing foreign key value in the CLS_XXX_Error tables if a Foreign Key has a NULL value in a relationship between a Fact and an Entity or between 2 --- [Skip to content](https://knowledge.bigenius-x.com/reduce-the-volume-of-the-cleansing-error-tables#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) # Reduce the volume of the Cleansing Error tables This article is valid for a Project with a **Generator Configuration version lower than or equal to 1.9.X**. For higher versions, see Reduce the volume of the Data Quality rules log. ### Reduce the volume of Cleansing Error tables By default, the cleansing action logs a line of type **Missing foreign key value** in the ***CLS\_XXX\_Error*** tables if a Foreign Key has a NULL value in a relationship between a Fact and an Entity or between 2 Entities. *Example*: We don't know who buys the Product X: the Person is set to NULL in the Fact Sale. You have the same kind of line if a Foreign Key has a value that does not exist in the target object of the relationship. *Example*: Product X was bought by Person Y, but Person Y didn't exist in the list of persons at that time. For more information about the cleansing actions, see the article [Take care of entries from Cleansing Error tables](https://knowledge.bigenius-x.com/take-care-of-entries-from-cleansing-error-tables?hsLang=en). By default, the cleansing action logs an error if a Foreign Key has a NULL value in a relationship between a Fact and an Entity or between two Entities. However, you might want to treat a NULL Foreign Key as meaningful, indicating that the related record is unknown, but the data is still correct in the source. To reduce the volume of entries in the *CLS\_XXX\_Error* tables, you may choose to suppress the creation of these Cleansing Action Log lines for such cases. To avoid logging these as errors in the Cleansing Action error tables, apply the following updates to your Data Solution: - Include a new entry in the relevant Entity to represent the "N/A" value. This is called a singleton. For example, in the case of the *Product\_Subcategory* Entity, you can add the following line: ``` -- Enable IDENTITY_INSERT in Product_Subcategory EntitySET IDENTITY_INSERT [DW].[COR_EN_Product_Subcategory] ONGO--Add the singleton value in Product_Subcategory EntityINSERT INTO [DW].[COR_EN_Product_Subcategory] ([BG_SourceSystem]      ,[BG_LoadTimestamp]      ,[BG_UpdateTimestamp]      ,[Product_Subcategory_ID]      ,[ProductSubcategoryID]      ,[FK_Product_Category_ProductCategoryID]      ,[Product_Category_Product_Category_ID]      ,[Name])VALUES (      'N/A'      ,CONVERT(DATETIME, '19000101', 112)      ,CONVERT(DATETIME, '19000101', 112)      ,-10      ,-10      ,0      ,0      ,'Not available');GO-- Disable IDENTITY_INSERT in Product_Subcategory EntitySET IDENTITY_INSERT [DW].[COR_EN_Product_Subcategory] OFFGO ``` - To implement the use of the N/A value, add a term rule to the Foreign Key in the related Entity. For instance, in the case of *Product\_Product*, the term rule should ensure that NULL values are replaced with the "N/A" value. - Edit the *FK\_Product\_Subcategory\_ProductSubcategoryID* Term Mapping - Fill in the following Expression: ``` CASE WHEN s1.ProductSubcategoryID IS NULL THEN -10 --apply the N/A singleton value ELSE s1.ProductSubcategoryIDEND ```   Once you generate, deploy, and load your project again, no more Cleansing Action Log entries will be created for a NULL Foreign Key in the relationship affected by this update (e.g., the relationship between a Product and a Product\_Subcategory). This update allows you to distinguish between **"real" unknown values** (which are subject to a Cleansing Action) and **N/A values**, which are now treated as valid and meaningful. The **new singleton line** added to the relevant Entity for the N/A value is not generated by biGENIUS-X since it was **created** **manually**. **As a result, a new deployment will remove this line**. To ensure the N/A value persists, it is recommended to **create a script** that can be **executed manually** after deploying the generated artifacts. This script should reinsert the N/A entry into the Entity table.   - [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