Unique Masks

Unique masks are masks that will ensure every value is replaced with a unique value for that column. For other masks see all mask functions.

From Unique (from_unique)
Generates random but unique strings or numbers, from a format string (databases only)
From Unique Imitate (from_unique_imitate)
Transforms strings or numbers to be random, retaining format and uniqueness

From unique (`from_unique`)

A mask that generates string values that are guaranteed to be unique within the target column.

Note: To automatically cascade primary and unique key values to foreign keys or mask composite keys, consider using mask_unique_key instead.

Parameters

format (required): The format that will be used to generate values. See format string syntax for details on how to construct a format string.

version: '1.0'
tasks:
  - type: mask_table
    table: drivers
    key: id
    rules:
      - column: licence_plate
        masks:
          - type: from_unique
            format: "{[A-Z],3}{[0-9],3}"

Show result

Before

After

licence_plate
ABC123
DEF456
GHI789
JKL246
MNP813
RTU579

licence_plate
ABT675
AAT808
AAB476
AAV509
ABH457
ABO157

Note

All values produced by from_unique will be strings. To convert values to other data types, you can chain your from_unique mask with a typecast mask.
Unlike from_format_string, from_unique ensures generated values are unique. This is achieved using the same underlying unique value generation procedure as mask_unique_key.
The specified format must allow for a sufficient number of unique values to cover the full number of rows in the target table or file (rounded up to the nearest multiple of the batch_size for mask_table tasks), otherwise an error will be reported when executing the run.
Using the from_unique mask type differs from the mask_unique_key task type in the following ways:
- from_unique cannot perform any cascading of values to related columns, such as foreign keys.
- from_unique cannot be used to guarantee joint uniqueness across the columns in a composite key, unless guaranteeing uniqueness within a single column within the composite key is sufficient for the target use case.
- from_unique can be used to update unique columns at the same time as other columns in a mask_table task, without the need for a separate database update performed by a mask_unique_key task.
- from_unique can be used to generate unique values in mask_tabular_file tasks or in mask_table tasks for databases that do not support mask_unique_key (such as Amazon DynamoDB).
Using from_unique in a mask_table task where worker_count > 1 is only supported for Oracle and Amazon DynamoDB connections.
from_unique cannot be used in the rules of a mask_file task.
Like other mask functions, from_unique cannot be used to update the key columns of a mask_table task (except for databases that allow mask_table to update key columns, such as Amazon DynamoDB).
All from_unique masks within a given task will be produced from the same sequence of unique values, such that two from_unique masks with identical format will produce the same value for the same row. This can be useful to ensure values produced by identical from_unique masks in different if branches are jointly unique.

From Unique Imitate (`from_unique_imitate`)

The from_unique_imitate mask is very similar to the imitate mask:

alphabetical characters a-z and A-Z are replaced by other alphabetical characters of the same case
digits are replaced by other digits
all other characters (whitespace, symbols such as %, and so on) are left as-is.

However, there are the following differences:

Most notably, from_unique_imitate can also be used on primary keys and columns that have a UNIQUE constraint. Indeed, it is designed specifically for data that must take unique values per row.
The masked values are guaranteed to be consistent for the same given input, and different for different inputs. (The specific values generated will vary across runs unless you control the seeding.)
- For example, if a column has a UNIQUE constraint and the existing data satisfies that constraint, the resulting masked values will all be unique.
- If a column contains the same value in every row, the content of the column after masking will also have the same value in every row.

The from_unique_imitate mask will always create a different result to its input, as if it has an implicit force_change: true. The mask may occasionally preserve individual letters and digits in the same position, but it is always guaranteed that at least one (and very likely almost all) of the alphanumeric characters will be different. For example, ABC-123 may be masked to BYC-457.

This mask can only be used on columns of integer or string (char / varchar) type.

null values will always be left as null.

Important! from_unique_imitate does not support IDENTITY columns in Oracle or Microsoft SQL Server.

