Data Masker

Table-to-Table Synchronization Rules

Use Table-To-Table Synchronization Rules when you have denormalized data in multiple tables and you want to ensure that the data is consistent across each location. Typically, Table-To-Table Synchronization Rules are run after the source table has been anonymized using other rules.

Example

An e-commerce website has a Customers table that has a lot of information about the customer:

IdFirstNameLastNameEmailAddressStreetAddressZipCode...
2787MelissaWoodsmwoods109@gmail.com45 Sherwood AvenueW10 5BF...
7898JohnMillerjohn@miller.com32 Church St.ST2 7NH...


They also have an Orders table where, for audit reasons, it was decided to have the copy of the customer's details at the time of the order creation:

IdCustomerIdFirstNameLastNameEmailAddressStreetAddressZipCode...
102982787MelissaWoodsmwoods109@gmail.com45 Sherwood AvenueW10 5BF...
103652787MelissaWoodsmwoods109@gmail.com45 Sherwood AvenueW10 5BF...
107687898JohnMillerjohn@miller.com32 Church St.ST2 7NH...
119637898JohnMillerjohn@miller.com32 Church St.ST2 7NH...
157772787MelissaWoodsmwoods109@gmail.com45 Sherwood AvenueW10 5BF...


After applying the rules to correctly mask the Customers table they create a Table-To-Table Synchronization Rule:

  • The source table: Customers
  • The target table: Orders
  • Setting the columns to synchronize: FirstName, Surname, EmailAddress, StreetAddress, ZipCode
  • Setting the Join Columns to be:
    • Source: Id
    • Target: CustomerId

After running the masking set the result should be two tables that have been correctly synchronized:

Customers

IdFirstNameLastNameEmailAddressStreetAddressZipCode...
2787DonnaBanksdonna.banks@test.com44 Belvoir Rd.UB5 6 UA...
7898SimonWalshsimon.walsh@test.com1 Orchard StreetPO1 3RJ...


Orders

IdCustomerIdFirstNameLastNameEmailAddressStreetAddressZipCode...
102982787DonnaBanksdonna.banks@test.com44 Belvoir Rd.UB5 6 UA...
103652787DonnaBanksdonna.banks@test.com44 Belvoir Rd.UB5 6 UA...
107687898SimonWalshsimon.walsh@test.com1 Orchard StreetPO1 3RJ...
119637898SimonWalshsimon.walsh@test.com1 Orchard StreetPO1 3RJ...
157772787DonnaBanksdonna.banks@test.com44 Belvoir Rd.UB5 6 UA...


Core concepts

Columns to synchronize

The list of columns to synchronize maps the columns in the source table to the appropriate columns in the target table. The target columns should have an appropriate type.

To allow the same source column to populate multiple target columns enable the Permit Duplicate Source Sync. Columns on the Options tab. 

Join condition

The join condition specifies how Data Masker will identify the rows in the target table that match a row in the source table; this is achieved by mapping the columns in the source table to the columns in the target table.

The Table-To-Table Synchronization Rule will be most effective when the join columns are represented by a foreign key relationship between the tables, however, there is no requirement for such a relationship to exist. 

If the join columns are not linked via a checked foreign key constraint it is possible that some rows exist in the target table where there are no corresponding records in the source table. These columns would not be masked via a Table-To-Table Synchronization Rule and would have to be masked independently (e.g. with a Substitution Rule), alternatively see the Synchronizing across multiple tables.

Adding a WHERE clause

If only certain rows in the table need masking specify a where on the Where clause or Sampling options tab of the Table-To-Table Synchronization Rule form).

For Table-To-Table Synchronization Rules, this where clause can be used to restrict the rows based on either the source or target table. The source table can be referenced in the where clause as DMSRC, and the target table can be referenced as DMTGT.

Fast Single Pass

The Fast Single Pass option on the Options tab can be used to improve the performance of your Table-To-Table Synchronization Rule. It does this by creating a single query to update the entire table, rather than taking a row-by-row approach. Note that the Where Clause cannot be used if the Fast Single Pass option is set.

Force ISNULLs Off

This option builds an SQL Statement which does not use ISNULLS on the Join condition. Builds an SQL Statement which does not use ISNULLS on the join condition. In SQL Server a NULL value does not equal NULL and ISNULLs are used to enforce the comparison so that join conditions containing NULL values will be correctly synchronized. If you are sure the Join condition in the Table-To-Table rule will never contain a NULL value you can check this option and ISNULLs will not be used. This option can speed up some rule processing.

Advanced concepts

Synchronizing across multiple tables

If you have multiple tables that contain the same information, which you want to keep consistent, consider using a Sync. Manager Rule, which allows you to identify the links between the tables and more efficiently mask the data consistently.

Sync. Manager Rules are also useful in the case where the join conditions specified would result in some rows in the target table not being masked as they operate on the union of the denormalized data across all tables. 

Where clauses referencing other tables

The custom where clause allows you to specify a JOIN clause (the target table will be aliased as DM1 to help create the join condition).



Didn't find what you were looking for?