Tutorial

A step-by-step walkthrough of QuerySurge™

Built by

© 2022 Real-Time Technology Solutions, Inc. All Rights Reserved.

22 West 38th Street, 11th Floor • New York, NY 10018
info@[Link] • [Link] • (212) 240-9050
Welcome to QuerySurge

QuerySurge is the smart Data Testing solution that automates the data validation and ETL testing of Big Data,
Data Warehouses, Business Intelligence Reports and Enterprise Applications with full DevOps functionality for
continuous testing. By analyzing and pinpointing any differences in the data, QuerySurge ensures that the data
extracted from source systems remains intact in the target and complies with transformation requirements.
QuerySurge is an essential asset to every data testing process.

Challenges QuerySurge Can Solve

• Your need for data quality at speed. Validate up to 100% of all data up to 1,000 x faster than traditional
testing. more »

• Your test automation issues. Now you can automate all your data testing, from the kickoff of tests to performing
the validation to automated emailing of the results and updating your test management system. more »

• Your ability to test across platforms, whether you use a Big Data Lake, Data Warehouse, traditional database,
NoSQL document store, BI reports, flat files, JSON files, SOAP or restful web services, xml, mainframe files, or
any other data store. more »

• Your need to integrate for Continuous Delivery. QuerySurge integrates with most Data Integration/ETL solutions,
Build/Configuration solutions, and QA/ Test Management solutions. more »

• Your ability to analyze your data, with our Data Analytics Dashboard & Data Intelligence Reports. more »

Additional Features & Benefits

• Integration with leading Test Management Solutions. QuerySurge offers integration with Micro Focus ALM,
IBM RQM, and Microsoft DevOps (formerly TFS & VSTS). Now you can store all of your data tests and receive all
of your results in your favorite test management solution. more »

• Query Wizards — fast and easy with no SQL coding needed. The smart Wizards provide both novices and
experienced team members with the ability to easily and quickly validate their data with no SQL coding required.
more »

• DevOps and Continuous Testing. Dynamically generate, execute, and update tests and data stores utilizing API
calls. more »

• Proven ROI. Making a business case for QuerySurge is easy. QuerySurge returns 1,287%. more »

© 20222Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
Resources

Knowledge Base and Community Forums provide a hub for extensive QuerySurge information and the
answers to the most frequently asked questions about QuerySurge. To access the QuerySurge Knowledge
Base, click here >>

• Video Library: The Video & Slide Library provides tutorials, demos and webinars on QuerySurge. To
access the Video Library, click here: [Link]

• Contact Us: The Contact Us page provides a form where you can submit comments, questions, or
suggestions. [Link]

Direct Link to Knowledge Base is provided within Querysurge. Select the help icon located on the bottom
panel.

Note: This tutorial requires the installation of ‘Tutorial + Sample Data’ in the QuerySurge installer. This component can
be selected during the initial QuerySurge installation. If you are using the Trial in the Cloud, the Sample Data is already
installed. If you are using the Trial Download, please select this option during installation.

Installation Requirements
QuerySurge is an enterprise data testing solution that will typically be used to validate large data sets. Since you are
testing either a Big Data store or Data Warehouse with potentially massive quantities of data, QuerySurge needs the
right hardware to perform at speeds that will meet your team's needs.

Therefore, the more CPU, RAM, and hard drive space you have, the better and faster QuerySurge performs.

For a trial setup, QuerySurge can be run on a desktop with modest resources, but that will limit QuerySurge's
performance and ability to scale. For additional information on system requirements, click here: QuerySurge System
Requirements.

© 20223Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
 Table of Contents

Welcome to QuerySurge ........................................................................................................................................................ 2

Resources........................................................................................................................................................................ 3

Tutorial.................................................................................................................................................................................... 6
The Story ........................................................................................................................................................................ 6
Overview ......................................................................................................................................................................... 8
First and Subsequent Logins to QuerySurge ................................................................................................................. 9
Create QueryPairs using the Query Wizard for Column-Level Comparison .................................................................. 12
Execute a QueryPair that fails ...................................................................................................................................... 27
Create and Execute a Test Suite ................................................................................................................................... 30
Running a Detailed Scenario Report ............................................................................................................................ 36
Create a QueryPair ........................................................................................................................................................ 38
Create a QueryPair using a Reusable Snippet............................................................................................................... 44
Add a Reusable Query Snippet to a QueryPair ............................................................................................................. 49
Create a Test Suite and Schedule an Execution Scenario ............................................................................................ 52
Create QueryPairs using the Query Wizard for Table-Level Comparison...................................................................... 59
Review of Data Health Dashboard ............................................................................................................................... 70
Summary ...................................................................................................................................................................... 73
Deleting the Tutorial Data............................................................................................................................................ 74

Appendix ............................................................................................................................................................................... 75
Documentation................................................................................................................................................................. 75
Data Warehouse Testing .................................................................................................................................................. 75
The QuerySurge Testing Process ..................................................................................................................................... 77
About the QuerySurge Architecture ................................................................................................................................ 79

About RTTS (developers of QuerySurge) ............................................................................................................................ 82

About QuerySurgeTM ............................................................................................................................................................ 82

© 20224Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
Important Terms

QueryPair
A pair of SQL queries where one query retrieves data from a source file or database and
another SQL query retrieves data from a target database, data warehouse or data mart.

Agent
Performs the query tasks. Agents execute queries against source and target data stores and return the
results to the QuerySurge database.

Design-Time Run
Allows you to execute a QueryPair test to make sure that it behaves as you expect. This is not intended for
actual test execution purposes.

Query Snippet
Reusable piece of SQL code that can be embedded in one or more queries. The purpose of a Snippet is to
minimize the number of places you need to make changes on the same code in different queries.

Test Suite
A collection of QueryPairs used for execution purposes. This feature allows you to group QueryPairs for
execution that is independent of your ‘Design Library’ organization

Scenarios
A collection of Suites that are scheduled for execution.

Query Wizard
A tool that allows you to generate QueryPairs automatically, requiring no manual SQL coding. The Query
Wizard generates tests that can cover about 80% of all data in a data warehouse automatically. 1

Widgets
Project Widgets give you a real-time view into your project progress at all levels, from QueryPair
development to execution and results.

Command Line Integration

Provides the ability to schedule Test Suites to run using Windows Task Scheduler or integrate with both Data
Integration (ETL) and Continuous Build systems.

1
A recent poll conducted by RTTS on targeted LinkedIn groups found that 80% of columns in data warehouse tables have no transformations.

© 20225Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
 QuerySurge
Tutorial

© 20226Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
Consider This…

ZCity is a popular electronics corporation that is in the market for a new data warehouse so that corporate business
personnel can assess the activities that are occurring throughout their sales regions. ZCity recently merged with one of
its competitors, XMart, and now has two sales teams. The sales system environment for ZCity and XMART both reside
on a MySQL database.

The business has decided that a data warehouse that runs nightly incremental ETL loads will help them make more
effective business decisions. The analysts created a mapping document that maps out each source field to its
corresponding target field from each source system. This includes any data transformations that the business deems
necessary to be entered into the warehouse. The corporate IT team has completed development of a MySQL data
warehouse (the target database) and an ETL process that includes transformation logic (from the mapping document) to
load the source data from each source into the data warehouse.

The business’ Quality Assurance team has been tasked with verifying the transformation logic and has purchased
QuerySurge to validate 100% of the data being transformed from source to target. The Quality Assurance Manager has
planned for the Quality Assurance team to review the mapping documents and build test cases, from the same logic that
the developers used when creating the ETL code. The QuerySurge term for test cases is “QueryPairs”. The team is
confident that this approach will not only cover testing 100% of the data but will also prove the mapping documents are
comprehensive.

Their idea is that if two separate teams, the Development Team and the Quality Assurance Team, are building queries
and logic from the same mapping documents separately and can attain the same results, then there is a very good
chance there will be no defects in the production environment.

