Skip to main content

How to Compare Transactional Data in Source with Aggregated Data in Target?

In this use case, we will compare transactional data with aggregated data. Our source table is the transactional data table, and the target table is the aggregation performed on that transactional table. Let's create a reconciliation rule for this purpose.

Steps​

Here are the steps outlined in the video:

Create Reconciliation Rule​

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

Set Up Source Data Connection​

  1. Configure a source data connection to the "AdventureWorks" database, using the "dbo" schema and the "AccountTransactions" table. Preview the data, which contains customer-level information with multiple transactions for each customer ID on a given transaction date. The test case is to validate the aggregated transaction amount, which is the sum of the total transaction amount for each day for a customer ID.

Define Source Query​

  1. Write a query to find the total amount for a specific transaction date and customer ID. Group the results by customer ID, customer name, and transaction date, and calculate the sum of the amount. Refresh the data to verify the results.

Set Up Target Data Connection​

  1. Configure a target data connection to the same "AdventureWorks" database, using the "dbo" schema and the "AccountTransactionDaily" table. Observe that the aggregated data is already present in this table, matching the expected aggregated transaction amounts.

Define Diff Join Condition​

  1. Set up a Diff Join condition using "Auto Map by Column Name" since the source and target tables have the same column names. Pin the primary keys, including customer ID and transaction date. Add customer name and aggregated transaction amount as checks.

Review Checks​

  1. In the checks section, review the comparisons between source and target customer names and the aggregated transaction amounts.

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, and 33 mismatches are identified. Investigate further by clicking on the instance ID.

  2. Examine the aggregated transaction amount discrepancies. For example, for customer Josephine White on June 29th, the expected aggregated transaction amount from the source is $26,140, while the value from the target is $17,922.

Video: How to Compare Transactional Data in Source with Aggregated Data in Target?​

Conclusion​

By using iceDQ, we have successfully compared transactional data with aggregated data. This process helps identify discrepancies and ensure data accuracy in the aggregation process.

Last updated on