Table masks (mask_table)

Each mask_table task specifies the masking rules to apply to a database table, as well as any required joins and any conditionals needed to define which rows should be masked. Masking rules and masks are applied sequentially in the order they are listed. When multiple masks are combined in sequence, the output value from each mask is passed as the input to the next mask in the sequence.

More detail about mask functions and rules is provided under Masking Overview section.

Note: While mask_table is suitable for the majority of generic masking requirements, many masking functions are not capable of masking unique keys or primary keys. To mask these columns, use the imitate_unique mask function, or the special-purpose mask_unique_key task.

• Parameters
  • About regex
  • About coerce_whole_numbers_to_int
• Composite keys
• Calculated or generated bounds
  • When to disable calculated bounds
  • Supported Database Engines
• Example mask_table ruleset

Parameters

Each task with type mask_table is defined by the following parameters:

• table (required): The name of the table in the database. The table name can be prefixed with a schema name to reference a table in another schema. If the table or schema name are case-sensitive, you must enclose the name in double and single quotation marks in order to specify the casing of the name. For example, table: '"CaseSensitiveSchema"."CaseSensitiveTable"'

  • For rulesets run against Amazon DynamoDB connections, the table name can be prefixed with the AWS region that contains the table e.g. <region>/<table-name> (without angle brackets). Note that the region prefix is required if you have not created a .aws/credentials file or environment variable with the default region or are running DataMasque on EC2.

• alias (optional): An alternate name for the table being masked, used to reference its columns in conditional masking and joins. Once an alias is set, the table's original name can no longer be used.

• key (required): One or more columns that identify each table row. Composite keys may be specified for the key parameter. For more details on using composite keys, see Composite keys

  • For Oracle databases it should always be ROWID (key: ROWID). For more details, refer to Query optimisation.
  • For Microsoft SQL Server and PostgreSQL databases it is recommended to use the primary key, or any other unique key that is not modified during masking, for better performance.
  • The key columns must not contain any NULL value.
  • Each database has a limited range of data types for key columns. Refer to the Database Connections page for information on which data types are accepted as key columns for mask_table tasks.
  • If the key column names are case-sensitive, you may enclose each key value in double and single quotation marks in order to specify the casing of the key. For example, key: '"Customer_ID"'
  • If the columns specified for the key parameter cannot be used to uniquely identify rows, then the masked values will be the same for rows that have the same key value. Refer to the key and hash columns example in the Notes section for how to avoid producing duplicate masked values.
  • Columns specified in the key cannot be masked in the task's rules. Amazon DynamoDB is an exception to this rule, allowing key columns to be masked with e.g. the from_unique mask type. IMPORTANT: Masking the key column in DynamoDB can result in loss of rows if the final key column values are not all unique.

• on_missing_column (optional): The action to take if a column in the ruleset is not found on the table. This option is only available for Amazon DynamoDB. If specified, must be one of error or skip. Defaults to error, which means a masking run will fail if a column in the ruleset does not exist. If set to skip then missing columns do not cause an error and other columns are masked as normal.

• use_calculated_bounds (optional): When set to true (default) then the batch bounding keys are calculated using values in the database. If set to false, then DataMasque generates these keys without querying the database, which may improve performance. However, this option is only supported for integer, non-composite keys. For more details, see Calculated or generated bounds.