That’s the plan; Now, it’s time to get testing!

© 20227Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
Overview

You will perform the following tasks in the tutorial. It is recommended that you use two monitors. It also might be easier
to print the tutorial.

➢ Generate QueryPairs using the “Query Wizards”

➢ Review existing and create new QueryPairs
➢ Review existing and create new Reusable Query Snippets
➢ Execute “Design-Time Runs”
➢ Investigate “Design-Time Run” failures
➢ Schedule and execute a Scenario
➢ Generate and review reports
➢ Review Widgets

The tutorial is comprised of the following assets:

1. ZCITY Source
• MySQL sales database (3 tables)
• 27 QueryPairs
• 1 Test Suite including all ZCITY QueryPairs
• 1 Reusable Query Snippet

2. XMART Source
• MySQL sales database (4 tables)
• 31 QueryPairs

3. Data Warehouse Target

• MySQL data warehouse (Dimensional Model)
▪ 5 dimension tables
▪ 2 fact tables

4. Mapping and Model Document (printing and reviewing this document is recommended)

The document is linked within the tool from the setup wizard and can also be downloaded from here: Tutorial
Models and Mapping Document.
• ZCITY mappings and data model
• XMART mapping and file data model
• Data warehouse data model

© 20228Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
First and Subsequent Logins to QuerySurge

Upon QuerySurge installation, a project administrative user has been created for you, or an Administrator has already
provided you with your own login credentials.

Note: If you have already logged into QuerySurge, this section will provide a review

1. On the installation machine, click Start.

2. Under the Start menu select All Programs > QuerySurge > Launch QuerySurge.
3. QuerySurge will launch in your default browser.

4. If your QuerySurge install is local, enter your credentials or use the credentials admin/admin. If you are using a
Cloud trial, use the credentials clouduser/clouduser. Click Login.

© 20229Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
5. If this is your first time launching QuerySurge, a splash screen will display. If this is not the first time launching
QuerySurge, the “Setup Wizard” is always available to you on the top toolbar.

6. Click “Setup Wizard” on the top toolbar if not already displayed.

© 202210Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
 Points of interest:
• Setup Wizard panel on the right
o The panel is divided into 3 sections:
▪ Configuring an Agent
▪ Creating a Connection
▪ Optional Tasks
o The panel shows that an Agent has been installed and enabled (The green circle with the check
mark next to task ‘Install an Agent’ and ‘Enable your Agent in QuerySurge’ signifies a completed

task)
o The ‘Create a Connection’ link brings you to the connection wizard
o The ‘Optional Tasks’ section allows you to easily navigate to documentation and videos or to
create additional connections
• Data Analytics Dashboard panel in the middle
o The center panel displays QuerySurge’s Data Analytics Dashboard and Data Widgets, which is a
configurable dashboard that provides a view into project information (more on this later)

7. Collapse the ‘Setup Wizard’ by clicking the button located at the top right corner of the Querysurge window.

© 202211Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
Create QueryPairs Using the Query Wizard for Column-Level Comparison

A QueryPair is what QuerySurge refers to as a test case – two queries with the following characteristics:
• A SQL query that retrieves data from a source file or database and
• Another SQL query that retrieves data from a target big data store, data warehouse, data mart or database

The Query Wizard is a feature that allows you to generate QueryPairs automatically requiring no SQL coding. It is a fast
and easy way to create QueryPairs for both skilled team members and beginners with less experience who want to
increase testing speed.

Based on a LinkedIn poll of Data Warehouse experts, on average 80% of columns in a typical data warehouse have no
transformations at all. The Query Wizards were developed to generate tests that can cover approximately 80% of all
data in a data warehouse quickly and without writing SQL code. The Wizards are user friendly and provide amazing
results for both beginners and experienced testers.

The Query Wizard can generate QueryPairs for:

• Column-Level Comparison: this is great for Big Data stores and Data Warehouses where tables will have some
columns with transformations (20% on average) and some columns with no transformations (80% on average).
The Column-Level Comparison is for the specific columns with no transformation. Columns with transformation
will require SQL coding.

• Table-Level Comparison: this is great for data migrations and database upgrades with no transformation at all.
Many tables can be compared simultaneously and quickly.

• Row Count Comparison: this works with all - Big Data stores, Data Warehouses, Data Migrations and Database
Upgrades. Many tables can be compared simultaneously and quickly.

A folder can be created that contains all the QueryPairs generated. These QueryPairs can also be added to a new Test
Suite so they can be easily run together (more on this later).

Note: This part of the tutorial illustrates the use of the Query Wizard for creating a QueryPair with Column-Level
Comparisons
1. Click the dropdown menu on the “Design Menu” icon in the top toolbar and select “Launch Query Wizard”

2. Click the Next button.

3. Select “XMART_SOURCE” from the “Source Connection” drop down menu
4. Select “DW_TARGET” from the “Target Connection” drop down menu.

© 202212Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
 Points of interest:

• Metadata (or data about our data) pre-populates for the selected databases

5. Click the Next button.

6. Keep the default “Column-Level Comparison” selected on the “Comparison Type” dialog window.

© 202213Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
 Points of interest:

• Column-Level Comparison validates data points in the selected columns and allows for the addition
of Filtering (WHERE Clause) and Sorting (ORDER BY)
• Table-Level Comparison validates each data point in the source table to its corresponding data point
in the target table
• Row Count Comparison validates the number of rows in each of the selected tables

7. Click the Next button

Points of interest:

• Schemas available for selection are retrieved from the source and target databases that you
previously selected
• One, many, or all schemas can be selected
8. Select ‘xmart’ on the “Source Schemas” pane and ‘dw’ on the “Target Schemas” pane and click Next
9. Select and drag ‘xmart_customer’ under “Source Tables” and drop it into it’s corresponding “Target Tables”
‘dw.customer_dim’

© 202214Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
 Points of interest:

• As a source table is dropped onto its corresponding target table, its ‘Query Mapping’ appears in the
left-hand column (if desired, you can double click to rename it)
• Mapped tables show a line connecting the source and target table with the selected ‘Query
Mappings’ line highlighted green
• Auto generated SQL appears in the ‘Source Query’ and ‘Target Query’ panes at the bottom of the
Query Wizard window

10. Select and drag ‘CUSTOMER ID’ under “Source Columns” and drop it into its corresponding “Target Columns”
‘SOURCE_ID’.
11. Select and drag “FIRST” under “Source Columns” and drop it onto “FIRST” in its corresponding “Target
Columns”.
12. Select and drag “LAST” under “Source Columns” and drop it onto “LAST” in the corresponding ”Target
Columns”.
13. Select and drag “EMAIL” under “Source Columns” and drop it into “EMAIL” in its corresponding ”Target
Columns”.

© 202215Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
14. Select and drag “PHONE NUMBER” under “Source Columns” and drop it into “PHONE” in its corresponding
“Target Columns”.

Points of interest:

• As a source column is dropped onto its corresponding target column the auto-generated SQL will
change to reflect your selections
• Column relationships show lines connecting the source and target columns with the currently
selected relationships highlighted in green

• Relationships can be removed by clicking the ‘Remove Relationship’ icon

15. In the lower middle of the screen under “Filtering (WHERE Clause) and Sorting (ORDER BY)” click the “Add New
Criteria” icon
16. Under “Table” in the newly created criteria field click the dropdown menu and select ‘Source’.
17. Under “Column” click the dropdown menu and select ‘CUSTOMER ID’.
18. Under “Operation” click the dropdown menu and select ‘Sort Direction’.
19. Under “Value” click the dropdown menu and select ‘Ascending’.
20. Click the “Add New Criteria” icon.
© 202216Real-Time Technology Solutions, Inc.
22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
21. Under “Table” click the dropdown menu and select ‘Target’.
22. Under “Column” click the dropdown menu and select ‘SOURCE_SYSTEM’
23. Under “Operation” click the dropdown menu and select ‘Equals’.
24. Under “Value” type ‘XMART’.
25. Click the “Add New Criteria” icon .
26. Under “Table” click the dropdown menu and select ‘Target’.
27. Under “Column” click the dropdown menu and select ‘SOURCE_ID’.
28. Under “Operation” click the dropdown menu and select ‘Sort Direction’.
29. Under “Value” click the dropdown menu and select ‘Ascending’.

