FDM: Achieving Masking Consistency between INT and VARCHAR Data Types

Products

• CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

When performing data masking on columns with different data types (e.g., date vs varchar for Date of Birth, or int vs varchar for Phone Numbers), the masking results may be inconsistent or fail for the VARCHAR fields. This often happens because VARCHAR fields contain formatting characters (spaces, hyphens, + signs) that are absent in the numeric or date-typed columns.

PII field	Data type	Sample data	Making function used	Masking result
Date of birth	date	DD-MM-YYYY	HASHDOB	Masked
Date of birth	int	YYYYMMDD	HASHDOB	Masked
Date of birth	varchar	DD-MMM-YY	HASHDOB	Not masked
Phone num	int	123456789	HASHPHONE4	Masked
Phone num	varchar	[phone]	HASHPHONE4	Not masked

Environment

FDM 4.11 and above

TDM portal 4.11 and above

Cause

Fast Data Masker (FDM) treats all data in flat files or VARCHAR columns as string data. If a masking function is applied without a defined format or pre-cleaning, FDM cannot parse the string as a valid date or numeric value, leading to inconsistent hash results or masking failure.

Resolution

To ensure consistency, you must standardize the input string so that the masking function receives the same raw value for both data types.

Step 1: Standardize VARCHAR Input

Use a SQL function or pre-masking script to remove formatting characters. For phone numbers, remove spaces, hyphens, and country codes that aren't present in the integer column.

Example SQL Function for Phone Numbers: SUBSTRING(REPLACE(REPLACE(Phone_Column, ' ', ''), '-', ''), 1, 13)

Step 2: Configure Date Formats

In your FDM definition file, you must explicitly define the date format for any date-related VARCHAR fields. If the format is missing, FDM treats the field as a plain string.

Example Definition File Entry: HEADER=1,TRAILER=0,DELIM=,,DATEQUOTED=,CHARQUOTED=,NUMQUOTED= BIRTH_DATE_COLUMN,"dd-MMM-yy"

Step 3: Use Consistent Functions

Apply the FORMATFPE1EXT or HASHDOB function consistently across both columns once the input formats match. Ensure that the 'ignore' parameters (e.g., ignoring the first character if it's a leading zero) are set identically.

Verification

1. Run a test masking job on a small subset of data.
2. Review the Audit Log.
3. Compare the NEWVALUE for both the INT and VARCHAR columns to confirm they match exactly.