Lesson Summary

In the previous lesson, we populated all three table each with 50000 rows of data so that we would have data from which we can perform data masking.


This lesson walks through the steps to create and run Scenarios that will update 50000 rows of data into each of the three database tables while masking data in the sensitive columns in their respective tables.  


Table Sensitive Data

The sensitive data columns the must undergo synthetic data replacement in each table are described below:

  • User
    • external_id
    • first_name
    • last_name
    • username
    • ssn
    • password - replaced by the pattern of referencing the id of the given table row.
  • grant_history
    • granter_id
    • first_name
    • last_name
    • username
    • credit_card
  • notification_setting
    • user_external_id
    • username


Lesson Steps

Step 1 -  Let GenRocket Create the Scenario

  • On the Project Dashboard, go to the Domains panel and click on the 'User' Domain link.


Step 2 -  Choose the Quick Scenario Option from Domain Dashboard

  • Click the Quick Scenario button


Step 3 - Name your Scenario

  • Set the Name parameter to UserUpdate
  • Click the Next button


Step 4 - Select which Receivers are Used in the Scenario

Only the SQLUpdateReceiver is needed to update data into a database table.

  • Click the SQLUpdateReceiver's check box
  • Click the Continue button


  • After clicking Continue button, the Scenario will be created with Parent/Child relationships, and the Scenario Dashboard will appear.


Step 5 - Modify User Domain's Attributes to Reference Master Domain Attributes

  • In the Scenario Domains Panel, click the User link


Step 6 - Reference Master.externalId for User.externalId

You are now in the Scenario Domain Dashboard.  We want to reference the Master Domains Attributes to guarantee that the same, externalId, firstName, lastName and username are generated the same in all three Scenarios.externalId, firstName, lastName and username are generated the same in all three Scenarios.

  • Goto the User externalId Attribute
  • Remove its current Generator
  • In the Generator search dialog, enter, reference
  • Click  ReferenceGen of its + icon


Step 7 - Search for an Attribute to reference

  • Double click the reference parameter


Step 8 - Select an Attribute to reference

  • Click the Master Scenario Domain link
  • Click the externalId Attribute link


Step 9 - Save your modifications

  • Click the Save Generator button


Step 10 - Set Master Domain referenced for User firstName, lastName, and username

  • Repeat steps 7 through 10
    • User.firstName references Master.firstName
    • User.lastName references Master.lastName
    • User.username references Master.username


Step 11 - Modify the ssn Attribute to generate in sequential order

  • Goto the User Domain's ssn Attribute
  • Change the generationType parameter from random to sequential
  • Click the Save Generator button


Step 12 - Modify the master Attribute to reference the Master.query Attribute

The master attribute was automatically added to the User Domain, when the Quick Scenario option was selected, to guarantee referential integrity between the Master and User Domain.  


We want the User.master Attribute to reference the Master.query Attribute because the Master.query Attribute uses the QueryBeforeLoop Generator to select the external Ids from the User table. 

  • select external_id from alpha.user order by external_id


The User.master Attribute will be used in the 'where clause' of the SQL update statement to locate the correct row for updating.

  • Goto the User Domain's master Attribute
  • Double click the reference parameter and select to reference the Master's query Attribute
  • Click the Save Generator button



Step 13 - Select the SQLUpdateReceiver

  • Click on the Scenario Domain: User breadcrumb


  • In the Scenario Domain Receivers Panel, click the SQLUpdateReceiver link


Step 14 - Enter the SQL Update Statement

  • Select the Data tab
  • Enter the following SQL statement


Update alpha.user set 
  external_id = 'var1',
  first_name = 'var2',
  last_name = 'var3',
  username = 'var4',
  ssn = 'var5'
where external_id = 'var6'


  • Click the Save button


Step 15 -  Set the Attribute to reference their respective variables