• hash_columns (optional): A list of columns which will be used as input to the Deterministic masking algorithm for all masking rules in this task, unless overridden at the column level. When specified at the task level, this provides a default deterministic behavior for all columns being masked in the table. If hash_columns is provided, all mask types that rely on randomisation become deterministic based on the hash_columns column values. Values in the provided columns can be null.

  Task-level hash_columns can be overridden for individual columns by:

  • Specifying different hash_columns at the column level (in the rule's hash_columns parameter). This completely replaces the task-level hash_columns for that column.
  • Setting hash_columns: null at the column level to disable deterministic masking for that specific column.

  For database masking, the hash column can be prefixed with a table name to reference a column in another table, and that table name can be prefixed with a schema name to reference a table in another schema.

  Hash columns can also be specified by a list of dictionaries with the following keys:

  • column_name: The name of the column which will be used as input as above. Required unless source is specified.
  • source (optional): Set to self to use the value being masked as its own hash source, ensuring identical input values produce identical masked outputs.
  • case_transform (optional): upper/lower - Allows for case transforms on the values of the input, ensuring consistent hashed values irrespective of case.
  • trim (optional): If the value extracted from the column, json_path, or xpath contains leading or trailing white space, it can be trimmed by setting this option to true. Defaults to false.
  • json_path (optional): If the column contains JSON data, the path to the value in the JSON data you wish to perform the hash on can be specified here, otherwise the hash will be performed on the entire column.
  • xpath (optional): If the column contains XML data, the Xpath to the value stored in the XML data you wish to perform the hash on can be specified here, otherwise the hash will be performed on the entire column.
  • coerce_whole_numbers_to_int (optional): When set to true, this option will coerce whole number float values in the specified column to integers before being used for hashing.
  • regex (optional): A regular expression pattern to extract and concatenate values from the hash column before hashing. When specified, all matches (or capturing groups, if present) are concatenated together to form the value used for hashing. If the pattern does not match, the original value is preserved. This is applied before other transforms (trim, case_transform).
  • match_index (optional): A zero-based index specifying which occurrence of the regex pattern to use for hashing. For example, 0 uses only the first match, 1 uses only the second match, and so on. If omitted, all matches are concatenated (default behaviour). Requires regex to be specified.

  A hash_columns entry can also be a table_reference lookup instead of a column: a mapping that seeds masking from a shared identity map, so the same entity masks consistently across systems. See Seeding masking from a table reference.

About regex

The regex transform is useful when hash column values have inconsistent formatting that should not affect deterministic masking. For example, SSN values may be stored as 123-45-6789 or 123456789 — using regex: '\d+' extracts all digit sequences and concatenates them, producing 123456789 in both cases and ensuring consistent hash output.

If the pattern contains capturing groups, all captured groups across all matches are concatenated. If no capturing groups are defined, all matched substrings are concatenated.

Example

  hash_columns:
    - column_name: ssn
      regex: '\d+'

Input	Regex	Hash Input
123-45-6789	\d+	123456789
123456789	\d+	123456789
(123) 456-789	\d+	123456789
no-digits	\d+	no-digits

About coerce_whole_numbers_to_int

When coerce_whole_numbers_to_int is enabled, whole number float values will be coerced to integers (e.g. 1.0 to 1, 4.0 to 4, and so on). This ensures consistent hashing between integer columns and columns where whole numbers are stored as floats/decimals.

Numbers that aren't whole are not affected by this option. For example, 1.5 will still be treated as 1.5 even when this option is enabled. Strings are also not affected.

Example Behavior

Input	Input Type	Output with coerce_whole_numbers_to_int set to false	Output with coerce_whole_numbers_to_int set to true
2.4	float/decimal	2.4	2.4
"3.0"	string	"3.0"	"3.0"
"3.1"	string	"3.1"	"3.1"
2.0	float/decimal	2.0	2
5	integer	5	5

For more information on the json_path, please refer to the JSON documentation.
For more information on the xpath, please refer to the XML documentation.
For more information on deterministic masking, please refer to the Deterministic masking.

rules (required): A list of masking rules (or dictionary mapping arbitrary keys to rules) to apply to the table. Rules are applied sequentially according to the order they are listed. Regular (non-conditional) masking rules are defined by the following attributes: - column (required): The name of the column to mask. For rulesets run against Amazon DynamoDB connections, a wildcard * can be used to apply masks to columns not matched by other column's rules. - masks (required): A list of masks (or dictionary mapping arbitrary keys to masks) to apply to the column. Masks are applied sequentially according to the order they are listed, so the output value from each mask is passed to the next mask in the list. Each type of mask has a different set of required attributes, as described for each type in Mask functions. - hash_columns (optional): A list of columns to use for Deterministic masking for this specific rule, overriding any task-level hash_columns. Set to null to disable deterministic masking for this column. See the task-level hash_columns parameter above for full details on syntax and options. - auto_truncate_value (optional): When set to true, a masked value wider than this column is truncated to fit for this specific rule, overriding the ruleset-level auto_truncate_value. If not set, the ruleset-level value is used (which itself defaults to false). - disable_warning_on_auto_truncate_value (optional): When set to true, suppresses the truncation warning for this specific rule, overriding the ruleset-level setting. If not set, the ruleset-level value is used.

• workers (optional): The number of parallel processes to use for masking this table (defaults to 1). Each process will operate on a separate batch of rows from the table (batch size is a configurable run option). Increasing workers may decrease masking run times, dependent on database performance and the complexity (e.g. number of columns to mask) of the masking task. It is recommended to increase the number of workers if DataMasque connections to your target database spend more time processing queries than waiting for DataMasque (i.e. the "waiting for clients" time approximates DataMasque CPU time), which suggests DataMasque could efficiently use multiple workers to mask other batches while waiting for database responses. Additionally, it is recommended that the number of parallel processes multiplied by the number of workers assigned to each process does not exceed twice the number of CPUs available on your deployed instance.

• index_key_columns (optional): When index_key_columns is true (the default setting), DataMasque will create an additional index on all key columns if there is no existing index contains all key columns. You may wish to disable the creation of a new index if you have an existing index on some of the key columns that will provide adequate performance.

  • Redshift databases do not support indexes, so this option has no effect for them.

• allow_masking_foreign_keys (optional): To preserve referential integrity, an error will be raised if any of the rules masks a column that is a foreign key, as the parent of a foreign key should usually be masked with mask_unique_key and cascaded to its foreign key(s) when possible. This error is disabled when this option is set to true. Defaults to false.

• disable_warning_on_use_of_conditionals (optional): By default, a warning is shown if conditional options skip, if/else or where are used, as this may mean not all rows are being masked (see Conditional Masking). To disable this warning, set disable_warning_on_use_of_conditionals to true on the specific mask_table task. Defaults to false.

• auto_manage_triggers (optional): When set to true, DataMasque will automatically detect and disable database triggers on the target table, or ON UPDATE expressions on the table's columns, before masking. After masking, triggers and expressions are enabled. This overrides any ruleset-level auto_manage_triggers setting. For detailed information, see Automatic trigger management. Defaults to true for supported databases.

• auto_manage_indexes (optional): When set to true, DataMasque will automatically detect and disable or drop non-unique indexes on the target table for columns being masked. After masking, indexes are re-added. This can help with performance and index-related issues during the masking process. For detailed information on database support and usage, see Automatic index management. Defaults to true for supported databases.

• action_on_batch_size_exceeded (optional): Specifies what DataMasque will do if the number of rows returned from the database exceeds the specified batch size, which can lead to excessive memory usage and performance degradation. This could happen if the key columns are non-unique or when querying corrupt tables. If specified, must be either warning or error. Defaults to error. The warning option will cause DataMasque to log a warning in the run log and continue masking. The error option will cause DataMasque to fail the masking run and log the error.

• log_row_key_on_failure (optional): When set to true, if an error occurs while masking a row, the primary key values of the failing row will be included in the error message. The error message will include the key column names and their values in the format: "Row key values: column1=value1, column2=value2 - skipping remainder of table: table_name". This is useful when debugging issues with masks that validate data format (e.g., credit_card with on_invalid: error, set_checksum with on_invalid_format: error). Defaults to false.

  Note: This parameter is only available for mask_table tasks, not for other task types like mask_unique_key.

Composite keys

A composite key can be specified in following formats:

• A list of columns; for example, key: ['invoice_id', 'product_id'] would be used to indicate a composite key consisting of a combination of two columns, invoice_id and product_id.
• For Microsoft SQL Server and PostgreSQL, when specifying a composite key, the order of the keys listed must respect the original order as defined in the database.
• A multiline composite key. An example of a multiline composite key is shown below.

  key:
    - 'invoice_id'
    - 'product_id'

Calculated or generated bounds

DataMasque's mask_table processes a table's data in batches. It fetches a specified number of rows, masks them, and writes them back to the database. The number of rows is determined by the batch size.

By default, DataMasque calculates bounding keys for each batch using the values in the key column(s) specified in the mask_table task. For example, if a table's id column has values ranging from 100,000 to 200,000 and the batch size is 50,000, the batches would be as follows:

Batch Number	Start id	End id	No. Rows In Batch
1	100,000	149,999	50,000
2	150,000	199,999	50,000
3	200,000	200,000	1

This approach is called calculated bounds and is the default method if use_calculated_bounds is not specified or set to true. Calculated bounds work with non-integer columns and composite keys. As long as the key is unique, all batches (except the last one if the total number of rows is not divisible by the batch size), will have the same number of rows as the batch size.

Generating bounding keys requires querying the database, which can be time-consuming for large tables (e.g. 100,000,000 rows or more). In such cases, setting use_calculated_bounds to false allows DataMasque to generate bounding keys without querying the table. This means batch sizes may vary if key values are non-contiguous or do not start at 0, but no batch will exceed the specified batch size.

With use_calculated_bounds: false, DataMasque generates bounds by incrementally adding the batch size until the largest value in the key column is reached. Here's how the batches would look for the same example:

Batch Number	Start id	End id	No. Rows In Batch
1	0	49,999	0
2	50,000	99,999	0
3	100,000	149,999	50,000
4	150,000	199,999	50,000
5	200,000	200,000	1

In this scenario, batch 1 is empty. DataMasque skips empty batches, but querying still occurs, which can be inefficient if there are many non-contiguous IDs.

The performance impact of enabling or disabling use_calculated_bounds depends on your dataset. Experimenting with use_calculated_bounds: false is recommended to see if it improves masking speed.

use_calculated_bounds may only be disabled if all the following conditions are true:

• A single key column is specified.
• The column is of an integer* type.
• The column must contain no negative values. 0 is allowed.
• The column must be a primary key or have a unique index/constraint.
• The maximum value in the column must be less than or equal to 2^32 (4,294,967,296).
• The column must not be nullable (e.g. should be created with NOT NULL constraint).

* Supported column types for all databases: INTEGER, BIGINT and SMALLINT. Additionally, Oracle also supports NUMBER columns with a scale of 0.

When to disable calculated bounds

The following table compares the effects of enabling or disabling use_calculated_bounds:

	use_calculated_bounds: true	use_calculated_bounds: false
Supports non-integer keys	Yes	No
Supports composite keys	Yes	No
Time spent up front querying bounds keys	May have long delays for bigger tables	No time spent up front
May waste time querying empty batches	No	Yes
Batch sizes consistent	Yes, except for last batch	Not consistent for non-contiguous keys
Requires an index*	No	Yes

*If use_calculated_bounds is true and no index is present, an index will be temporarily added for the task. Setting use_calculated_bounds to false is intended to boost performance on large tables. However, adding an index to such large tables may be time-consuming, thus making the overall task take longer. Therefore, if no index exists, the task will fail instead of adding an index. An index should be added prior to executing the mask_table task, either manually or with the use of a run_sql task.

Supported Database Engines

Disabling calculated bounds is supported on:

• IBM Db2 for i
• IBM Db2 LUW
• MariaDB
• Microsoft SQL Server
• MySQL
• Oracle
• PostgreSQL

Example mask_table ruleset

version: '1.0'
tasks:
  - type: mask_table
    table: users
    key: user_id
    rules:
      - column: last_name
        masks:
        - type: from_fixed
          value: "redacted last name"

Notes

1. Index operations will be performed online (ONLINE=ON) on SQL Server editions that support this feature.
2. Certain data types cannot be used as key columns for specific database connections. See Unsupported Key Columns by Database for details.
3. While rules and masks should typically be provided as lists, they can also be specified as dictionaries that map arbitrary keys to rules/masks. For example:

...
rules:
  last_name_rule:
    column: last_name
    masks:
      fixed_mask:
        type: from_fixed
        value: "redacted last name"

4. Specifying rules or masks as a dictionary can allow you to override the rule/mask for a specific key when inheriting from a definition.
5. When masking a table, if a non-unique key is specified for the mask_table task alongside hash_columns, if the value of the hash_columns is different for more than one row with same key value, the final masked values will arbitrarily depend on the order that update statements are executed. This can be avoided by including the targeted hash_columns as part of a composite key for the mask_table task.

---