Comparing output of Custom SQL query with a snapshot

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

Comparing the output of Custom SQL query with a snapshot

Let’s compare an output of a Custom SQL 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 a ‘Custom SQL’ icon onto the FDR session page in create and edit mode.
• Double tap the ‘Custom SQL’ (Read data from custom SQL query) widget.

• A ‘Read data from custom SQL query’ pop-up window is displayed.
• Let’s, select the Database Type as ‘PostgreSQL’ from the drop-down list.
• Select Connection Name as ‘PostgreSQL Northwind-QA’ from the drop-down list.
• Click the ‘Navigator’ button.

• A ‘Navigator’ slide screen is displayed.
• Click the ‘Order_details’ from the ‘Public’ schema.

• The SQL query of ‘order_details’ table is added.
• Click the ‘Infer Metadata’ button.

• This action navigates the user to the ‘Metadata’ tab displaying all the column names and data types.
• Click the ‘Data’ tab.

• This action displays the data within the columns.
• Click the ‘Save’ button.

• This action closes the ‘Custom SQL’ widget by saving its data.
• The user is navigated to the FDR session page.

• From the ‘Data Source’ tab, drag another ‘RDBMS Tables’ icon onto the FDR session page.
• Click 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.
• 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 Postgre SQL Northwind-QA) widget.

• An ‘Aggregate data from PostgreSQL Northwind-QA’ 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’ widget by saving its data.
• From the ‘Transformation’ tab, drag a ‘Filters’ icon onto the FDR session page.
• Double tap the ‘Filters’ (Filter data from public.order_details) widget.

• A ‘Filter data from public.order_details’ pop-up window is displayed.
• Drag the Available Fields, Available Operators, and input.
• Select ‘orderid’ drop-down list for ‘Available Fields’, ‘Equals to’ for ‘Available Operators, and ‘input’ as ‘10248’.
• The ‘Expression’ is updated accordingly.
• Click the ‘Save’ button.

• The ‘Filters’ widget is closed 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 (78).

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

• The user receives exceptions email details as shown here.