Description

Test Data Queries are a key part of GenRocket's Self Service Test Data Portal. We organize all the Test Data Query functionality into a part of the GenRocket platform we call "G-Queries".


Story

A tester needs to use real data from tables in two databases as part of their test data. This data then needs to be blended with synthetically generated test data to test a rewards program. 

Two Test Data Queries will need to be created to obtain the required real data from these databases. The reward level will then be used within a Test Data Rule to assign a specific credit card prefix for each customer within the generated test data. 


Actors

  • Project (or Project Version) - The Project or Project Version determines what Domains and Attributes will be used for the Test Data Queries, Rules, and Case.
  • Test Data Query - The configuration for performing an SQL query on each database to obtain real data for test data generation. 
  • Test Data Rules - Determines what card prefix will be assigned based off of the customer's reward level.
  • Test Data Case - Applies Test Data Queries and Rules during test data generation.

Queried Databases

Data will need to be obtained from these databases and tables by performing a query:  

  • Customer Database - Customer table and Address table
  • Reward Database -  Reward table


Queried Data Columns

For this example, the following real data will need to be obtained from 2 separate databases using Test Data Queries:

  • address
  • city
  • state
  • zip code
  • reward level


Synthetically Generated Data

  • id,
  • firstName
  • lastName
  • birthDate
  • ssn
  • credit card number (prefix based on reward level)


Configuration Steps

This example uses 3 Self-Service Test Data Platforms: G-Cases, G-Rules, and G-Queries. To achieve this testing goal, you will need to do the following: 

  1. Set up a Test Data Query for each database
  2. Set up a Test Data Rule for assigning the credit card prefix
  3. Set up a Test Data Case with the required Domains and Loop Counts
  4. Add the Test Data Rules and Queries to the Test Data Case
  5. Download the Test Data Case Suite
  6. Run the Test Data Case Command Line in a Command or Terminal Window


Setup Overview

The testing team requires the following data Attributes for secure customer test data: 

  • Synthetic Data - id, firstName, lastName, birthDate, ssn
  • Real Data - address, city, state, zipCode

Additionally, a synthetically generated credit card requires a specific prefix based on this reward criteria. The reward level will be used to assign the required credit card prefix for each customer:


if reward.level == "Gold"
   then prefix starts with 264444
if reward.level == "Silver"
   then prefix starts with 264445
if reward.level == "Bronze"
   then prefix starts with 264446
else prefix starts with 2644467


How to Set up this Example and Generate Your Test Data


A Test Data Query can also be run on its own with a Scenario, Scenario Chain, or Scenario Chain Set. These steps are shown at the bottom of the article. 


Step 1: Set up Address Test Data Query

The first Test Data Query will query the Customer database for this data: 

  • address
  • city
  • state
  • zip code

Step 1a: Add the Address Test Data Query to the Project

  • Select the appropriate Project within the Project Dashboard of the GenRocket web platform.

  • Use the Filter input field or scroll up/down to locate the Project. Click on the Project Name to select it.


  • Expand the Self Serve Menu Options drop-down menu for the appropriate Project Version and then select G-Queries.



  • Click on Add G-Query.


  • Enter a Name and Description (Optional) for the Test Data Query.

  • Select Query Before for the type and click Save to finish.



  • The new Test Data Query will appear in the table as shown below:



Step 1b: Configure Address Query Parameters

  • For this example, the following parameters have been configured: 
    • resourcePath* - #{resource.output.directory}
    • resourceSubDir - customer
    • resourceName* - config.properties
    • grvar1 - #{Customer.id}

  • Click Save after the query has been entered to save the change.



Step 1c: Enter the Address Query

A query will need to be entered to obtain the customer address information.

  • The following query has been entered for the Address Test Data Query:
    select address, city, state, zip_code
from address
where customer_id = grVar1

  • Click Save after the query has been entered to save the change.



Step 1d: Add Columns to the Address Query

A Domain Attribute will need to be added to the Test Data Query configuration for each data column. These Attributes are will store the obtained real data values.

Each Domain, Attribute, and Column will need to be added to the Test Data Query. Queried data will be populated for these Attributes: 

  • address
  • city
  • state
  • zip code


Note: Repeat these steps 4 times to add this information for each Domain Attribute: 

  • Click on Add to select a Domain and Attribute.


  • Select the Domain and Attribute from each drop-down menu.

  • Enter a Column Name for the data. Click Save to finish.


  • The table will appear as below once all Domain Attributes have been added:



Step 2: Set up the Reward Test Data Query

The second Test Data Query will query the Reward database for this data: 

  • reward level

Step 2a: Add the Reward Test Data Query to the Project

  • Click on Add G-Query to add another query.


  • Enter a Name and Description (Optional) for the Test Data Query.

  • Select Query Each as the type and click Save to finish.


  • The new Test Data Query will appear in the table as shown below:


Step 2b: Configure Reward Query Parameters

  • For this example, the following parameters have been configured: 
    • resourcePath* - #{resource.output.directory}
    • resourceSubDir - reward
    • resourceName* - config.properties
    • grvar1 - #{Customer.id}

  • Click Save after the query has been entered to save the change.