Points of interest:

• Added criteria appear in the ‘Filtering (WHERE Clause) and Sorting (ORDER BY)’ section
• Auto generated SQL based on added filter criteria appear in the query panes
• Criteria can be removed by clicking the ‘Remove Criteria’ icon

© 202217Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
30. Click the Next button.
31. Under “Design Library” select “Create New Folder” if not already selected.
32. Under “Parent Folder” click the dropdown menu and select ‘QueryPairs’.
33. Type ‘WIZARD – COLUMN-LEVEL’ in the “Folder Name” field.
34. Under “Scheduling” select “Yes”.
35. Type ‘WIZARD – COLUMN-LEVEL’ in the “Suite Name” field.

Points of interest:

• The Query Wizard will automatically create the following test assets:
o A new folder in the ‘Design Library’ with the name ‘WIZARD – COLUMN-LEVEL’
o All QueryPair(s) for testing the tables selected
o A new Test Suite in the ‘Scheduling’ module with the generated QueryPair(s)
36. Click the Next button.

© 202218Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
 Points of interest:

• A review screen appears with the details for your Query Wizard execution

37. Click the Create button.

© 202219Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
 Points of interest:

• A folder named ‘WIZARD – COLUMN-LEVEL’ was created in the ‘Design Library’ module
• A Test Suite named ‘WIZARD – COLUMN-LEVEL’ was created in the ‘Scheduling’ module
• One QueryPair was created
38. Click the OK button.

© 202220Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
Execute a QueryPair that passes

The QueryPair created in the previous chapter will be utilized for this section of the tutorial

1. In the “Design Library” panel on the left, select “QueryPairs”.

Points of interest:
• QuerySurge utilizes a folder structure similar to Microsoft Windows Explorer
• Multiple sub-folders can be contained within folders
• The left panel allows for simple navigation
• The tutorial has two main folders, one for each source system, and the folder you created while running
the Query Wizard

2. Double click the ‘WIZARD - COLUMN-LEVEL’ folder in the center panel.

© 202221Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
 Points of interest:

• The QueryPair contained within the ‘WIZARD - COLUMN-LEVEL’ folder is displayed on the center panel
• In this case, there is only one QueryPair that was created during the previous chapter

3. Double click the ‘Data (Column): xmart.xmart_customer - dw.customer_dim’ QueryPair

© 202222Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
Points of interest:

• The QueryPair was created to validate that the source customer data was loaded into the data
warehouse. (See mapping 1.01 – 1.05 of the tutorial mapping document - a link to the document is
located on the ‘Setup Wizard’)
o This QueryPair is validating the direct map (no logical transformation) of the data from the
Source, XMART, to the target data warehouse
• The ‘QueryPair’ tab contains
o ‘Source Query’ and ‘Connection’ – for storing the query with a specific database connection
o ‘Target Query’ and ‘Connection’ – for storing the query with a specific database connection

o ‘Save’ icons – for saving the source or target query

• The ‘Properties’ tab contains
o QueryPair name, description, and mapping fields
o Data Type Checking – Ability to broadly check data types (e.g., text vs numeric) or convert all
types to varchar (String) before comparison
o Row Count Options – to set reporting for row count differences

© 202223Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
 o Shared Key Column Definition – to set a column from either the source or target query as key
o Column Threshold Options – Ability to allow fields to pass if within a user-defined threshold
o Ignorable Columns – Ability to ignore columns during analysis
o Duplicate Row Options – to set whether QuerySurge uses a comparison algorithm to manage
duplicate rows in your data
• The ‘Design-Time Run’ tab contains
o Detailed results for each QueryPair. This tab allows you to execute a QueryPair test run to make
sure that it behaves as you expect
• The ‘History’ tab shows the history of changes to the QueryPair
• The ‘DTR History’ tab shows pass/fail information from previous ‘Design-Time Runs’ (currently empty as
no runs have been executed yet)

4. Click the “Design-Time Run” tab.

Points of interest:

• The first time clicking on the ’Design-Time Run’ for a QueryPair will indicate that a run has not been
previously executed
• A specific Agent, if desired, can be selected in the drop-down menu (not required)

5. Click the “Run” button to execute the QueryPair.

© 202224Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
 Points of interest:

• When executing a ‘Design-Time Run’, QuerySurge goes through the following phases:
o Loading – QuerySurge is loading the QueryPair on the Agent for execution
o Running – The Agent is running the target and source queries
o Analyzing – QuerySurge is comparing the results
• The QueryPair passes
• Both source and target queries returned 1250 rows, and all rows matched from source to target
• QuerySurge displays the following information about the last ‘Design-Time Run’
o Source Rows – number of source rows returned
o Target Rows – number of target rows returned
o Row Count Difference – the difference between the number of source and target rows
returned
o Failed Row Count – number of rows with data differences
o Non-Matching Source Rows – number of source rows not found in the target (based on key
column(s))
o Non-Matching Target Rows – number of target rows not found in the source (based on key
column(s))
• ‘Connections’ and ‘Query Performance’ metrics

6. Click “View Query Results”

© 202225Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
 Points of interest:

• The “QueryPair Results” window contains six tabs

o Source Tab – displays the source result rows
o Target Tab – displays the target result rows
o Failures Tab – displays data failures (not populated in this example as no rows failed)
o Source Query Tab – displays the source query
o Target Query Tab – displays the target query
o Query Performance – displays metrics
• Since this is a passing QueryPair, the source and target tabs display the same data (note: the column
headers have been aliased)

7. Close the query results window.

For more information on QueryPairs, please visit Design Library→Working with QueryPairs in the
Knowledge Base

© 202226Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
Execute a QueryPair That Fails

Use QuerySurge to execute a QueryPair that fails.

1. In the “Design Library” tree (left panel), navigate to the folders ‘QueryPairs -> ZCITY -> ORDERS_FACT’

Points of interest:

• The QueryPairs contained within ‘ORDERS_FACT’ folder is displayed

2. Double click the ‘ZCITY – STATUS’ QueryPair.

© 202227Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
 Points of interest:

• The ‘ZCITY – STATUS’ QueryPair was created to validate the source SALE_STATUS column was loaded
into the data warehouse correctly (See tutorial mapping document on the ‘Setup Wizard’).
o This QueryPair is validating that the SALE_STATUS from source ZCITY and target data warehouse are
loaded with the following logic:
▪ Populate target ORDERS_FACT.STATUS using the following logic: if the SALE_STATUS value in the
Sale table is ‘Incomplete’, then change to ‘Pending’. Otherwise, leave as is.

3. Click the “Design-Time Run” tab.

4. Click the Run button to execute the QueryPair.

Points of interest:

• The ‘ZCITY – STATUS’ QueryPair fails. (If one or more fields do not match, then QuerySurge fails the
QueryPair)
• Both source and target returned 6,919 rows
• 1,373 rows failed

5. Click the “View Query Results” button.

6. Click the “Failures” tab.

© 202228Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
 Points of interest:

• The rows with left-pointing green arrows are source rows

• The rows with right-pointing blue arrows are target rows

• Notice the source rows contain the value ‘Pending’ and the target rows contain the value
‘Incomplete’

• All failures were ‘Data Failures’, as indicated by a red flag

• If there were any ‘Non-matching Rows’, they would appear with a yellow flag
• Failures are shown with a red background

