Value-Preserving Masking

Overview

This guide describes how to create a mapping table for value-preserving masking in XDM.

The goal is to shuffle values, so that sensitive data is masked while valid values from the original dataset are preserved.

Identical source values are mapped to identical target values. This ensures consistent masking results when the same value appears multiple times or is used across different tables.

Prerequisites

This guide uses the employees table from the tutorial’s sample environment.

Before using this approach, ensure that the following prerequisites are met:

A connection to the database where the source table is located. In this example, a connection to the sample environment is required.
Read access to the source table.
A target mapping table where the generated key/value pairs can be stored.
Write access to the target mapping table.

The target can be an empty H2 mapping table in XDM. It does not need to be located in a Mapping Table Container.

It is also possible to use an existing database table or an existing mapping table as the basis for shuffling.

Steps

All steps described below must be executed in one hook.

Step 1: Prepare the Workspace

Import the required package and initialize the helper classes.

import de.ubs.xdm3.batch.modification.MappingTableUtils

def utilPOOL = new MappingTableUtils(myConnection)
def poolTable = 'TABLE_2'

In this example:

myConnection is the connection to the target mapping table.
TABLE_2 is the target mapping table where the shuffled key/value pairs are stored.

Step 2: Read All Relevant Values from the Source Table

Retrieve the values that should be shuffled.

In this example, the distinct employee last names are read from the production.employees table.

def stmt = myConnection.createStatement()
def rs = stmt.executeQuery("SELECT DISTINCT last_name FROM production.employees")
def valueList = []

while (rs.next()) {
    valueList << rs.getString(1)
}

rs.close()
stmt.close()

The DISTINCT clause ensures that each last name is included only once. This creates one unique assignment per name.

If the frequency of values should be taken into account, extract the list of IDs in addition to the list of names.

Example:

def stmt = myConnection.createStatement()

def rs1 = stmt.executeQuery("SELECT emp_no FROM production.employees")
def valueList1 = []

while (rs1.next()) {
    valueList1 << rs1.getString(1)
}
rs1.close()

def rs2 = stmt.executeQuery("SELECT last_name FROM production.employees")
def valueList2 = []

while (rs2.next()) {
    valueList2 << rs2.getString(1)
}
rs2.close()

stmt.close()

This process keeps the full list in memory. Therefore, it has limitations depending on the available memory. It is not suitable for very large tables, for example tables with more than 100 million entries.

Step 3: Create and Shuffle Two Lists

Create two separate copies of the value list and shuffle both.

def list1 = new ArrayList(valueList)
def list2 = new ArrayList(valueList)

Collections.shuffle(list1)
Collections.shuffle(list2)

This creates a random but reproducible mapping table after the values have been stored.

If two lists are selected from the database, use:

valueList1 for list1
valueList2 for list2

Example:

def list1 = new ArrayList(valueList1)
def list2 = new ArrayList(valueList2)

Collections.shuffle(list1)
Collections.shuffle(list2)

Step 4: Generate and Populate the Mapping Table

Pair the entries from both lists and store the key/value pairs in the mapping table.

def n = Math.min(list1.size(), list2.size())

for (int i = 0; i < n; i++) {
    def key = [list1[i]] as String[]
    def data = [list2[i]] as String[]

    utilPOOL.merge(poolTable, key, data)

    println "Pair: ${key[0]} -> ${data[0]}"
}

utilPOOL.close()

The generated mapping table can then be used in a Modification Method to replace original values with their mapped target values.

Benefits

This approach provides the following benefits:

Consistent mapping: Identical source values are always mapped to the same target value.
Realistic test data: Masked values still look valid because they come from the original data domain.
Automation: The mapping table can be generated automatically and reused in masking processes.

Conclusion

Value-preserving masking allows sensitive data to be shuffled while keeping realistic values.

By creating a mapping table, XDM can consistently replace original values with valid alternative values.