Step 2c: Enter the Reward Query

A query will need to be entered to obtain the reward level information.

  • The following query has been entered for the Reward Test Data Query:
    select reward_level
from reward
where customer_id = grVar1


  • Click Save after the query has been entered to save the change.



Step 2d: Add Columns to the Reward Query

A Domain Attribute will need to be added to the Test Data Query configuration for each data column. These Attributes will store the obtained real data values. Each Domain, Attribute, and Column will need to be added to the Test Data Query. 


Queried data will be populated for this Attribute: rewardLevel.

  • Click on Add to select a Domain and Attribute.




  • Select the Domain and Attribute from each drop-down menu.

  • Enter a Column Name for the data.




Step 3: Test Data Rules Configuration

A Test Data rule will need to be set up to assign the credit card prefix based on the reward level for each customer. Here are the conditions that need to be defined as rules: 


if reward.level == "Gold"
   then prefix starts with 264444
if reward.level == "Silver"
   then prefix starts with 264445
if reward.level == "Bronze"
   then prefix starts with 264446
else prefix starts with 2644467


Four Test Data Rules will need to be created (1 for each reward level and a default rule) and are shown below. Each rule contains a condition and an action. The default rule will assign a specific card prefix of 2644467 when no reward level is present in a customer record. 



Note: To learn more about setting up Test Data Rules, please click here.


Step 4: Test Data Case Configuration

A Test Data Case can be configured with the created Test Data Queries and Test Data Rules. This allows you to run the Test Data Case command, which also automatically applied any added queries and rules during test data generation.



A Test Data Rules Suite has been added to the Test Data Case. 


Both Test Data Queries have also been added to the Test Data Case.



When the Test Data Case Domain is run, the queries will also be run and the rules will be applied to assign the appropriate card prefix. 



Note: A Test Data Query can be downloaded on its own and run with a Scenario, Scenario Chain, or Scenario Chain Set. This example shows how to query real data from multiple databases and blend it with synthetically generated data based on specified rules. To see how to run a query by itself, see the last section of this article.


Step 5: Download the Test Data Case and Scenario

Next, the Test Data Case will need to be downloaded and run with a Scenario, Scenario Chain, or Scenario Chain Set. For this example a Scenario will be used.

  • Click on the Download (Cloud) option located on the far right of the table in the Actions Column to download it to your local computer.


  • Click on the Download (Cloud) option within the Scenarios Pane of the Project Dashboard to download it to your local computer.



Step 6: Run the Test Data Case with the Scenario

The Test Data Case and the Scenario (or Scenario Chain/Scenario Chain Set) are run using the command line shown at the bottom of the screen for the Test Data Case in this example.



This command can be copied and pasted into a CMD or Terminal Window and modified accordingly. These values will be different depending on the name of the Test Data Case and the Scenario to be run.

  • Customer.gtdc - Name of the Test Data Case to be run.
  • <ScenarioName.grs> - Name of the Scenario, Scenario Chain, or Scenario Chain Set to run. For this example, it is CustomerScneario.grs.


Open a Command or Terminal Window and set the directory to where the Test Data Case and Scenario were downloaded to your local computer. The following images show the command line in a CMD window.



Then replace the appropriate values within the command line. The Test Data Case and Scenario Name can be changed accordingly. Press the Enter key to run the command.



Generated Test Data

The generated data would appear similar to what is shown below:



(Optional) Download an Individual Test Data Query and Scenario

A Test Data Query can be downloaded individually and run with a Scenario, Scenario Chain, or Scenario Chain Set. When ran on its own, it will query the database or CSV file for real data and blend it with the synthetic data without applying any rules. 

  • Click on the Download (Cloud) option located on the far right of the table in the Actions Column to download it to your local computer.


  • Click on the Download (Cloud) option within the Scenarios Pane of the Project Dashboard to download it to your local computer.


(Optional) Run the Test Data Query with the Scenario

The Test Data Query and the Scenario (or Scenario Chain/Scenario Chain Set) are ran using the command line shown at the bottom of the G-Queries Dashboard for a selected query. 


This command can be copied and pasted into a Command or Terminal Window and modified accordingly.



The following is an example of the command line for running this example's Address Test Data Query with a Scenario.



These values will be different depending on the name of the Test Data Query and the Scenario to be run.

  • AddressQueries.gtdq - Name of the Test Data Query to be run.
  • <ScenarioName.grs> - Name of the Scenario, Scenario Chain, or Scenario Chain Set to run.


Open a Command or Terminal Window and set the directory to where files are downloaded from the GenRocket web platform to your local computer. The following images show the command line in a CMD window.



Then replace the appropriate values within the command line. The Test Data Query and Scenario Name can be changed accordingly. Press the Enter key to run the command.



Generated Test Data

The generated data would appear similar to what is shown below. In this example, the address, city, state, and zip code were queried from a single database and blended with the synthetically generated test data.