7. Close the ‘ZCITY – STATUS’ query results window.

© 202229Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
Create and Execute a Test Suite

The Test Suites feature of QuerySurge lets you organize your QueryPairs in Test Suites - collections of QueryPairs for
execution. This feature allows you a different level of organization for execution purposes that is independent of your
‘Design Library’ organization.

1. Click the “Scheduling” icon located on the bottom panel.

Points of interest:

• A Test Suite has been created for you called ‘ZCITY – ALL’
o ‘ZCITY – ALL’ contains all the QueryPairs associate with the ZCITY source
• The ‘WIZARD – COLUMN-LEVEL’ suite was created in a previous section of this tutorial
• Left Panel – displays all the available Test Suites that can be run
• Middle Panel – displays tests associated with a Test Suite (currently no Test Suite is selected)
• Right Panel – displays your ‘QueryPair Library’ tree

2. Click “Create Suite” on the left panel.

© 202230Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
 Points of interest:

• The “Create New Test Suite” dialog contains:

o Test Suite Name – a name for your suite
o Description – a description for your suite

3. Enter ‘ZCITY – CUSTOMER_DIM’ in the “Test Suite Name” field

4. Enter ‘To test the customer dimension fields’ in the “Description” field
5. Click the Save button.

© 202231Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
 Points of interest:

• Your new Test Suite is currently empty

6. Click “+” next to ‘QueryPairs’ in ‘QueryPair Library’ panel on the right and double click ‘ZCITY’.
7. Drag and drop the ‘CUSTOMER_DIM’ folder to the ‘ZCITY – CUSTOMER_DIM’ test suite panel in the middle.

Points of interest:

• The ‘ZCITY - CUSTOMER_DIM’ Test Suite now contains all QueryPairs from the ‘CUSTOMER_DIM’
folder

8. Click the Save button in the upper left corner of the middle panel to save your Test Suite.
9. Right click on the “ZCITY – CUSTOMER_DIM” on the Test Suite panel (Left panel) and select “Run Now”.
10. Accept all defaults and click the OK button on the “Run Scenario” dialog.
11. QuerySurge will automatically toggle you over to the “Run Dashboard” and the Test Suite will execute.

© 202232Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
 Points of interest:

• Left Panel – ‘Scenario Queue’ contains all Scenarios, including those previously executed
o The executed Scenario was created for you with a single test suite named ‘ZCITY –
CUSTOMER_DIM’
o Notice QuerySurge appended a date/time stamp to the end of the Scenario name
• Upper Middle Panel – the currently selected Scenario’s Test Suites and their progress
• Upper Right Panel – additional information and metrics
• Lower Middle/Right Panel – graphs of progress

12. Double click the Test Suite ‘ZCITY – CUSTOMER_DIM’ to see detailed data about the Test Suite execution results
displayed in the middle panel.

© 202233Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
13. Click on the “ZCITY – EMAIL” QueryPair

© 202234Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
 Points of interest:

• Notice the lower panel has a layout similar to the “Design-Time Run” execution results
• Lower panel contains
o Source Tab – displays the source result rows
o Target Tab – displays the target result rows
o Failures Tab – displays data failures (not populated in this example as no rows failed)
o Source Query Tab – displays the source query
o Target Query Tab – displays the target query
o Query Performance – displays metrics
• Right panel contains
o Source Row Count – displays number of source rows
o Target Row Count – displays number of target rows
o Row Count Difference – displays number of extra rows in source or target
o Failed row count – displays number of data failures
o Non-Matching Source Rows – displays number of source rows that do not match target rows
o Non-Matching Target Rows – displays number of target rows that do not match source rows
• You can export results to Excel, CSV file, or an XML document using the ‘Export to’ dropdown menu
located below the source tab on the lower panel

14. Close the “QueryPairs” Test Suite results window.

For more information on Execution, please visit Scheduling→ Running a Test Suite in the Knowledge Base

© 202235Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
Running a Detailed Scenario Report

The “Reporting Module” shows the formal reports offered by QuerySurge. Some reports have configurable options
which are found once the report type has been selected and opened. Once executed, reports can be saved as a PDF or
exported to Excel.

1. Click the “Reporting” icon in the bottom panel.

Points of interest:

• A list of reports is shown in the ‘Reporting Center’ (left panel)

2. Select the “Scenario Detail” report under “Run: Scenario Reports”.

3. Click Open Report.
4. Under “Select a Scenario” select ‘ZCITY – CUSTOMER_DIM’ from the dropdown list. (a date time stamp was
appended to the end of the Suite name from the previous exercise)
5. Click the Run Report button.

© 202236Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
 Points of interest:

• In the screenshot above the “Scenario Outcome” has an overall status of “FAILED” since one of the
QueryPairs failed
• The “Test Results” displays the number of passed and failed QueryPairs
• Additional information is displayed in the “Overview” section

6. Close the “Scenario Detail” report tab.

For more information on Reports, please visit Reporting in the Knowledge Base

© 202237Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
Create a QueryPair

Use QuerySurge to create a new QueryPair.

1. Click the “Design Library” icon located on the bottom panel

2. In the left panel under “Design Library” select QueryPairs and select the “XMART” folder and then the “ADDRESS
DIM” sub-folder.

3. Click the “Create New QueryPair” button.

© 202238Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
 Points of interest:

• The “Create a New QueryPair” dialog contains:

o QueryPair Name field – a name for your QueryPair
o Description field – a description for your QueryPair
o Mapping field – for reference back to a mapping document if applicable. (Data mapping is a
process used in data projects by which different data models are linked to each other. Mappings
are typically used for transformations in an ETL process or for consolidation of multiple
databases and/or redundant columns.)
• See the Tutorial Mappings document, which can be found from the Setup Wizard

4. Type “XMART – STREET (SHIPPING)” in the “QueryPair Name” field.

5. Type “Extract the Street portion of XMART_CUSTOMER.SHIPPING ADDRESS by pulling all text before the first
comma” in the “Description” field. (This description was taken from the tutorial mapping document for the
XMART mapping 5.05)
6. Enter “5.05” in the “Mapping” field.
7. Click the Save button.

© 202239Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
8. Type or copy/paste the following under the “Source” editor panel:

select
c.`CUSTOMER ID` as source_id,
substr(c.`SHIPPING ADDRESS`, 1, instr(c.`SHIPPING ADDRESS`, ',')-1) as street
from
XMART_CUSTOMER as c
order by
source_id

9. Type or copy/paste the following under the “Target” editor panel:

select
c.SOURCE_ID as source_id,
[Link] as street
from
CUSTOMER_DIM as c
left outer join
(
select
a.ADDRESS_ID as address_id,
[Link] as street
from
ADDRESS_DIM as a
where
a.SOURCE_SYSTEM = 'XMART'
) a
on
c.SHIPPING_ADDR_ID = a.ADDRESS_ID
where
c.SOURCE_SYSTEM = 'XMART'
order by
c.SOURCE_ID

10. Under “Connection” select “XMART_SOURCE” from the dropdown menu in the “Source” panel.
11. Under “Connection” select “DW_TARGET” from the dropdown menu in the “Target” panel.
12. Click the “Save” icon in both the source and target editor panels.

© 202240Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
Points of interest:

• The ‘XMART – STREET (SHIPPING)’ QueryPair is implemented to validate that the source
SHIPPING_ADDRESS column was loaded into the data warehouse correctly (see Tutorial Mapping
document from the ‘Setup Wizard’)
▪ This QueryPair is validating that the SHIPPING_ADDRESS column from source
XMART, to the target data warehouse is loaded with the following logic:
• Extract the street name from "XMART_CUSTOMER.SHIPPING ADDRESS"
by retrieving all the text before the first comma

© 202241Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
13. Click the “Design Run Time” tab.

14. Click the “Run” button.

15. Click the “View Query Results” button.