Leading zeroes

All zeroes appearing before any other digit in a value will be preserved. For example, when masking the value ABC-00123-0044, the mask will not change the two zeroes before 123 but can change the two zeroes before 44. A sample result for this value might be ZQD-00157-3498.

Similarly, no masked value will be created that includes a zero as the first digit character. For example, when masking A123-456 the character immediately after the A will never be a zero, though the other digits may be zero. A sample output value might be A439-007. Likewise, when masking the integer value 12345, the result will not be 5892 as if there was a leading zero; it will always be of five digits in length, i.e. be between 10000 and 99999 inclusive.

Outside the rules above, the mask can produce a sequence of digits that are all zero. For example, M109D732 may be masked to A543G000.

You can apply a from_unique_imitate mask to a primary key column or a column that is used as a foreign key in another table. References will be updated automatically. Composite primary keys are supported.

Parameters

skip_letters (optional): A boolean to enable or disable the skipping alphabetical characters from being masked. Defaults to false (alphabetical characters will be replaced).
skip_digits (optional): A boolean to enable or disable the skipping of digits from being masked. Defaults to false (digits will be replaced).
checksum (optional): A string to specify an algorithm to use to generate unique valid replacements. Options: brazilian_cpf, credit_card, or luhn.
on_invalid (optional): A string to specify the action to take if the value fails checksum validation. One of:
- mask (default): Always overwrite without validating the value by checksum. If the input length is valid, the input value will be masked with checksum. Otherwise, the input value will be masked without checksum.
- skip: Skip to the next value, the value remains unchanged.
- error: Raise an error and stop masking.
retain_prefix_length (optional): The number of characters of the input value to retain. See Retaining Prefixes below. By default, no prefix is retained (i.e. the entire input value is masked).

Invalid Parameter Combination:

Due to no masking occurring if both skip_digits and skip_letters are true, this combination is invalid and a run will be prevented if this ruleset is specified.

Values Requiring Checksums

When the output values must satisfy a checksum, specify the name of the checksum as the checksum parameter. Unique values will be generated that satisfy that checksum algorithm. The available options for checksum are:

brazilian_cpf
credit_card
luhn

For each checksum, the input value must contain a certain number of digits 0-9 and no letters A-Z or a-z. Other non-letter characters that are used for formatting are retained in the output. The replacement value will conform to the checksum algorithm, even if the input did not, provided it is of the correct length.

To handle masking of values that may not match the checksum, or that may contain letters, specify the on_invalid parameter. Please refer to Using on_invalid for a detailed explanation of the behavior of each on_invalid option.

`brazilian_cpf`

Use this checksum type to generate values that satisfy the Brazilian CPF (Cadastro de Pessoas Físicas) number checksum.

For valid CPFs to be generated, the input value must contain 11 digits (and may contain spaces or punctuation). For handling of invalid input values (for example, incorrect length, bad checksum or the presence of letters), please refer to Using on_invalid.

The table below shows example input and output data, based on the default parameters.

Input Example	Description	Output Example	Output Description
298.056.372-20	Valid, formatted CPF	886.972.870-65	Valid CPF with formatting retained
2980,5637,220	Valid CPF, with other formatting	8869,7287,065	Valid CPF with formatting retained
29805637220	Valid CPF, digits only	88697287065	Valid CPF, digits only
298.056.372-29	11-digit, formatted number, that is not a CPF	886.972.870-65	Valid CPF with formatting retained
29805637229	11-digit number that is not a CPF	88697287065	Valid CPF, digits only
298056372	9-digit number	–	No output, error is raised and masking stops due to invalid length
298A056B372C20	String with letters	–	No output, error is raised and masking stops due to invalid characters

`credit_card` and `luhn`

The credit_card and luhn checksums both generate values that satisfy the Luhn checksum algorithm. The difference is how they each validate the length of the number:

