To mask a column with a different dataset, add the column to your options file and specify a "dataset" on the column.

{
  "tables": [
    {
      "schema": "Person",
      "name": "Address",
      "columns": [
        {
          "name": "CountyOrState",
          "dataset": "USStates"
        }
      ]
    }
  ]
}

Defining a custom dataset for masking

To define a custom dataset for your column, you can choose between several types of dataset:

Pattern
List
File
Binary file

Note: The "name" property of the dataset in your options file can be one of the built-in datasets (overriding that dataset), or a distinct name of your choice (creating an additional dataset). Any column in your masking file that is assigned your named dataset will be masked with the values you define.

Pattern

A pattern dataset defines multiple patterns that can be used to generate values.

{
  "datasets": [
    {
      "name": "MiddleInitials",
      "type": "Pattern",
      "values": ["?.", "?. ?."]
    }
  ]
}

Specifying a pattern using the following rules can be done to generate alphanumeric strings:

# representing integers (0-9)
? representing characters (A-Z)
* representing either integers or characters
\ to escape pattern characters
any symbol within square brackets ([...]) to limit to one of the specified characters

Example: US phone numbers

{
  "datasets": [
    {
      "name": "PhoneNumbers",
      "type": "Pattern",
      "values": [
        "(###) ###-####",
        "(###) ###-#### x####",
        "1-###-###-####",
        "###-###-####",
        "[23456789]## ###-####"
      ]
    }
  ]
}

In this above example, the built-in PhoneNumbers dataset is being overridden. Any column assigned the PhoneNumbers dataset will be masked with values that match one of the patterns specified in the values array. For all the values to have the same format, specify only a single pattern.

List

A list dataset defines a set of values that can be used when masking.

{
  "datasets": [
    {
      "name": "ShortFirstNames",
      "type": "List",
      "values": [ "Ann", "Bob", "Carl", "Dave"]
    }
  ]
}

Example: Masking JSON column data

When a column stores JSON data as text (such as NVARCHAR or VARCHAR), you can replace it with predefined JSON templates using a List dataset.

Note: This approach works for JSON stored in text-based columns. Native JSON column types such as JSON and JSONB in PostgreSQL are not currently supported.

{
    "datasets": [
        {
            "name": "ProductAttributes",
            "type": "List",
            "values": [
                "{\"brand\": \"Acme Corp\", \"cpu\": \"i5\", \"ram\": 8, \"storage\": {\"type\": \"SSD\", \"size\": 256}}",
                "{\"brand\": \"Globex Corporation\", \"cpu\": \"i7\", \"ram\": 16, \"storage\": {\"type\": \"SSD\", \"size\": 512}}",
                "{\"brand\": \"Stark Industries\", \"cpu\": \"Ryzen 9\", \"ram\": 64, \"storage\": {\"type\": \"NVMe\", \"size\": 2000}}"
            ]
        }
    ]
}

Each JSON value must be written as an escaped string — use \" for internal double quotes. Define multiple templates to create varied, realistic test data.

File-based

A file dataset defines a set of values in an associated file that can be used when masking. Each value should be on a separate new line. This file must be in the same directory as the options file.

{
  "datasets": [
    {
      "name": "NorthWestCities",
      "type": "File",
      "file": "NorthWestCities.txt"
    }
  ]
}

NorthWestCities.txt

Bellevue
Kennewick
Pasco
Portland
Seattle
Spokane
Tacoma
Yakima

Binary file-based

A binary file dataset uses an associated file containing a list of encoded binary files, separated by new lines. This file must be in the same directory as the options file.

{
  "datasets": [
    {
      "name": "SampleDocuments",
      "type": "BinaryFile", 
      "file": "SampleDocuments.txt"
    }
  ]
}

SampleDocuments.txt

