Skip to main content

How to Find Common Records between Source and Target tables?

In this use case, we will discuss the validation process to ensure that the source and target tables do not contain any common records. Specifically, we will focus on two tables: the "Permanent Employee" table and the "Temporary Employee" table. The objective is to confirm that there are no overlapping records between these tables, meaning no employees who are both temporary and permanent.

Steps​

Here are the steps outlined in the video:

Create Reconciliation Rule​

  1. Begin by creating a reconciliation rule with an appropriate name for clarity.

Set Up Source Data Connection​

  1. Configure a source data connection to the "Adventure Works" database with the schema "HR" and the table "Permanent Employee." Preview the data to see columns such as first name, middle name, last name, and employee ID.

Set Up Target Data Connection​

  1. Configure a target data connection to the same "Adventure Works" database, schema "HR," and table "Temporary Employee." This table has the same columns as the "Permanent Employee" table.

Define Diff Join Condition​

  1. Set up a Diff Join condition using the employee ID as the join condition. This condition will help identify common records between the two tables.

Define Checks​

  1. Focus on finding common records (the intersection of records) between the two tables, denoted as "A ∩ B." Disable all other checks and solely enable the "Intersection A ∩ B" check.

Publish & Execute​

  1. Publish the rule and execute it.

Review Results​

  1. After execution, review the results. In the example provided, a warning is encountered. The source count, representing the number of records in the "Permanent Employee" table, is 99. In contrast, the target count, representing the number of records in the "Temporary Employee" table, is 201. This indicates that four records are common between the two tables.

  2. Investigate further by clicking on the instance ID. It is revealed that employees with IDs 96, 97, 98, and 99 exist in both the "Permanent Employee" and "Temporary Employee" tables.

Video: How to Find Common Records between Source and Target tables?​

Conclusion​

By using iceDQ, we have successfully validated the scenario of finding common records between two tables. Our expectation was that no common records would be present. This demonstration highlights the effectiveness of iceDQ in maintaining data integrity and uncovering unexpected data overlaps.

Last updated on