---
title: Reduce the volume of the Data Quality rules log
description: The Missing Foreign Keys data quality rule logs every row of an Entity or Fact whose foreign key is NULL or has no match in the referenced Entity.
---

[Skip to content](https://knowledge.bigenius-x.com/reduce-the-volume-of-the-data-quality-rules-log#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 Data Quality rules log

**Applies to**  
Platform: Microsoft SQL Server · Snowflake · Databricks · Fabric Lakehouse · Fabric Warehouse  
Architecture: Dimensional

The Missing Foreign Keys data quality rule logs every row of an Entity or Fact whose foreign key is NULL or has no match in the referenced Entity. When a NULL foreign key is a valid case in your source, point it to an N/A row of your own in the referenced Entity. These rows then load without log entries, and real missing keys are still logged.

For Generator 1.9 and earlier, see [Reduce the volume of the Cleansing Error tables](https://knowledge.bigenius-x.com/reduce-the-volume-of-the-cleansing-error-tables?hsLang=en). The example uses Microsoft SQL Server.

### How missing foreign keys are logged

The Missing Foreign Keys rule is created by default for each relationship of an Entity or Fact, with logging switched on. For each row with a missing foreign key, it writes a row with the description `Missing foreign key value for relation to "<referenced entity name>"` to the error table of the model object:

- Entity: `CLS_EN_<entity name>_DataQualityError`
- Fact: `CLS_F_<fact name>_DataQualityError`

With the default settings, the row is still loaded, and its foreign key gets the surrogate key `-1` of the unknown-member row. See [Missing Foreign Keys (Data Quality Rule)](https://knowledge.bigenius-x.com/missing-foreign-keys-data-quality-rule?hsLang=en) and [Take care of entries from the Data Quality rules log](https://knowledge.bigenius-x.com/take-care-of-entries-from-the-data-quality-rules-log?hsLang=en).

### Point NULL foreign keys to your own row

1. Choose a surrogate key and a business key for the N/A row that the source never delivers, for example `-10` for both. The unknown-member row already uses the surrogate key `-1` and the business key `0` for numbers or `Unknown` for text.
2. Open **Manage Term Mappings** of the model object that has the relationship. Set the **Expression** of the foreign key term so that NULL becomes the business key of the N/A row, see the [example](https://knowledge.bigenius-x.com/reduce-the-volume-of-the-data-quality-rules-log#example).
3. Generate the artifacts and deploy them.
4. Insert the N/A row into the table of the referenced Entity. Take the column list from the `CREATE TABLE` statement of the Entity in the generated artifacts, and give every `NOT NULL` column a value.
5. Load the project. The rows with a NULL foreign key now get the surrogate key of the N/A row, and the error table has no rows for them.

The N/A row is separate from the unknown-member row (`-1`) that biGENIUS-X generates. Rows whose foreign key has another value without a match are still logged and get `-1`.

The N/A row is not part of the generated artifacts. With the [Deployment Options](https://knowledge.bigenius-x.com/deployment-options-project?hsLang=en) value Generate Drop Table Statements (the default), each deployment recreates the table and removes the row. Without that value, the row stays. Keep the `INSERT` statement as a script and run it after each deployment that recreates the table.

### Example

The Entity `Sales_Transaction` has a relationship to the Entity `Loyalty_Card`. Sales without a loyalty card deliver NULL in `LOYALTYCARD_ID`, in 23 rows of the source. The foreign key term `FK_Loyalty_Card_LOYALTYCARD_ID` of `Sales_Transaction` gets this expression:

```
CASE
    WHEN s1.LOYALTYCARD_ID IS NULL THEN -10
    ELSE s1.LOYALTYCARD_ID
END
```

After the deployment, this script adds the N/A row to `Loyalty_Card`. In the script, `core` stands for the schema of the Core layer. `Loyalty_Card` has SCD1 terms only, so its table has `BG_RowHashScd1`. The table of an Entity with SCD2 terms also has the `NOT NULL` columns `BG_ValidFromTimestamp` and `BG_RowHashScd2`.

```
SET IDENTITY_INSERT [core].[COR_EN_Loyalty_Card] ON;
GO
INSERT INTO [core].[COR_EN_Loyalty_Card] (
     [BG_SourceSystem]
    ,[BG_LoadTimestamp]
    ,[BG_UpdateTimestamp]
    ,[Loyalty_Card_SK]
    ,[BG_RowHashScd1]
    ,[LOYALTYCARD_ID]
    ,[CARD_NUMBER]
    ,[CUSTOMER_ID]
    ,[VALID_FROM]
    ,[VALID_TO]
    ,[IS_ACTIVE]
)
VALUES (
     N'N/A'
    ,CAST(N'19000101' AS DATETIMEOFFSET)
    ,CAST(N'19000101' AS DATETIMEOFFSET)
    ,-10
    ,0x00
    ,-10
    ,0
    ,0
    ,'19000101'
    ,'99991231'
    ,0
);
GO
SET IDENTITY_INSERT [core].[COR_EN_Loyalty_Card] OFF;
GO
```

### Check the result

After the load, the 23 sales without a loyalty card point to the N/A row:

```
SELECT [Loyalty_Card_Loyalty_Card_SK], COUNT(*) AS [rows]
FROM [core].[COR_EN_Sales_Transaction]
WHERE [Loyalty_Card_Loyalty_Card_SK] = -10
GROUP BY [Loyalty_Card_Loyalty_Card_SK];

Loyalty_Card_Loyalty_Card_SK  rows
-10                           23
```

The error table of the Entity has no rows. In the query, `cleanse` stands for the schema of the Cleanse layer:

```
SELECT COUNT(*) AS [rows]
FROM [cleanse].[CLS_EN_Sales_Transaction_DataQualityError];

rows
0
```

Without the expression, it holds the 23 rows with the description `Missing foreign key value for relation to "Loyalty_Card"`, and the 23 sales get the surrogate key `-1`.

### Related articles

- Reference: [Missing Foreign Keys (Data Quality Rule)](https://knowledge.bigenius-x.com/missing-foreign-keys-data-quality-rule?hsLang=en), [Deployment Options (Project)](https://knowledge.bigenius-x.com/deployment-options-project?hsLang=en), [Custom Singleton Values (Model Object)](https://knowledge.bigenius-x.com/custom-singleton-values-model-object?hsLang=en)
- See also: [Take care of entries from the Data Quality rules log](https://knowledge.bigenius-x.com/take-care-of-entries-from-the-data-quality-rules-log?hsLang=en), [Handle missing Foreign Key in a Relationship](https://knowledge.bigenius-x.com/handle-missing-foreign-key-in-a-relationship?hsLang=en), [Reduce the volume of the Cleansing Error tables](https://knowledge.bigenius-x.com/reduce-the-volume-of-the-cleansing-error-tables?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)

    - [Databricks](https://knowledge.bigenius-x.com/generators?hsLang=en#databricks)
    - [Databricks dbt](https://knowledge.bigenius-x.com/generators?hsLang=en#databricks-dbt)
    - [Fabric Lakehouse](https://knowledge.bigenius-x.com/generators?hsLang=en#fabric-lakehouse)
    - [Fabric Warehouse](https://knowledge.bigenius-x.com/generators?hsLang=en#fabric-warehouse)
    - [Microsoft SQL Server](https://knowledge.bigenius-x.com/generators?hsLang=en#microsoft-sql-server)
    - [Oracle](https://knowledge.bigenius-x.com/generators?hsLang=en#oracle)
    - [Snowflake](https://knowledge.bigenius-x.com/generators?hsLang=en#snowflake)
    - [Snowflake dbt](https://knowledge.bigenius-x.com/generators?hsLang=en#snowflake-dbt)
    - [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 Azure DevOps pipeline](https://knowledge.bigenius-x.com/generators?hsLang=en#deployment-with-an-azure-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)

<https://www.linkedin.com/company/bigenius> <https://www.youtube.com/@biGENIUS_DWA>

All rights reserved © 2025, biGENIUS AG