Each variable used in the update statement (e.g. var1, var2, etc.) must be referenced by at least on Attribute so that variable can pass back a generated value to the update query. 

  • Click the Attributes tab
  • On the right hand side, select the Attribute you want to update the variable for
  • Update the variable value and click Save
     
  • Set the variables for the following Attributes: 
    • set externalId to var1
    • set firstName to var2
    • set lastName to var3
    • set master to var6
    • set ssn to var5
    • set username to var4
  • When you have completed this step, click the Attributes tab and your screen should look like the following: 


Step 16 - Download Scenario

You are now ready to download your update Scenario to your local computer where it can be executed by the GenRocket command line interface.

  • Click the Download button


Step 17 - Run your Scenario

Normally downloads go to to your download directory; make sure you know where you downloaded your Scenario.

  • On your local computer, bring up a command line terminal
  • Change to the folder where you downloaded the Scenario
  • At the command prompt, type the following command and press the Enter key on your key board
    • genrocket -r UserUpdateScenario.grs
  • If all goes well, you should see the following similar output 
    • Note: Runtime, Generator, and Receiver versions may have changed since the creation of this article.


> ~/Downloads: genrocket -r UserUpdateScenario.grs 
License validated
GenRocket Runtime Version: 3.5.6
GenRocket Generator Runtime Version: 3.5.6.7
GenRocket Receiver Runtime Version: 3.5.6.2
Validating the Scenario Checksum for Integrity.
Starting Engine...
Running Scenario UserUpdateScenario...
Update Count = 10000...
Update Count = 20000...
Update Count = 30000...
Update Count = 40000...
Update Count = 50000...
All done! Time elapsed: 0m:20s


  • You should have 50000 rows of updated data in your user table with synthetically replaced values for

    • external_id, first_name, last_name, username and ssn


Step 18 - Create the GrantHistoryUpdateScenario

For the most part, you'll be repeating steps 1 through 17 with the exception of a few details defined below

  • Modify the following GrantHistory Attributes
    • GrantHistory.granterId references Master.externalId

    • GrantHistory.firstName references Master.firstName

    • GrantHistory.lastName references Master.lastName
    • GrantHistory.username references Master.username
    • GrantHistory.master references Master.query

    • GrantHistory.creditCard replaces RandomCreditCardGen with CreditCardGen

      • Set the masterCard parameter to True

      • Click the Save Generator button

  • Go to the Master Domain's query Attribute and change the query parameter to the following
    • select granter_id from beta.grant_history order by granter_id
    • click the Save Generator button
  • In the GrantHistory Domain's SQLUpdateReceiver's Data tab, enter the following SQL statement

  • Click the Save button


update beta.grant_history set
  granter_id = 'var1',
  first_name = 'var2',
  last_name = 'var3',
  username = 'var4',
  credit_card = 'var5'
where granter_id = 'var6'


  • Click the Attribute tab and make the following modifications
    • set creditCard to var5
    • set firstName to var2
    • set granterId to var1
    • set lastName to var3
    • set master to var6
    • set username to var4


Step 19 - Create the NotificationSettingUpdateScenario

For the most part, you'll be repeating steps 1 through 17 with the exception of a few details defined below

  • Modify the following NotifiationSetting Attributes
    • NotifiationSetting.userExternalId references Master.externalId

    • NotifiationSetting.username references Master.username
    • NotifiationSetting.master references Master.query

  • Go to the Master Domain's query Attribute and change the query parameter to the following
    • select user_external_id from beta.notification_setting order by user_external_id
    • click the Save Generator button
  • In the NotifiationSetting Domain's SQLUpdateReceiver's Data tab, enter the following SQL statement

  • Click the Save button


update beta.notification_setting set
  user_external_id = 'var1',
  username = 'var2'
where user_external_id = 'var3'


  • Click the Attribute tab and make the following modifications
    • set user_external_id to var1
    • set username to var2
    • set master to var3


Move on to the next lesson >