There are sometimes cases where the data type of a column does not match that supported by a dataset. This means that the dataset cannot be applied to the column.

For example, the USSocialSecurityNumbers dataset generates US Social Security numbers in the format 989-11-9389. If the database column used to store this data is defined as a number, it cannot then store the data generated by the dataset when masking takes place.

To get around this problem, pre-masking and post-masking transforms can be used to manipulate the data into different formats.

Pre-masking transforms

Pre-masking transforms run after the data is loaded from the database, but before it is masked. This enables the original data to be transformed into a different form/type suitable for the applied dataset.

Post-masking transforms

Post-masking transforms run after the data is masked, but before it is written back to the database. This enables the masked data to be transformed into a different form/type suitable for the column being masked.

Configuring transforms

On a column

Pre-masking and post-masking transforms can be configured on a column in the masking.json file. Both are optional, allowing a pre-masking transform or post-masking transform to be defined on their own, or both can be defined.

{
  "tables": [
    {
      "schema": "Person",
      "name": "Address",
      "columns": [
        {
          "name": "SSN",
          "dataset": "USSocialSecurityNumbers",
          "transforms": {
            "pre": "...",
            "post": "...",
          }
        }
      ]
    }
  ]
}

Transforms take the form of an expression that is used to manipulate the data, similar to how it is done within a cell in Microsoft Excel.

The special placeholder ${value} is used to represent the input to the transform. For pre-masking transforms, this represents the value from the database column. For post-masking transforms, this represents the output of the dataset after masking.

Using a transform rule

Transform rules can also be defined in the masking.json file or the options.json file. These apply a similar syntax to classification rules, and allow conditions to be applied to determine whether to apply the transform to a given column.

{
  "transformRules": [
    {
      "condition": "Table.Name = 'Customers' AND Column.Name = 'PostalCode'",
      "pre": "removeWhitespace(${value})",
      "post": "upper(${value})"
    }
  ]
}

Transform rules will apply the specified pre-masking and post-masking transforms to any columns that match the specified condition.

Both pre-masking and post-masking transforms are again optional, allowing a pre-masking transform or post-masking transform to be defined on their own, or both can be defined.

In the above example, the transforms will be applied to any columns called "PostalCode" in any table called "Customers".

In addition to the conditions used by classification rules, transform rules can also be about:

• a column's classification - Column.Classification = 'GivenNames' (only relevant when running the map step)
• a column's dataset - Column.Dataset = 'GivenNames' (only relevant when running the mask step)

Data types

Expressions are type-safe, meaning that different data types cannot be interchanged. The supported data types are:

If you do need to interchange data types, use one of the conversions functions listed below.

Characters are quoted using single quotes. e.g. 'a'

Strings are quoted using double quotes. e.g. "hello"

Supported Functions

String functions

* Whitespace characters include spaces, tabs (\t), new lines (\n) and carriage returns (\r).

Conversion functions

* A format string

Format strings

The format strings passed to toString and toDate determine the format of the converted value. These use the standard format patterns for numbers and dates.

Date functions

Null functions

Syntax errors

When working with transforms, it is possible to get a syntax error. This can be due to a number of reasons:

When using strings in an expression in a masking.json file, it is necessary to escape the double quotes surrounding the string:

{
  "name": "SSN",
  "dataset": "USSocialSecurityNumbers",
  "transforms": {
    "pre": "replace(${value}, \"-\", \"\")"
  }
}

Example expressions