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:

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:

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

Orders

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. 

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

Using this option, target table rows where the join column value is null will not be masked. 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).