Share:

Query Compare Test Case can be used for comparing data from a single Source and Target query. It provides a visual query builder interface to select columns to compare and generates the query automatically based on the data model. Query Compare Test Case also allows for specifying expressions on Source columns to match with the Target columns. In a typical scenario, this test case is used to verify the data moved from the source to target database. 


Create a Query Compare Test Case

There are several methods to launch the Query Compare Test Case in the ETL Validator.

Depending on your convenience, select any one of the following methods:

  • Method 1
    Mouse hover over the plus icon and select Query Compare Test Case.
  • Method 2
  1. On the side menu, click TEST CASES.
  2. Click Tests +.
  3. Under Wizards for Creating Tests, select Query Compare Test Case.
  • Method 3
  1. On the side menu, click HOME.
  2. Under Common Tasks, Click Create Tests.
  3. Under Wizards for Creating Tests, select Query Compare Test Case.

Open/Edit a Query Compare Test Case

To open/edit a Query Compare Test Case,

  1. On the side menu, click TEST CASES.
  2. In the Test Cases tree, select the folder containing the Query Compare Test Case.
  3. Click on the name of the Query Compare Test Case.
InformationYou can also open a test case from the TEST RUNS view.

Query Compare Test Case Details

When you want to create or edit a Query Compare Test Case, complete the information in the following pages:

Start

In the Start page, you will complete basic details like name, database and folder.

This page contains the following:

TEST CASE NAME

Enter a name for the Query Compare Test Case.

TEST SUITE

Select the test suite where you want to create the test case.

TEST FOLDER

Select the folder where you want to create the test case.

PRIORITY

Select the priority to to let your team know about the significance of the Test Case. Available priorities are Lowest, Low, Medium, High, and Highest.

Mark as Private

If selected, the user who selected this option and the admin users will only be able to work on this test case. If an admin user selects this option while creating the test case, normal users can see the test case, but can not open and work on the test case.  

Description

Enter a description for the Query Compare Test Case.

Label

Create a label for easy identification of test plan. To create a label, click the Click to add label link, enter the string, and press Enter.

TARGET DB CONNECTION

Select a connection for the target database.

SOURCE DB CONNECTION

Select a connection for the source database.

WORK SCHEMA

Select a workschema where temporary tables with test data will be stored.

Queries

In this page, you create and execute a query. To create a query, simply type in the query for Target and Source or use Query Builder.

This page contains the following:

Query Builder

Allows you to create a query using the data model defined for the database. If there is no data model, you can create one.

Execute Query

Allows you to execute the query and see results.

Execute Count Query

Allows you to view the actual count of result rows. 

Add Parameters

Allows you to add parameters to the query.

Set Timeout

Allows you to set timeout in minutes for the ETL Server to wait before the query produces results.

Mapping

In this page, you will select the columns and create joins on the columns.

This page contains the following:

Settings

  • Mark Test Case as Failure if records on source and target are empty. Selecting this option will fail the test case if there is no data in records pertaining to source and target databases.
  • Need to trim. Any whitespaces found in the source and target columns will be removed.
  • Use Copy Command. This option is for fast loading of data into tables, from PostgreSQL database. However, use this option when PostgreSQL database supports UTF8 characters. If you receive any failures due to UTF8 characters after running the test case, disable this option and re-run the test case.

Remap Columns by Name

Selecting this option will display the mappings by column name.

Remap Columns by Order

Selecting this option will display the mappings by column order.

Run

In the final Run page, you will run the test case. Before running the test case, read through the settings below once and understand their purpose.

The Run page contains the following:

Allowed Variation Settings

You can allow ETL Validator Server to pass the test case with percentage of variation in the following cases:

  • Only in Target. Enter the percentage of variation to allow in Target. 
  • Only in Source. Enter the percentage of variation to allow in Source.
  • Data Differences. Enter the percentage of variation to allow for data differences.
  • Duplicate Source. Enter the percentage of variation to allow for duplicates in Source.
  • Duplicate Target. Enter the percentage of variation to allow for duplicates in Source. 
  • Compute Column Stats. Selecting this option displays unmatched differences columns wise.
  • Truncate Result tables. Selecting this option empties the result tables after the test case is run.

Run

Allows you to run the test case.

Abort

Allows you to stop the test case execution.

Remove

Allows you to remove the selected test plan run.

Remove All

Allows you to remove the complete test plan run history.

Refresh

Allows you to refresh the Run page.

As soon as the test case is run, you will see the results for the following:

  • Unmatched –This category is further divided into three tabs: Data Differences, Join Based Differences, and Row Differences. The data that appears in each of the tabs is understood by their names.
  • Matched –This category displays unique matches from Source and Target.
  • Source – This category displays the Source data, any duplicates, and unique keys.
  • Target –This category displays Target data, any duplicates, and unique keys. 

Beyond the above information, you can export the results, view queries, see the run durations, and see the datasources for Source and Target.

PreviousNext

© Datagaps. All rights reserved.
Send feedback on this topic to Datagaps Support