luhn may be applied to any number containing two or more digits.
credit_card is only valid for numbers of length 12 to 19, inclusive.

For values of length 12-19 characters, the behaviour of both checksums is identical.

If you are masking only credit cards, then credit_card should be preferred, as it will also validate the length of existing values. luhn should be used when generating values of other lengths, for example, mobile phone IMEI numbers.

For handling of invalid input values (for example, incorrect length, bad checksum or the presence or letters), please refer to Using on_invalid.

Input Example	Description	Output Example	Output Description
4111 1111 1111 1111	Valid, formatted card number	2260 5651 2623 0906	Number that satisfies the Luhn checksum, with formatting retained
2980,5637/2204	Number with other formatting	8869,7287/0655	Number that satisfies the Luhn checksum, with formatting retained
4111111111111111	Valid card number, digits only	2260565126230906	Number that satisfies the Luhn checksum, digits only
1234 1234 5678 5678	Formatted card number that does not satisfy the Luhn algorithm	2260 5651 2623 0906	Number that satisfies the Luhn checksum, with formatting retained
298A056B372C20	String with letters	–	No output, error is raised and masking stops due to invalid characters

Example

This example will apply from_unique_imitate masks to the vehicle_id, license_plate and validation_code columns.

version: '1.0'
tasks:
  - type: mask_table
    table: employees
    key: id
    rules:
      - column: vehicle_id
        masks:
          - type: from_unique_imitate
      - column: license_plate
        masks:
          - type: from_unique_imitate
      - column: validation_code
        masks:
          - type: from_unique_imitate

Show result

Before

After

vehicle_id	license_plate	validation_code
A003B77	BA981	aFec9-LIZN7
A143.B#77	GL1748	147563
A N4 CC-55	CDF345	aaGn%

vehicle_id	license_plate	validation_code
Z009Q32	BV912	bZwh0-NCZY9
M187.C#97	XP9165	986402
S K6 DR-92	LCU788	nbPr%

Using `on_invalid`

The on_invalid parameter can be used to control how invalid values are handled by from_unique_imitate. It can only be used in combination with the checksum parameter, since without specifying a checksum there is no way of considering a value "invalid".

A value is considered invalid if:

It is too short for the checksum, or,
The checkdigit(s) are not valid for the checksum, or,
It contains letters – this can be controlled with the skip_letters parameter. See Determining and Handling Invalid Values for more detail.

Note that null is a special case and is not considered invalid. null input is masked to null output, regardless of settings.

The following table illustrates the behaviour of from_unique_imitate based on different on_invalid parameters.

`on_invalid`	Behaviour when encountering invalid value
`error` (default if not specified)	The masking task stops with an error.
`skip`	The invalid value is retained.
`mask`	The value is masked, and warnings are logged to the run log. See Determining and Handling Invalid Values for more detail.

Determining and Handling Invalid Values

There two are main ways that a value can be invalid:

It contains letters.
It has the wrong length or checksum.

Note that special characters, punctuation, and spaces, do not affect the validity of values. For example, from_unique_imitate considers the values 123 456, 123-456 and 123456 the same.

Determining the validity based on the presence of letters can be controlled with the skip_letters parameter. When set to true, a value will not be invalid if it contains letters. However, if the digits themselves do not satisfy the given checksum, then the value would be considered invalid.

The following table shows the validity of some example values for the brazilian_cpf algorithm, with and without the use of skip_letters: true. The rules are applicable to any checksum.

Input Value	`skip_letters`	Valid	Reason
`12175488403` Valid CPF number	`true` or `false`	Yes
`121.754.884-03` Valid CPF number with formatting	`true` or `false`	Yes	Punctuation is ignored.
`AB121.754.884-03` Valid CPF number containing letters	`false`	No	The value contains letters.
`AB121.754.884-03` Valid CPF number containing letters	`true`	Yes	The letters are ignored due to the use of `skip_letters: true`.
`121.754.884-00` Invalid CPF, bad check digits.	`true` or `false`	No	Invalid due to bad check digits.
`AB121.754.884-00` Invalid CPF, contains letters and bad check digits.	`true` or `false`	No	Invalid due to bad check digits, regardless of skipping letters or not.
`121.754.884` Invalid CPF, bad length.	`true` or `false`	No	Invalid due to length.
`AB121.754.884` Invalid CPF, bad length.	`true` or `false`	No	Invalid due to bad length, regardless of skipping letters or not.