© 202242Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
16. Close the “XMART – STREET (SHIPPING)” query results window

© 202243Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
Create a QueryPair Using a Reusable Snippet

A query Snippet is a reusable piece of SQL code that can be embedded in one or more queries. The purpose of a Snippet
is to make common SQL fragments reusable across multiple Queries. A Snippet does not have to be a syntactically intact
query (for example, it might just be a WHERE clause) but there is no reason why it can’t be a complete query (e.g., a
reusable sub-query). This feature lets you develop libraries of ‘Reusable Query Snippets’ to be reused in multiple
queries. If a Snippet shared by multiple queries needs to change, you can make the change once and all of the queries
using the Snippet are changed automatically.

1. Under “Design Library” in the lefthand panel select “QueryPairs” and then select the ‘ZCITY’ folder and then the
‘ORDERS_FACT’ sub-folder.
2. Click the “Create New QueryPair” button.
3. Type ‘ZCITY – COMMENTS’ in the “QueryPair Name” field.
4. Click the Save button.
5. Type or copy/paste the following under the “Source” editor panel:

select
source_order_id,
cast(group_concat(comments order by source_saleitem_id SEPARATOR ' | ') as
char(100)) as comments
from
(
select
si.SALE_ID as source_order_id,
concat([Link],': ',[Link]) as comments,
si.SALEITEM_ID as source_saleitem_id
from
SALEITEM as si,
SALE as s
where
si.SALE_ID = s.SALE_ID
order by
source_order_id
) main
group by
source_order_id

© 202244Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
6. Type or copy/paste the following under the “Target” editor panel:

select
o.SOURCE_ORDER_ID as source_order_id,
[Link] as comments
from
ORDERS_FACT as o
where
o.SOURCE_SYSTEM = 'ZCITY'
order by
source_order_id

7. In the “Source” editor panel select “ZCITY_SOURCE”’ from the “‘Connection” dropdown menu.
8. In the “Target” editor panel select “DW_TARGET” from the “Connection” dropdown menu.
9. Click the “Save” button on both the source and target queries.

Points of interest:

• The “ZCITY – COMMENTS” QueryPair is to validate that the source comments column was loaded into
the data warehouse (see tutorial mapping document from the ‘Setup Wizard’)
▪ This QueryPair is validating that the COMMENT column from source ZCITY, to the target data
warehouse, is loaded with the following logic:
• Populate the COMMENTS column on the target table ORDERS_FACT by concatenating
columns from source ZCITY with the following logic:

© 202245Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
 o [Link] + ‘ : ‘ + [Link]
o For multiple comments separate source comments with ‘ | ‘
10. In the “Target” editor panel, highlight the following text: ‘o.SOURCE_SYSTEM = 'ZCITY’

11. Click the “Save selected SQL as Reusable Query Snippet” icon.

Points of interest:

• The ‘Create New Reusable Query Snippet’ dialog contains:

o Reusable Query Snippet Name field – a name for your Snippet
o Description field – a description for your Snippet

12. Type ‘ZCITY ORDERS Filter’ in the “Reusable Query Snippet Name” field.
13. Click the Save button.
14. Click the Save button under the “Target” editor panel.

© 202246Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
 Points of interest:

• Notice that ‘o.SOURCE_SYSTEM = 'ZCITY'’ has been replaced with ‘${ZCITY ORDERS Filter}’, signifying a
reusable query snippet
• Hover your mouse over the Snippet to resolve, or see, the actual query
o You can click the “Query View” tab at the bottom of the pane to resolve the Snippet as well

15. Execute a Design-Time Run by selecting the “Design-Time Run” tab then click the Run button.
16. Click the View Query Results button.
17. Click the “ailures” tab. (expand the comments field to view the entire column)

© 202247Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
 Points of interest:

• Notice that on the target side, the development team is not using the correct delimiter in their ETL
process
o The target data warehouse is using ‘+’ as its delimiter between comments, but according to the
mapping document, ‘|’ was specified
• Notice line 1249 on the source/target. The data looks the same; however, the target has an extra space
after “sidedoor”. Use View Options -> Show Whitespace to highlight this extra space. (“View options” is
located in the upper right of the results dialog window)

18. Close the “ZCITY – COMMENTS” query results window.

For more information on Reusable Snippets, please visit Design Library→ Working with Reusable Snippets in the
Knowledge Base

© 202248Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
Add a Reusable Query Snippet to a QueryPair

Add a Snippet to an existing QueryPair

1. Under “Design Library”, select the ‘ZCITY’ folder and then select the ‘ORDERS_FACT’ sub-folder.
2. Double click the ‘ZCITY - ORDER_DT’ QueryPair.
3. Under the “Target” editor panel highlight the following text: ‘o.SOURCE_SYSTEM = 'ZCITY'’.

4. Click the “Add Reusable Query Snippet” icon.

Points of interest:

• The left panel utilizes a folder structure similar to Microsoft Windows Explorer (our Snippets are saved in
the root folder, but can be grouped by subfolders manually if desired)
• The top right panel will display the ‘Reusable Query Snippets’ contained within the selected folder once
a folder is selected
• The bottom right panel displays the SQL of the selected Snippet

5. Click the “Reusable Query Snippets” folder in the left panel.

© 202249Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
 Points of interest:
• The top right pane displays the ‘Reusable Query Snippets’, including the one you created earlier
6. Select the “ZCITY ORDERS Filter” snippet and click the OK button.

Points of interest:
• The “ZCITY ORDERS – Filter” snippet replaces the highlighted text with the Reusable Query Snippet
• Now the Snippet is in two locations. If you need to change that part of the query, it can be done in one
place instead of two. (What if the same SQL fragment was in hundreds of QueryPairs? That would save
quite a bit of time.)

© 202250Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
7. Click the Save icon on the target query.

© 202251Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
Create a Test Suite and Schedule an Execution Scenario

QuerySurge Scenarios are collections of Test Suites that are scheduled for execution. Scenarios can be run immediately
after creation, or scheduled for a future date/time, and are viewable in the ‘Run Dashboard’.

1. Click the “Scheduling” icon located on the bottom panel.

2. Click the Create button to create a new test Suite.

3. Type ‘ALL – CUSTOMER_DIM’ in the “Test Suite Name” field.

4. Click the Save button.
5. Expand the ‘XMART’ folder in the “QueryPair Library” panel on the right.
6. Drag and drop the ‘CUSTOMER_DIM’ folder to the ‘ALL – CUSTOMER_DIM’ test suite panel in the middle.
7. Expand the ‘ZCITY’ folder in the ‘QueryPair Library’ panel on the right.
8. Drag and drop the ‘CUSTOMER_DIM’ folder to the ‘ALL – CUSTOMER_DIM’ test suite panel in the middle.

© 202252Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
 Points of interest:

• The ‘ALL – CUSTOMER_DIM’ Test Suite now contains all QueryPairs from the ‘CUSTOMER_DIM’ folders
from both ‘XMART’ and ‘ZCITY’ (14 QueryPairs in total)
• Test Suites can contain a combination of QueryPairs from various sources (XMART and ZCITY) and
varying connection types (flat file and database, respectively)

9. Click the Save icon in the upper left corner of the middle panel to save your Test Suite.
10. Click the Create button to create a new test Suite.
11. Enter ‘ALL – ORDERS_FACT’ in the “Test Suite Name” field.
12. Click the Save button.
13. Expand the ‘XMART’ folder in the ”QueryPair Library” panel on the right.
14. Drag and drop the ‘ORDERS_FACT’ folder to the ‘ALL – ORDERS_FACT’ test suite panel in the middle.
15. Expand the ‘ZCITY’ folder in the “QueryPair Library” panel on the right.
16. Drag and drop the ‘ORDERS_FACT’ folder to the “ALL – ORDERS_FACT” test suite panel in the middle.

© 202253Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
 Points of interest:

• The ‘ALL – ORDERS_FACT’ Test Suite now contains all QueryPairs from the ‘ORDERS_FACT’ folders
from both ‘XMART’ and ‘ZCITY’ (17 QueryPairs)

17. Click Save in the upper left corner of the middle panel to save your Test Suite.

18. Click the Schedule button on the right panel.

19. Choose the One-time radio button and click Next.

© 202254Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
 Points of interest:

• The ‘Schedule Scenario’ window contains:

▪ Scenario Name: field – the name of the Scenario
▪ Set All Dates to: field – sets all Test Suites to run on a particular date and time
▪ Set All Agents as field – sets all Test Suites to run on a particular Agent
▪ Asset Tags: field – optional tag descriptors for this Scenario
▪ A Test Suite schedule area with columns containing:
• Test Suite – Test Suites for this Scenario
• Agent – allows you to select a particular Agent
• Scheduled Date & Time of Run – for selecting a specific date and time
for a run
o Useful for executing tests, e.g., during non-working hours

20. Enter “SCENARIO – XMART/ZCITY – CUSTOMERS AND ORDERS” into the “Scenario Name” field

21. Click the Add Test Suite button.

22. In the “Test Suite” column, click the drop down list and select the ‘ALL – CUSTOMER_DIM’ Test Suite

© 202255Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
23. Click the “Add Test Suite” button and add a second Test Suite by selecting the ‘ALL – ORDERS_FACT’ Test Suite
24. Click in the “Scheduled Date & Time of Run” field for the ‘ALL – ORDERS_FACT’ Test Suite to enable the

selection of a date and time for the run. Then click the “Calendar” icon

© 202256Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
25. Click the Today button for the date portion, and then set the time for the nearest future time from now (your
current time), and click the Save button
26. Click the “Run Dashboard” icon located on the bottom pane. (Your system may have toggled you over to the

run dashboard automatically)

27. Click the Refresh at the bottom of the ‘Scenario Queue’ pane (left pane)
28. Click the “SCENARIO – XMART/ZCITY – CUSTOMERS AND ORDERS” Scenario in the “Scenario Queue” pane (this
may take a few seconds to refresh and appear). Observe that the Scenario just created is now executing

Points of interest:

• The ‘ALL – CUSTOMER_DIM’ Test Suite executes immediately while the ‘ALL – ORDERS_FACT’ Test Suite
remains idle since it was set to execute to the nearest time in the future
• Even when the “ALL – CUSTOMER_DIM” Test Suite completes its execution, the “ALL – ORDERS_FACT”
Test Suite will remain idle until its set execution time is reached

© 202257Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
Points of interest:

• After the time has elapsed, the ‘ALL – ORDERS_FACT’ Test Suite will execute automatically

© 202258Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
Create QueryPairs Using the Query Wizard for Table-Level Comparison

The Query Wizard is a tool that allows you to generate QueryPairs automatically. This is great for those with limited or
no SQL experience, as well as for experts who are looking for a fast and easy way to create QueryPairs for data
migrations, database upgrades, and other database comparisons that do not have transformations. The Query Wizard
can generate QueryPairs for Row Count Comparison, Table-Level Comparison, and Column-Level Comparison validations.
A folder can be created in the Wizard that contains all of the QueryPairs generated. These QueryPairs can also be added
to a new Test Suite so they can be easily run together.

Note: This part of the tutorial illustrates the use of the Query Wizard for validating a database upgrade. In this case, the
data warehouse was upgraded to a new version ‘Connection DW_UPGRADE’ from an older version ‘Connection
DW_OLD’

1. In the “Design Menu” dropdown menu on the top toolbar, select “Launch Query Wizard”.
2. Click Next under the Query Wizard welcome screen.
3. Select ‘DW_OLD’ from the “Source Connection” drop down menu.
4. Select ‘’DW_UPGRADE’ from the “Target Connection” drop down menu.

Points of interest:

• Metadata (or data about our data) pre-populates for the selected databases

© 202259Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
5. Click the Next button
6. Select the “Table-Level Comparison” radio button in the “Comparison Type” section of the Query Wizard
window

Points of interest:

• Column-Level Comparison validates data points in the selected columns and allows for the addition
of Filtering (WHERE Clause) and Sorting (ORDER BY)
• Table-Level Comparison validates each data point in the source table to its corresponding data point
in the target table
• Row Count Comparison validates the number of rows in each of the selected tables

7. Click the Next button.

© 202260Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
 Points of interest:

• Schemas available for selection are retrieved from the source and target databases that you
previously selected
• One, many, or all schemas can be selected

8. Select ‘dw_old’ (the location for the data warehouse data before the upgrade) for the source schema and ‘dw’
(the location for the data warehouse data after the upgrade) for the target schema.
9. Click the Next button.
10. Drag each individual source table and drop it onto its corresponding target table. Scroll down in the ‘Source
Tables’ and ‘Target Tables’ windows if needed.

© 202261Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
 Points of interest:

• As each individual source table is dropped onto its corresponding target table, its Query Mapping
appears in the left-hand column and can be renamed if desired
• Mapped tables show a line connecting the source and target tables with the selected Query
Mapping line highlighted in green
• Auto generated SQL appears in the ‘Source Query’ and ‘Target Query’ panes at the bottom of the
Query Wizard window

11. Click the Next button.

12. Select the “Create a New Folder” radio button in the “Design Library” section of the Query Wizard window.
13. In the “Parent Folder” drop down menu, select the root “QueryPairs” folder.
14. Enter ‘WIZARD – DW_UPGRADE TEST’ in the “Folder Name” field.
15. Select the “Yes” radio button in the “Scheduling” section of the Query Wizard window.
16. Enter “WIZARD – DW_UPGRADE TEST” in the “Suite Name” field.

© 202262Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
 Points of interest:

• The Query Wizard will automatically create the test assets:

o A new folder in the ‘Design Library’ with the name entered in the ‘Folder Name’ field
o All QueryPair(s) for testing the tables selected
o A new Test Suite in the ‘Scheduling’ module with all of the generated QueryPairs

17. Click the Next button.

© 202263Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
 Points of interest:

• A review screen appears with the details for your Query Wizard execution

18. Click the Create button.

© 202264Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
 Points of interest:

• A test folder named ‘WIZARD – DW_UPGRADE TEST’ was created in the ‘Design Library’ module
• A Test Suite named ‘WIZARD – DW_UPGRADE TEST’ was created in the ‘Scheduling’ module
• Seven QueryPairs were created (one for each of the source and target tables mapped)

19. Click the OK button.

20. In the “Design Menu” dropdown menu on the top toolbar, select “Library Explorer”.
21. In the “Design Library” tree (left panel), navigate to the folder ‘QuerySurge Design -> QueryPairs -> WIZARD –
DW_UPGRADE TEST’.

Points of interest:

• The name of the folder is the name you entered into the ‘Folder Name’ field in the Query Wizard
• The name of a QueryPair is in the following format:
o Validation Type: source [Link] table - target [Link] table

19. Double click the ‘Data (Table): dw_old.address_dim - dw.address_dim’ QueryPair and notice the SQL for source
and target queries that was automatically generated.

© 202265Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
 Points of interest:

• The QueryPair is selecting all columns from the source connection and target connection
• During execution, all columns will be compared between source and target

20. Execute a ‘Design-Time Run’ by clicking the “Design-Time Run” tab and clicking the “Run” button.

Points of interest:

• The QueryPair compared 5,944 source and target rows, which passed
• Feel free to view the results

© 202266Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
21. Click the ”Scheduling” icon located on the bottom panel.
22. Select the ‘WIZARD – DW_UPGRADE TEST’ Test Suite in the left pane.

Points of interest:

• The name of the Test Suite is the name entered into the ‘Name’ field during the Query Wizard
• All the QueryPair(s) are included in the Test Suite upon generation

