Comparing output of predefined query with another

You are viewing archived documentation for Data Trust v2024.06 (previous version).
Go to the latest v7.6 documentation →

Comparing output of snapshot of a predefined query

Let’s compare an output of a predefined query with an RDBMS data table using an FDR (Functional Data Reconciliation).

To learn more, let us walk through the below procedure:

Click the ‘SCENARIO STUDIO’ module.
Hover on the ‘Scenario Builder’ snippet from the left pane options.
Click the ‘Functional Data Recon’ option.

Path 01: QUERY STUDIO > Scenario Builder > Functional Data Recon

The user is navigated to the FDR Session page in create and edit mode.
From the ‘Data Source’ tab, let’s drag and drop ‘Queries’ icon onto the FDR session page in create and edit mode.
Double tap the ‘Queries’ (Read data from <Query>) widget.

A ‘Read data from Query’ pop-up window is displayed.
Select ‘orders1 1211’ Query from the ‘Query Name’ column.

Checkbox ‘orders1 1211’ Query.

Note: A ‘Metadata for orders1 1211’ slide screen is displayed.

Click the ‘Select’ button.

This action closes the ‘Queries’ (Read data from “orders1 1211” for data source “Postgre SQL Northwind-QA” widget.
From the Data Source tab, drag and drop an ‘RDBMS Table’ icon onto the FDR session page.
Double tap the ‘RDBMS Tables’ (Read data from Table) widget.

A ‘Read data from Table’ pop-up window is displayed.
Select the Database Type as ‘Redshift’ from the drop-down list.
Select Connection Name as ‘Redshift-QA’ from the drop-down list.
Click the ‘Navigator’ button.

A ‘Navigator’ slide screen is displayed on the top right side of the page.
Select and click ‘order_details’ from the ‘public’ schema.

This action picks and loads the table (public.order_details) displaying the column names and data types.
Click the ‘Select’ button.

This action closes the ‘RDBMS Tables’ widget by saving its data.
Click the ‘Transformations’ tab.

Drag and drop an ‘Aggregation’ icon onto the FDR session page.
Double tap the ‘Aggregation’ (Aggregate data from orders1 1211) widget.

An ‘Aggregate data from orders1 1211’ pop-up window is displayed.

Note: By default, all the column names are checked in.

Select and checkbox the first two columns namely ‘orderid’ and ‘productid’.
Select the Aggregation type to ‘Count’ for ‘orderid’ and ‘Min’ for ‘productid’.
Click the ‘Save’ button.

This action closes the ‘Aggregation’ (Aggregate data by) widget by saving its data.
From the ‘Transformation’ tab, drag another ‘Aggregation’ icon onto the FDR session page.
Double tap the ‘Aggregation’ (Aggregate data from public.order_details) widget.

An ‘Aggregate data from public.order_details’ pop-up window is displayed.

Note: By default, all the column names are checked in.

Select and checkbox the second and third columns namely ‘productid’ and ‘orderid’.
Select the Aggregation type to ‘Count’ for ‘productid’ and ‘Min’ for ‘orderid’.
Click the ‘Save’ button.

This action closes the ‘Aggregation’ widget by saving its data.
Click the ‘Reconciliation’ tab.

Drag and drop the ‘Map source & target fields’ icon.
Double tap the ‘Map source & target fields’ widget.

A ‘Map source & target fields’ pop-up window is displayed.
Click the ‘Save’ button.

This action pops up a ‘Confirm action’ window.
Click the ‘Verified’ button.

The ‘Map source & target fields’ widget is closed saving its data.
Click the ‘Additional Options’ tab.

Drag and drop an ‘Email’ icon onto the FDR session page.
Click the ‘Email Settings’ widget.

An ‘Email Settings’ pop-up window is displayed.
Click the ‘Save’ button.

Note: Email ids are greyed due to confidential data.

Click the ‘Execute Now’ button.

This action executes the FDR.
A monitor mode is enabled.
The result here is a ‘Failed’ scenario that resulted in an exceptions count.
Click on the ‘Map source & target fields’ count icon (1).

This action navigates the user to the ‘Functional Data Reconciliation Exceptions’ summary page.

The user receives exceptions email details as shown here.