Once a value is determined to be invalid:

If using on_invalid: error then the masking task will stop with an error.
If using on_invalid: skip then the value will be returned unmasked.
If using on_invalid: mask then masking will continue, and is described in more detail below.

For invalid values, the behaviour of the mask will change based on the reason for it being invalid.

If masking a value that doesn't match the checksum, the output will match the checksum, provided the input value is of the correct length.

For example, the brazilian_cpf algorithm requires 11-digit values. An 11-digit value that is not a valid CPF number will be masked to a valid CPF number. However, a number that is not 11-digits will not be masked to a valid CPF number, as the number of digits is not correct for that algorithm.

The following table shows the minimum and maximum value length to which the checksum applies.

`checksum`	Minimum length (inclusive)	Maximum length (inclusive)
`brazilian_cpf`	11	11
`credit_card`	12	19
`luhn`	2	Unlimited

If the value is invalid because it contains letters:

The letters will be masked if skip_digits is false.
The letters remain unchanged if skip_digits is true.

The following table gives examples of masked outputs for invalid inputs, for different skip_letters options. on_invalid is set to mask, otherwise no masking would occur. The example uses the brazlian_cpf algorithm but the rules are applicable to any checksum.

Input Value	`skip_letters`	Output Value	Explanation
`AB121.754.884-03` Valid CPF with letters	`false`	`DF149.758.055-29`	Valid CPF in output, with letters masked.
`AB121.754.884-03` Valid CPF with letters	`true`	`AB149.758.055-29`	Valid CPF in output, letters not masked.
`121.754.884-99` Invalid CPF	`true` or `false`	`149.758.055-29`	Valid CPF in output.
`121.754.884-99AB` Invalid CPF with letters	`false`	`149.758.055-29DF`	Valid CPF in output, with letters masked.
`121.754.884-99AB` Invalid CPF with letters	`true`	`149.758.055-29AB`	Valid CPF in output, letters not masked.
`121.754.884` Too short for CPF	`true` or `false`	`246.016.536`	Not a valid CPF, as input too short.
`121.754.884.692` Too long for CPF	`true` or `false`	`246.016.536.420`	Not a valid CPF, as input too long.
`AB121.754.884` Too short for CPF, with letters	`false`	`DF246.016.536`	Not a valid CPF, as input too short, with letters masked.
`AB121.754.884.692` Too long for CPF, with letters	`false`	`DF246.016.536.420`	Not a valid CPF, as input too long, with letters masked.
`AB121.754.884` Too short for CPF, with letters	`true`	`AB246.016.536`	Not a valid CPF, as input too short, letters not masked.
`AB121.754.884.692` Too long for CPF, with letters	`true`	`AB246.016.536.420`	Not a valid CPF, as input too long, letters not masked.

Warning: When using on_invalid: mask, there may be cases where unique inputs create non-unique outputs.

For example, a column may contain a CPF number 121.754.884-03 and similar number 121.754.884-99 (which is not a valid CPF, but shares the same first nine digits). On masking these would be become the same number in the output, due to mask being applied to the first nine identical digits, and then identical check digits being generated from this.

For this reason, you should be careful with the use of on_invalid: mask. Consider using on_invalid: skip instead, which will retain invalid values as they are, and only generate valid replacements from other valid inputs.

Retaining Prefixes

When masking values with from_unique_imitate, the retain_prefix_length option be used to specify the number of prefix characters of the input to retain in the output.