23. Right click the ‘WIZARD – DW_UPGRADE TEST’ Test Suite and select the “Run Now” option.
24. If a “Run Scenario” dialog is displayed, click the OK button.
25. The “Run Dashboard” is displayed. Double click the ‘WIZARD – DW_UPGRADE TEST’ row in the middle panel to
review the results.

© 202267Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
 Points of interest:

• One QueryPair of the seven total failed

26. Click the ‘failed’ QueryPair.

27. Select the “Failures” tab.
28. Click “View Options” dropdown in the upper right of the lower panel.
29. Select “Show Inferred Data Mismatches” radio button – Since no keys have been specified, using Inferred Data
Mismatches provides some additional insights into the data causing failures.

Points of interest:

• Four email addresses do not match from source to target

• Investigation is needed to determine the reason for the data differences that occurred during the
upgrade
© 202268Real-Time Technology Solutions, Inc.
22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
30. Close the “QueryPairs” query results window.

31. Click the “Data Intelligence Reporting” icon located on the bottom panel.
32. Select the “Scenario Detail" report from the left panel.
33. Click the “Open Report” button.
34. Select the ‘WIZARD – DW_UPGRADE TEST’. (a date time stamp will be appended to the end of the name in the
‘Select a Scenario’ dropdown list)
Click the Run Report button.

Points of interest:

• Notice the number of individual field verifications that QuerySurge was able to accomplish, just
under a quarter million, in less than 9 seconds on our tutorial instance (completion time will vary
based on your hardware)

For more information on Reports, please visit Reporting in the Knowledge Base

© 202269Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
Review of Data Health Dashboard

The Data Health Dashboard gives you a real-time view into your project progress at all levels, from QueryPair
development to execution and results. Data Widgets are configurable, so you can see the slice of your project that you
want to see, in the way you want to see it.

1. Click the “Design Library” icon located on the bottom panel.

2. Click the “Welcome” tab if it’s not already in view.

Note: If the available widgets panel is not displayed, then click the ‘Add Widget’ Button

Points of interest:

• The “Welcome” tab defaults to one panel containing four Widgets (center panel)
• Click the ‘Add Widget’ button for a list of additional ‘Available Widgets’ (right panel)
• Panels can be configured by dragging and dropping Widgets as desired

3. Click the Maximize button on the “Scenario Outcome / Data Reliability” Widget for a closer view.

© 202270Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
 Points of interest:

• Pass/Fail Verifications of your recent Scenario Runs displayed as a bar graph of Pass/Fail Rate
• Data Reliability per Scenario metric displayed as a blue line
• The vertical axis for the bar graph is on the left, while the vertical axis for the blue line is on the right

4. Click the ‘Scenario Outcome / Data Reliability’ Widget title to make it editable, type ‘Tutorial’ at the beginning of
the existing title and hit enter.
5. Turn off the ‘Total Verifications Passed’ data in the Widget by clicking on the legend at the bottom.
6. Zoom in on the data by clicking on the “SCENARIO” bar in the graph and dragging your mouse across to the left.
(Zoom occurs upon the release of the mouse button)

© 202271Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
Points of interest:

• Title changes are reflected above the graph and in the title bar
• ‘Total Verifications Passed’ in no longer reflected in the graph, you can add the data back by clicking
on the greyed-out legend
• The graph is zoomed in on the bars that were highlighted in the previous step, and a ‘Reset zoom’
button now appears to revert.

• You can point many of the Widgets at specific data by clicking on the ‘View Settings’ icon
• You can have multiple panels of Widgets by clicking on the ‘Add Panel’ button

For more information on Widgets, please visit Reporting→ Project Widgets in the Knowledge Base

© 202272Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
Summary

You have just finished the QuerySurge tutorial. This is what you completed:

➢ Created a QueryPair using the Column-Level Comparison Query Wizard

➢ Reviewed existing and created new QueryPairs
➢ Created Reusable Query Snippets
➢ Executed Design-Time Runs
➢ Investigated ‘Design-Time Run’ failures
➢ Scheduled and executed a Scenario
➢ Generated and reviewed reports
➢ Generated QueryPairs using the Table-Level Comparison Query Wizard
➢ Reviewed Widgets

There are additional examples within the sample data and QueryPairs, many of them which are more complex than the
exercises you completed in this tutorial. Now that you have a solid foundation on using QuerySurge, try them out. You
can access the mapping and data model document from the “Setup Wizard.”

© 202273Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
Deleting the Tutorial Data

This section is for removing the tutorial data from QuerySurge.

Warning: Deleting the tutorial data will permanently remove data. Please read this section carefully before proceeding!

The following entities will be permanently deleted (original sample data):

➢ The ZCITY folder and all QueryPairs contained within
➢ The XMART folder and all QueryPairs contained within
➢ The Snippet named “ZCITY – Filter”
➢ The Test Suite named “ZCITY – ALL”

The following entities are not deleted: (can be deleted manually)

➢ QueryPairs created with the Query Wizard
➢ Snippets created during the tutorial
➢ Test Suites created during tutorial
➢ Scenario execution data and results

© 202274Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
Appendix

Documentation

• System Requirements Guide – hardware and software needed to run QuerySurge minimally and
optimally
• Installation Guides
o for Windows (Single Machine)
o for Windows (Multi Machine)
o for Linux (Single Machine)
o for Linux (Multi Machine)
• Configuration Guides for connecting to a broad range of technologies.

QuickStart User Guide – Everything you need to know to get started

with QuerySurge

Data Warehouse Testing

Comprehensive testing of a data warehouse at every point throughout the ETL (extract, transform, and load)
process is becoming increasingly important, as more data is being collected and used for strategic decision-
making.

Data warehouse projects are undertaken because of mergers and acquisitions, compliance and regulations, data
consolidation, and the increased reliance on data-driven decision making (using Business Intelligence tools, etc.).

Any way you slice it, the data warehouse/business intelligence (BI) platform is complex and presents many data
quality and testing problems to overcome. Some of the main challenges of data warehouse testing are:

• Data Completeness. Verifying that all data has been loaded from the sources to the target data
warehouse
• Data Transformation. Ensuring that all data has been transformed correctly during the ETL process
• Data Quality. Ensuring that the ETL process correctly rejects, corrects, ignores, substitutes default
values, and reports invalid data
• Regression Testing. Ensuring existing functionality remains intact each time a new release of code is
completed

© 202275Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
QuerySurge has five different modules:

Design Library

Use the QuerySurge “Design Library” tools to create collections of powerful tests across your data warehouse
architecture.
❑ Create test QueryPairs between any two points (Source, Staging, Data Warehouse, Data Mart) in your
architecture
❑ See query history for all of your queries
❑ Run QueryPair executions as you build queries to ensure they produce the required results
❑ Make your design flexible with Reusable Query Snippets - Snippet libraries of query fragments that you
can use to modularize your queries, helping to speed up the process of bulk QueryPair updates
❑ Run the ‘Query Wizard’, which provides information for (1) row count compares, (2) full table compares,
and (3) column compares without writing any SQL code

Scheduling

Schedule your testing by time and location for maximum productivity. Schedule your tests for the specific
time and day or run tests when an event, like the ETL process, completes.
❑ Mix-and-match test QueryPairs in QuerySurge Test Suites to meet specific project execution goals
❑ Build groups of QuerySurge Test Suites to test specific mappings, ETL logic or Data Warehouse Sources
❑ Build QuerySurge Scenarios for scheduling your execution runs at specific dates and times
❑ Use the execution API for event-based scheduling

Run Dashboard

Visualize and track the real-time progress of your running Scenarios on the QuerySurge ‘Run Dashboard’. A
graphical display helps you to follow the flow of your queries, and you can drill-down to see test details and
data failures.
❑ View query execution progress live via graphical run status displays
❑ Drill-down into data as QuerySurge executes to examine results as they become available during
execution
❑ See real-time statistics for each QueryPair executed and for the Scenario execution as a whole
❑ Alert your team about the status of execution via custom email notifications
❑ Export detailed results in Excel, CSV, or XML formats to share with team members and other project
stakeholders

