Foreign Key Compare Test Plan allows users to check for data integrity by checking the validity of the foreign key relationships. User can also enter the Unspecified Identifier to indicate foreign key mismatches between the tables. One of the use cases of Foreign Key Compare test plan is to check Fact-to-Dimension mapping.

Some example validations are:

  • Validate completeness of data in foreign key column
  • Check that the data in the foreign key column is consistent with data in the corresponding primary key column

Foreign Key Compare Test Plan makes use of the table joins defined in the data model for the selected connection. You can also specify allowed variance for foreign key mismatches before executing the test plan.

Foreign Key Compare Test Plan can be scheduled to run repeatedly at specific time periods. User can also specify options such as number of parallel tests that can executed. The Run History details of the Test Plan gets saved whenever the test plan is executed.

Create a Foreign Key Test Plan

There are several methods to launch the Foreign Key Test Plan in ETL Validator.

Depending on your convenience, select any one of the following methods:

  • Method 1
    Mouse hover over the plus icon and select Foreign Key Test Plan.
  • Method 2
  1. On the side menu, click TEST PLANS.
  2. Click Tests +.
  3. Under Data Quality Tests, select Foreign Key Compare Test Plan.
  • Method 3
  1. On the side menu, click HOME.
  2. Under Common Tasks, Click Create Tests.
  3. Under Data Quality Tests, select Foreign Key Compare Test Plan.

Open/Edit a Foreign Key Test Plan

To open/edit a Foreign Key Test Plan,

  1. On the side menu, click TEST PLANS.
  2. In the Test Plans tree, select the Foreign Key Compare folder.
  3. Click on the name of the Foreign Key Test Plan.
Information You can also open a test case from the TEST RUNS view.

Foreign Key Test Plan Details

When you want to create or edit a Foreign Key Test Plan, complete the information in the following pages.


In the Start page, you will enter basic details like name and database.

This page contains the following:


Enter a name for the Foreign Key Test Plan.


Select the database connection from the drop-down list.


Enter a description for the Foreign Key Test Plan.


Enter the identifier to indicate foreign key mismatches between fact and dimensions.


Select this checkbox if there is no identifier.


Create a label for easy identification of test plan. To create a label, click the Click to add label link, enter the string, and press Enter.

Select Joins

Select tables joins for testing. Select the checkboxes for the applicable tables joins.

View Model

Allows you to view data model defined for the database.

Enter Variance

In this page, you enter the percentage of variance allowed for each foreign key mismatch.

To enter variance percentage, in the table join condition row, double-click in the % Variance Allowed column cell, and enter the percentage value (≤ 100).


In the final Run page, you will run the test plan immediately or schedule it to run later.

The Run page contains the following:


  • Parallel Joins. Enter how many joins to consider in parallel  while running the test plan.
  • Callback URL. Select an URL that will call the external process after the test plan is executed. 


Allows you to run the test plan.


Allow you to create a schedule to run the test plan at recurring time intervals.


Allows you to add recipients for sending email notifications about test plan run details.

Run Date

Allows you to select the test plan run history.


Allows you to remove the selected test plan run.

Remove All

Allows you to remove the complete test plan run history.


Allows you to refresh the Run page.


Allows you to export the test plan results to Excel format.

View Report in Browser

Opens the test plan results in ETL Reporting, in a web browser.


© Datagaps. All rights reserved.
Send feedback on this topic to Datagaps Support