The length takes into account only characters that would be masked based on the parameters of the mask. Since from_unique_imitate doesn't mask punctuation or spaces, then these are not counted towards the prefix length. Similarly, if using skip_letters/skip_digits, then letters or digits (respectively) won't be counted in the prefix.

If the retain_prefix_length is equal to or longer than the values to be masked, then an error will be raised during masking.

The following table shows the retained prefix for example values, based on different parameters.

Input Value	`retain_prefix_length`	`skip_letters`	`skip_digits`	Retained Prefix	Example Output
`A1B2C3D4E5`	4	`false`	`false`	`A1B2`	`A1B2F6G7H8`
`A1 B2 C3 D4 E5`	4	`false`	`false`	`A1 B2`	`A1 B2 F6 G7 H8`
`A1 B2 C3 D4 E5`	4	`true`	`false`	`A1 B2 C3 D4`	`A1 B2 C3 D4 E8`
`A1 B2 C3 D4 E5`	4	`false`	`true`	`A1 B2 C3 D`	`A1 B2 C3 D4 J5`
`A1B`	4	`true`/`false`	`true`/`false`	-	No output, error is raised as the prefix is >= the length of the value
`AAA111`	3	`false`	`false`	`AAA`	`AAA456`
`AAA111`	3	`true`	`false`	-	No output, error is raised as the prefix is >= than number of digits
`AAA111`	3	`false`	`true`	-	No output, error is raised as the prefix is >= than number of letters

The retain_prefix_length parameter can be combined with the checksum parameter. The checksum will be generated after combining the prefix with the masked values.

For example, using the luhn checksum, with retain_prefix_length of 5. The value to be masked is 211287932175 (which is valid for the Luhn checksum).

The order of masking is:

Extract 5 characters as a prefix: 21128
Apply unique masking to all digits but the checksum digit 793217, giving (for example) 123456
This value is combined with the prefix, giving 21128123456
The checksum digit is calculated from this prefix and masked value, which is 0
The checksum digit is appended to the masked value, giving final output value of 211281234560, which is also valid for the Luhn algorithm.

When retaining the prefix and using a checksum, the number of digits must be less than the number of digits in the original value, minus the length of the check digits. For example, to mask a Brazilian CPF, a maximum of 8 digits can be retained, since the value is made up of 11 digits with the final 2 being checkdigits.

Checksum validity is checked when retaining the prefix, and validation is on the original value (i.e. including the prefix). To control how to handle invalid values, refer to Determining and Handling Invalid Values.

The following example ruleset shows masking a column using the credit_card checksum, retaining the first 4 digits.

version: "1.0"
tasks:
  - type: mask_table
    table: customers
    key: customer_id
    rules:
      - column: credit_card_number
        masks:
          - type: from_unique_imitate
            retain_prefix_length: 4
            skip_letters: true
            checksum: credit_card

Show result

Before

After

customer_id	credit_card_number
1	ABC455673abc758689.985/5
2	4412ABC5
3	492.993918.7357.975

customer_id	credit_card_number
1	ABC455677abc906097.737/7
2	4412ABC3
3	492.966096.5480.180

RedShift Limitations

Due to the current method in which we mask with RedShift, please note the current limitations:

PRIMARY KEY constrained columns will be transformed into columns which are UNIQUE constrained instead of PRIMARY KEY constrained.

NOT NULL constrained columns will lose their NOT NULL constraint. However, this is not just limited to from_unique_imitate masking.

Unique Masks

From unique (from_unique)

Parameters

From Unique Imitate (from_unique_imitate)

Parameters

Values Requiring Checksums

brazilian_cpf

credit_card and luhn

Example

Using on_invalid

Determining and Handling Invalid Values

Retaining Prefixes

From unique (`from_unique`)

From Unique Imitate (`from_unique_imitate`)

`brazilian_cpf`

`credit_card` and `luhn`

Using `on_invalid`