Data Intelligence Reporting

Get a comprehensive audit and share detailed results with others. Use QuerySurge reports to share both
high-level and detailed views of your testing with team members, managers, and business stakeholders.

❑ Choose from a wide selection of configurable reports within the QuerySurge ‘Reporting Center’
❑ Built-in reports range from high-level summary reports to lower-level, detailed reports that contain a
complete audit trail of test modifications

© 202276Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
 ❑ Configure reports for specific date ranges, asset types, or specific executions, to get the view into your
results that you need
❑ Export your reports as Excel or PDF files to share within your organization, or to archive for future audit
needs

Administration

The administration module provides access to the control features of your QuerySurge installation. Here, you
have the ability to create and manage user profiles, database connections, Agent configuration, data
archiving and automated email notification options.
❑ Create and manage QuerySurge users and Agents
❑ Manage QuerySurge data storage with the included storage tools
❑ Create and manage connections to source and target data stores
❑ View QuerySurge server information, including configuration settings and your application licensing
details
❑ Create automated email notifications to send results to your team at the end of a test run

The QuerySurge Testing Process

Testing and validating the ETL process is the key component to the success of a data warehouse
implementation. Bad data caused by defects in the ETL process can cause data problems in reporting that can
result in poor strategic decision making.
Example: A large fast-food company depends on BI reports to determine how much raw beef to order on a
monthly basis, by sales region and time of year. If these reports are not correct, then the company could order
an incorrect amount, which could cost the company millions of dollars in either lost revenue or wasted product.

Developers utilize ETL tools to accomplish the extracting, transformation and loading of data from sources into
target systems.

Test Points and ETL Legs

• An “ETL Leg” refers to a single ETL process that moves/transforms data between two discrete points in the data
warehouse architecture
• A full ETL process may have multiple legs
• Test points and single ETL leg: the verification is between the source and the target for that leg
Example: An operational source database (source test point) is extracted, transformed, and loaded into a data
warehouse (target test point). Testing is conducted across this ETL leg

© 202277Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
• Test points and multiple ETL legs: The multi-leg approach is to validate multiple legs of the ETL process in a
single test, ‘jumping over’ ETL legs in the process. A typical multi-leg test is to validate the entire ETL process
from data sources to final data endpoint utilizing test points only at the data sources and at the final data
endpoint

Data Mapping Document

A source-to-target map is a “data mapping” document and is the most important artifact when building or
testing a data warehouse. Data mapping documents are often created in Excel spreadsheets or Word
documents. The document acts as a central listing of the “functional” requirements. Testers use the mapping
document to verify that the data has been extracted from the source databases, data stores and files and into
the target data warehouse and data marts correctly. The following information is contained within the mapping
document:

© 202278Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
QuerySurge: The Process in a Nutshell
• Review the mapping document
• Determine the optimum percent of coverage or amount of data that is required to be tested, based upon time
and resources
• If not testing 100% of the data, determine the exact sampling of data needed
• Create test cases that exercise the requirements of the mapping document
• Create pairs of SQL queries (QueryPairs) – one aimed at the source database or file, one at the target data
warehouse or data mart
• Bundle groups of QueryPairs (Test Suites) into test Scenarios
• Schedule Scenarios to run (a) immediately, (b) at a certain day and time, or (c) automatically after an event
• Analyze and drill down into your results and identify bad data and data defects with our robust reporting engine.
• Report defects in your defect tracking tool (i.e., HP Quality Center, IBM Rational Quality Manager, Jira, Bugzilla,
etc.)
• Have reports sent automatically via email to team members
The QuerySurge Architecture

© 202279Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
About the QuerySurge™ Architecture
QuerySurge is a locally installed, browser-based testing tool. Supporting all current browsers (Chrome, Firefox, IE, Safari,
etc.), QuerySurge is perfect for teams that are geographically distributed. QuerySurge extracts the data-under-test to its
own, separate infrastructure, which
eliminates processing overhead on the
source and target database servers in your
data warehouse architecture. The
QuerySurge architecture is comprised of an
application server, database server and
Agents.

QuerySurge Application Server and

Database
QuerySurge utilizes a Tomcat application
server and a MySQL database. The database
is bundled with and embedded within the
product.

QuerySurge Agents
QuerySurge Agents are the components of the architecture that execute queries against source and target data sources,
returning the results to the QuerySurge database. The Agents execute SQL queries, validating each piece of data
throughout the ETL process. This exposes all data mismatch failures, row count differences, and column type mismatch
failures, affording you the ability to test to 100% of your data quickly.

Although the Agents issue queries to both the source and target databases, they do not reside on the physical source or
target database boxes. QuerySurge Agents can be deployed on the same box as the QuerySurge Application Server and
QuerySurge Database Server in a single box install or on any “satellite” boxes in the environment (often, these are test
lab boxes or available desktop boxes). The QuerySurge Agent, when it receives a bundle of queries, can run multiple
queries simultaneously (in parallel).
One of the features QuerySurge gives you is the ability to raise your ‘testing throughput’. The idea is to be able to fire off
QueryPairs in bulk against your sources and targets so that you can execute at the highest level your environment can
support. The QuerySurge Agent lets you do this, because you can deploy multiple Agents in your environment – on
‘satellite’ boxes surrounding your QuerySurge server (note that each Agent can run multiple query threads as well).
QuerySurge allows you to install up to 10 Agents.

More Agents = more queries = more throughput

How many Agents are appropriate for your environment? The answer is – you find out by experimentation. Once you
have built an initial test library, start with 2 or 3 Agents, and see how your sources and targets behave. Add additional
Agents in a subsequent cycle, again monitoring the source and target behavior. As you continue to add Agents, the loads
on sources and targets will grow with query volume – and source/target response times will start to grow as well. Once

© 202280Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
you have identified the level where response times have started to increase, back down to the previous level. This level
is roughly the maximum throughput your environment can support.

Database / Data Warehouse Support

In principle, QuerySurge can support any JDBC-compliant data source. QuerySurge currently ships with Oracle, MySQL,
Teradata, Aster, SQL Server, DB2, Informix, Netezza, Sybase, in-memory database, flat file support, and supports many
other data sources.

© 202281Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]
 About RTTS

RTTS was founded in 1996, and has cultivated partnerships with the world's leading
test tool vendors, including IBM, Microsoft, and HP. We are headquartered in New
York City and our satellite locations are in Philadelphia, Atlanta, and Phoenix. Many
of our consulting and education services are also offered through the cloud, so that
no matter where you are, RTTS will ensure application functionality, performance, scalability, and security for your organization.

RTTS is the premier professional services organization that specializes in

providing software quality for critical business applications. We offer the most
comprehensive suite of quality assurance services. We’ve helped 400+
organizations drive positive results from their software development projects.

For more information, please visit [Link]

About QuerySurgeTM

QuerySurge is the only automated software tool built specifically for ETL testing. It can verify as much as 100% of all data from
source systems, through the ETL process, to the target data warehouse and data marts.
QuerySurge has increased test coverage and reduced test cycle time for numerous
Fortune 500 organizations, helping them to mitigate risk and meet business
requirements. For more information, please visit RTTS’ team of test experts developed
QuerySurge ([Link]) to address the unique testing needs in the data
warehousing and data migration area. It has been implemented on projects ranging
from large data warehousing and ETL processes to data migrations, database upgrades,
integration testing, data load testing and system patch testing.

For more information, please visit [Link]

Click here to contact us

for more information

© 202282Real-Time Technology Solutions, Inc.

22 West 38th Street, 11th Floor • New York, NY 10018
[Link]