iVBORw0KGgoAAAANSUhEUgAAAD4AAAATCAYAAADI876sAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAAFiUAABYlAUlSJPAAAAGHaVRYdFhNTDpjb20uYWRvYmUueG1wAAAAAAA8P3hwYWNrZXQgYmVnaW49J++7vycgaWQ9J1c1TTBNcENlaGlIenJlU3pOVGN6a2M5ZCc/Pg0KPHg6eG1wbWV0YSB4bWxuczp4PSJhZG9iZTpuczptZXRhLyI+PHJkZjpSREYgeG1sbnM6cmRmPSJodHRwOi8vd3d3LnczLm9yZy8xOTk5LzAyLzIyLXJkZi1zeW50YXgtbnMjIj48cmRmOkRlc2NyaXB0aW9uIHJkZjphYm91dD0idXVpZDpmYWY1YmRkNS1iYTNkLTExZGEtYWQzMS1kMzNkNzUxODJmMWIiIHhtbG5zOnRpZmY9Imh0dHA6Ly9ucy5hZG9iZS5jb20vdGlmZi8xLjAvIj48dGlmZjpPcmllbnRhdGlvbj4xPC90aWZmOk9yaWVudGF0aW9uPjwvcmRmOkRlc2NyaXB0aW9uPjwvcmRmOlJERj48L3g6eG1wbWV0YT4NCjw/eHBhY2tldCBlbmQ9J3cnPz4slJgLAAAGi0lEQVRYR+WXW0hU3x7HP3NxxtQxNK+hGGoS46BZeKGMgki0ol4C09BupBSGCCJaD4EQJVQQXe3Bh6AeBiIFKS0LQ7tIVtM4lom3zNug4ugYouP4Ow/pnDNh51+cP+d06AObtdfav73W+q3fd63f3goREf5AlN83/Cn8sY4rlpP6s2fPqK2tRa/Xs2/fPnQ63fcmvz12u53m5maamppYuXIlBw8eJCQk5J8Gsgzl5eWiVColPT1dvnz58v3j3xqHwyEWi0WKiookNDRUVCqVrF27Vsxms5vdslJ3Op0sLCy4yv8nBgYGKCkpoba2luDgYJxOJyLC98JWu9X+DW/fvqWnp4fExEQALBYLc3NzREVFERsby+TkJCaTCZvNRmhoKAaDwbVFnE4n/f39dHV1YbPZUCgUhIeHExsbi4+Pj2uMyclJzGYzQ0NDrjYAf39/UlJS0Ol02O12Pn78yMDAAAsLC0RGRqLX6/H09IRvCsZgMHDy5EkaGxsxmUxufblwi/8iZ86cEUB27Nghnz9/FhGR/Px8UalUkpubK2lpaeLr6ytarVaSkpKksrJSjh8/LqGhoeLh4SGRkZFy4cIFcTgcIiLy8OFD2b59uwQGBopSqXTJr6ysTKxWq4iI9Pf3S2FhoURERAjgdhkMBmlvb5fh4WE5deqUxMTEiIeHh6hUKjEYDHLu3DkZGxsTEZH5+XmZnZ118yM6Olrev3/v8k9+JPXlmJ+fx+l0cv/+fUZGRti6dSsBAQG0trZSUlJCfX09KSkpxMbG0tPTw507d+jq6oJFtfT19ZGUlMT+/ftJS0vDarVy+fJlXr9+DYDRaOTmzZusWLGCrKwsUlNTUalUBAQEkJGRgUajobKykqtXrzI1NUVGRgZ79uzBarVy9uxZHjx4AIBKpUKj0bjNfVnclmGR5SJ+9OhRASQ5OVlaWlpkYmJCysrKRKvVik6nk2vXrsn09LRUV1eLTqeTkJAQqa6uFhERs9ks9fX10tHRIX19fdLa2ipxcXECyI0bN9z6P3/+vMzNzcnjx48lKChINm7cKENDQ/LhwweJj48Xb29vKS0tlf7+fhkcHJS8vDwBJCcnx80H+YuI//QeX0Kv15OYmIhCoWDdunV4eXnh5+fHpk2b8Pb2Zs2aNQQGBmKz2bDb7QD4+flRU1PDxYsX6e3tZW5uDqvVCsDExAQAq1atAqCpqQl/f3/MZjNTU1NotVo0Gg2jo6O0t7ej1WoxmUyUl5cD0NHRAcDg4ODiDH+On5b6EkqlEoVC4XavVCpRKr91tVRfYnx8nIqKCioqKhgdHWXbtm3s3LkTf39/AFfW0Ov1qNVqGhoayM/Pp7KyEl9fX/bu3etaFIDZ2VnMZjONjY00NjYyMjJCdHQ0MTExLpuf4Zcj/qsMDw/T0NCAQqEgKyuLQ4cO0dPTQ1NTk9vp/fTpU5xOJ9nZ2fj4+KDT6diyZQubN2+GRdVERkZitVo5cuQIBw4cwNfXl/Hxcdra2oiMjPyXUf+aX474r+Lt7U1wcDAzMzPcvn2bvLw8Tpw4QXd3t5ud3W5HROju7mZsbIyBgQGePHlCXV0dNpuN8PBwdu/ejcPhoKqqiuLiYoqKiiguLub06dO8evUKgKGhIUpLS8nMzOTevXsAjIyMUFJSQmZmJu/evYMfOe7p6YlarUaj0aBWfxPFUp5cKlk8QZVKJVqt1mWnVCpRqVSoVCo8PT0JCwujoKCAhIQEurq6qKurQ6VSERcXB4CXlxcA8fHxADx//hyj0cjdu3e5cuUKBQUFXL9+HY1GQ0FBAceOHcPb25tHjx5hNBoxmUxERESg1+sBmJqaoq6uDqPRiMViAWB6epr6+nqMRiPDw8Pwo2/17u5uLBYLISEhrF+/Hq1Wi8ViobOzk5iYGAwGAyweKCaTCS8vLxITE/Hx8cFut9PS0oLD4SAhIYGQkBCcTicdHR309vYCEBUVxcLCAp8+fWLDhg0AHD58mJcvX1JYWEhycjKTk5NUVVXR3NzMrl27uHXrFqtXr8Zms9HZ2YnVasXhcODn58eaNWsICwvDw8ODr1+/8ubNG8bGxtx8WiI1NZWgoKDlHf9v8+LFC3JzcxkaGiI7O5vY2FhsNhs1NTW0tbWRk5PDpUuXXAfi34JbcvsfYbVaJScnR4KCgsTDw0MAUavVEhQUJOnp6dLc3CxOp/P71/4jfouIA8zMzDA7O+v6qQBQq9VotVo8PT1dKfTv4h/kgxUzDOZjwgAAAABJRU5ErkJggg==
iVBORw0KGgoAAAANSUhEUgAAAD4AAAATCAYAAADI876sAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAAFiUAABYlAUlSJPAAAAGHaVRYdFhNTDpjb20uYWRvYmUueG1wAAAAAAA8P3hwYWNrZXQgYmVnaW49J++7vycgaWQ9J1c1TTBNcENlaGlIenJlU3pOVGN6a2M5ZCc/Pg0KPHg6eG1wbWV0YSB4bWxuczp4PSJhZG9iZTpuczptZXRhLyI+PHJkZjpSREYgeG1sbnM6cmRmPSJodHRwOi8vd3d3LnczLm9yZy8xOTk5LzAyLzIyLXJkZi1zeW50YXgtbnMjIj48cmRmOkRlc2NyaXB0aW9uIHJkZjphYm91dD0idXVpZDpmYWY1YmRkNS1iYTNkLTExZGEtYWQzMS1kMzNkNzUxODJmMWIiIHhtbG5zOnRpZmY9Imh0dHA6Ly9ucy5hZG9iZS5jb20vdGlmZi8xLjAvIj48dGlmZjpPcmllbnRhdGlvbj4xPC90aWZmOk9yaWVudGF0aW9uPjwvcmRmOkRlc2NyaXB0aW9uPjwvcmRmOlJERj48L3g6eG1wbWV0YT4NCjw/eHBhY2tldCBlbmQ9J3cnPz4slJgLAAAGl0lEQVRYR+2Xe0xU2R3HP8wdZobhYXiJdrEQRKqALxLwhaupAYNva7sRjIiaSDQERAmNj2SMRi1GaTTEiEaiY222GJeQUgQEQ0RW8UF5iAoFROUxU8YCA8Ud7wynf6ATh5BWt5vGzfaTnJx7z/nd3/l9z++cnHuc0BsEP0EUYxt+Kvxf+IdU/vwZGQ0nybMWY16nHdv9o8C8TkuxTy37np7md92X6Ilx3NHjCr99+zbZ2dnk5+czMDAwtvuzRo735nGECZ1Ox/bt2zlx4gQXL16kt7fXwW5c4TabjZGREXv9Y6Kzs5PMzEyKiorw8/PDZrMhhECIj8j4eDwK6+KaSxUdX35Hx5ffUeT1kG/cvqV+thFrgg+vVzpT8UUT112r+Ta43WGLWBN8aI8epmxSA/ma21xzqeJeSAeD610dxuhfo+F2YAtfqyodStmkBrs/8zotNb94wXXXaq65VPEorIs3v5lg9yGEIDw8nDNnzrB8+fIPvI9FbxBji06nE4CIiYkRL168EOgNIjk5WUiSJBITE0VsbKzw8PAQarVaREVFidzcXLFz504xefJk4ezsLIKCgsTJkyeFLMsCvUHcuHFDLFu2TPj6+gqFQiEkSRLTpk0T+/btE0ajUaA3iJcvX4q0tDQREBAgAIcSHh4umpqaRE9Pj9i/f78ICQkRzs7OQpIkER4eLo4fPy5MJpNAbxBWq1VYLBYHHcHBwaK+vt5B40dn3Gq1YrPZKCgowGAwsGTJEnx8fHj48CGZmZmUlpYyf/58wsLCaG9v5+rVq7S2tgJQW1tLR0cHUVFRbNy4kdjYWIxGI6dPn+bBgwcA5Ofnc+7cOVxcXIiPjyc6OhpJkvDx8SEuLg6VSkVubi45OTmYzWbi4uJYs2YNRqORo0ePUlxcDIDyjybUf+pziH08Plr4e0JDQ7lw4QJ6vZ7ExEScnZ0ZGRlh7969XLlyhUOHDuHu7k5PTw/Nzc0ArF69mrNnz3Lq1CmOHTvGkSNHCAwMZHh4mFevXgHw9OlTLBYLSUlJXL58GZ1Oh7e3NwEBAaSnpyPLMgUFBdhsNpKSksjJySEnJ4f169czNDTEzZs3x0T67/lewiMjI/H8s4Xp06ej1Wrx9fVl4cKFuH0zRGBgIL6+vrx9+5bBwUEAPD09uX//PqmpqcTExLBhwwZaWloA6OsbzY63tzcAVVVVXLp0icLCQsxmM2q1GpVKRW9vL01NTQDU1dVx+PBhdDodT548AaCrq+tdhB/HJwtXKBQo/vB3+7OTk9Nom2LU1fv395hWKMnKyiIrK4ve3l6WLl3KihUr8PLyArCfGqGhoSiVSsrLy0lOTiY3NxcPDw/Wrl1rnxQAi8VCQ0MDlZWVVFZWYjAYCA4OJiQkxG7zMSjHNvzQ9PT0UF5ejpOTE/Hx8SQlJdHe3k5VVRXd3d12u1u3bmGz2UhISMDNzQ13d3cWL17MokWL4N2qCQoKwmg0sm3bNjZt2oSHhwevX7+msbGRoKAgzv3tg4H/A5+c8U/F1dUVPz8/3rx5g16vZ8eOHezatYu2tjYHu8HBQYQQtLW1YTKZ6OzspKKigpKSEvr7+5kyZQqrVq1ClmXy8vLIyMggPT2djIwMDhw4wL179wDoWmbjt02/56uiVK5fvw6AwWAgMzOTr4pSqQ0fnexxM67RaFAqlahUKpTKURONRuNQA0iShEKhQK1W2+0UCgWSJCFJEhqNBn9/f1JSUhgeHqaxsZHW1lZmzpzJrFmzqKmpQasdPZ9nz55NQUEB1dXV8G7LSJLEhAkT2LNnD7t37yYlJQWbzUZxcTFlZWXIsszEiROZMWMGoaGhYASz2UxJSQn19fX2OIeGhigtLQVgy5YtwM9wGu9a2rpwiMePHzNp0iTmzJmDJr+fxrm9tLS0EBISwsy/+gLQ+UsrdXV1aLVaIiMjcS/45+gPRk0Nsiwzd+5cJt90wprgw7Nnz3j+/DkAU6dOZWRkhObmZiIiIgDYunUrd+/eJS0tjXnz5jEwMEBeXh537txh5cqVnD9/ni8qJPpWq2lpacFoNCLLMp6engQGBuLv74/q638w9Cs3Hj16hMlkctD0nujoaPxKxfjC/9dUT20jMTGR7u5uEhISCAsLo7+/n8LCQhobG9m8eTPZ2dl4/0Ue++n35rMQblzuREZGBqWlpfT19SHLMkqlEi8vLyIiIjh48CALFixAuup40fhv+CyEAwz/2gOLxWK/VAAolUrUajUajcZ+hP5Q/AuaFO1JZkxi3AAAAABJRU5ErkJggg==

The associated file (SampleDocuments.txt in this example) is a text file containing a file per line. Each line should be a base-64 encoded version of the binary data of the file.

Preserving null data values

By default, null data values will be preserved when masking. To turn this behaviour off, add "preserveNull": false to the corresponding JSON node.

{
  "tables": [
    {
      "schema": "Person",
      "name": "PersonPhone",
      "preserveNulls": false
    }
  ]
}

{
  "tables": [
    {
      "schema": "Person",
      "name": "PersonPhone",
      "columns": [
        {
          "name": "PhoneNumber",
          "preserveNulls": false
        }
      ]
    }
  ]
}

Redgate Test Data Manager

Using different or custom datasets