Transformation Guide

Back Next

Masking Data with an Expression Transformation

Use the Expression transformation with the Data Masking transformation to maintain a relationship between two columns after you mask one of the columns.

For example, when you mask account information that contains start and end dates for insurance policies, you want to maintain the policy length in each masked record. Use a Data Masking transformation to mask all data except the end date. Use an Expression transformation to calculate the policy length and add the policy length to the masked start date.

This example includes the following types of masking:

Key

Date blurring

Number blurring

Mask formatting

This example is the M_CUSTOMER_ACCOUNTS_MASKING.xml mapping that you can import to your repository from the

client\samples

folder.

A customer database table called Customers_Prod contains sensitive data. You mask the data in each column and write the test data to a target table called Customers_Test.

Mask the following Customer_Accounts_Prod columns:

Column	Datatype
AcctID	String
CustID	Integer
Balance	Double
StartDate	Datetime
EndDate	Datetime

The following figure shows the mapping that you can import:

The mapping has following transformations along with a source and target:

Source Qualifier.
Passes the AcctID, CustID, Balance, and Start_Date to the Data Masking transformation. It passes Start_Date and End_Date columns to an Expression transformation.

Data Masking transformation
. Masks all the columns except End_Date. The Data Masking transformation passes the masked columns to the target. It passes the policy start date, end date, and the masked start date to the Expression transformation.

The Data Masking transformation masks the following columns
:

Input Port	Masking Type	Masking Rules	Description	Output Destination
AcctID	Random	Mask format AA+DDDDD Result String Replacement Characters ABCDEFGHIJKLMNOPQRSTUVWXYZ	The first two characters are uppercase alphabetic characters. The third character is a dash and is not masked. The last five characters are numbers.	Customer_Account_Test target
CustID	Key	Seed = 934	The seed is 934. The CustID mask is deterministic.	Customer_Account_Test target
Balance	Random	Blurring Percent Low bound = 10 High bound = 10	The masked balance is within ten percent of the source balance.	Customer_Account_Test target
Start_Date	Random	Blurring Unit = Year Low Bound = 2 High Bound = 2	The masked start_date is within two years of the source date.	Customer_Account_Test target Exp_MaskEndDatetransformation

Expression transformation.
Calculates the masked end date. It calculates the time between the start and end dates. It adds the time to the masked start date to determine the masked end date.

The expressions to generate the masked end date are:

DIFF = DATE_DIFF(END_DATE,START_DATE,'DD')
out_END_DATE = ADD_TO_DATE(out_START_DATE,'DD',DIFF)

Rename Saved Search

Table of Contents

Transformation Guide

Transformation Guide

Masking Data with an Expression Transformation

Masking Data with an Expression Transformation