Table-to-Table Synchronization Rules
Published 06 January 2020
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.
An e-commerce website has a
Customers table that has a lot of information about the customer:
|2787||Melissa||Woodsfirstname.lastname@example.org||45 Sherwood Avenue||W10 5BF||...|
|7898||John||Milleremail@example.com||32 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:
|10298||2787||Melissa||Woodsfirstname.lastname@example.org||45 Sherwood Avenue||W10 5BF||...|
|10365||2787||Melissa||Woodsemail@example.com||45 Sherwood Avenue||W10 5BF||...|
|10768||7898||John||Millerfirstname.lastname@example.org||32 Church St.||ST2 7NH||...|
|11963||7898||John||Milleremail@example.com||32 Church St.||ST2 7NH||...|
|15777||2787||Melissa||Woodsfirstname.lastname@example.org||45 Sherwood Avenue||W10 5BF||...|
After applying the rules to correctly mask the
Customers table they create a Table-To-Table Synchronization Rule:
- The source table:
- The target table:
- Setting the columns to synchronize:
FirstName, Surname, EmailAddress, StreetAddress, ZipCode
- Setting the Join Columns to be:
After running the masking set the result should be two tables that have been correctly synchronized:
|2787||Donna||Banksemail@example.com||44 Belvoir Rd.||UB5 6 UA||...|
|7898||Simon||Walshfirstname.lastname@example.org||1 Orchard Street||PO1 3RJ||...|
|10298||2787||Donna||Banksemail@example.com||44 Belvoir Rd.||UB5 6 UA||...|
|10365||2787||Donna||Banksfirstname.lastname@example.org||44 Belvoir Rd.||UB5 6 UA||...|
|10768||7898||Simon||Walshemail@example.com||1 Orchard Street||PO1 3RJ||...|
|11963||7898||Simon||Walshfirstname.lastname@example.org||1 Orchard Street||PO1 3RJ||...|
|15777||2787||Donna||Banksemail@example.com||44 Belvoir Rd.||UB5 6 UA||...|
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.
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
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.
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).