Looking for the previous version? View v2024.06 documentation →
Comparing Output of Custom SQL Query with a Snapshot
Overview
This scenario compares the output of a custom SQL query against a data snapshot. The SQL query result forms the source dataset, and the snapshot provides a point-in-time baseline for comparison to detect data drift.
Prerequisites
- You are logged in to Data Trust with a Pro-User or Admin Pro-User role.
- An RDBMS connection profile is configured and a snapshot of the target data has been taken.
Steps
Path: Data Trust › Scenario Studio › Scenario Builder › Comparing Output of Custom SQL Query with a Snapshot
Navigate to the Scenario Studio module and select the Comparing Output of Custom SQL Query with a Snapshot option from the left sidebar or main menu. This opens the scenario builder canvas where you will configure the data source connections, transformation rules, and execution settings.
Drag the Custom Sql Widget from the widget palette and drop it onto the scenario canvas. Positioning the widget opens its configuration popup where you will set the connection, table, and any additional options.
This step involves clicking the Click on Navigatior Button within the Comparing Output of Custom SQL Query with a Snapshot workflow. Follow the on-screen prompts to complete this step before proceeding to the next configuration stage.
The Navigator Popup dialog exposes all available configuration options for this step. Review each field carefully and fill in the required values before clicking Save or OK to apply the configuration.
The Navigator panel displays the available databases, schemas, and tables for the selected connection. Expand the tree to locate the required table or view, then click Select to add it to the widget configuration.
Click Get Metadata to automatically retrieve the column structure and data types from the connected data source. Review the retrieved metadata and deselect any columns that are not relevant before proceeding to configure validation rules.
This step involves interacting with the Data within the Comparing Output of Custom SQL Query with a Snapshot workflow. Follow the on-screen prompts to complete this step before proceeding to the next configuration stage.
Click the Save button to persist the current widget or scenario configuration. A confirmation indicator appears on the canvas once the save is successful, and the widget thumbnail updates to reflect the configured state.
This step involves interacting with the Draga and drop RDBMS widget within the Comparing Output of Custom SQL Query with a Snapshot workflow. Follow the on-screen prompts to complete this step before proceeding to the next configuration stage.
The Navigator panel displays the available databases, schemas, and tables for the selected connection. Expand the tree to locate the required table or view, then click Select to add it to the widget configuration.
The Navigator panel displays the available databases, schemas, and tables for the selected connection. Expand the tree to locate the required table or view, then click Select to add it to the widget configuration.
The Navigator panel displays the available databases, schemas, and tables for the selected connection. Expand the tree to locate the required table or view, then click Select to add it to the widget configuration.
Drag the Aggregation Widget from the widget palette and drop it onto the scenario canvas. Positioning the widget opens its configuration popup where you will set the connection, table, and any additional options.
Click the Save button to persist the current widget or scenario configuration. A confirmation indicator appears on the canvas once the save is successful, and the widget thumbnail updates to reflect the configured state.
The Aggregation popup dialog exposes all available configuration options for this step. Review each field carefully and fill in the required values before clicking Save or OK to apply the configuration.
This step involves interacting with the Add Aggregation within the Comparing Output of Custom SQL Query with a Snapshot workflow. Follow the on-screen prompts to complete this step before proceeding to the next configuration stage.
Drag the Filter Widget from the widget palette and drop it onto the scenario canvas. Positioning the widget opens its configuration popup where you will set the connection, table, and any additional options.
The Apply Filter panel allows you to define criteria that limit which records are included in the comparison. Filters reduce data volume and processing time by excluding records that are out of scope for this reconciliation or validation run.
The Transformations tab lists all available data transformation widgets including Filter, Derive, Aggregate, and Lookup. Add transformation widgets between data source and reconciliation or validation widgets to cleanse or reshape data before comparison.
Drag the Map And Recon Widget from the widget palette and drop it onto the scenario canvas. Positioning the widget opens its configuration popup where you will set the connection, table, and any additional options.
The Map and recon popup dialog exposes all available configuration options for this step. Review each field carefully and fill in the required values before clicking Save or OK to apply the configuration.
The Confirm Action popup dialog exposes all available configuration options for this step. Review each field carefully and fill in the required values before clicking Save or OK to apply the configuration.
Click the Save button to persist the current widget or scenario configuration. A confirmation indicator appears on the canvas once the save is successful, and the widget thumbnail updates to reflect the configured state.
Drag the Email Widget from the widget palette and drop it onto the scenario canvas. Positioning the widget opens its configuration popup where you will set the connection, table, and any additional options.
The Email widget sends an automated result notification to configured recipients after the scenario finishes executing. Provide the recipient addresses, subject, and choose whether to attach the exception report or include a summary in the email body.
Click the Save button to persist the current widget or scenario configuration. A confirmation indicator appears on the canvas once the save is successful, and the widget thumbnail updates to reflect the configured state.
This step involves interacting with the Execution compleated within the Comparing Output of Custom SQL Query with a Snapshot workflow. Follow the on-screen prompts to complete this step before proceeding to the next configuration stage.
The Result summary page page presents the full outcome of the scenario execution including record counts, match rates, and any detected exceptions. Drill into exception records to inspect individual mismatches and use the Export to CSV option to share results with stakeholders.
The Email widget sends an automated result notification to configured recipients after the scenario finishes executing. Provide the recipient addresses, subject, and choose whether to attach the exception report or include a summary in the email body.