Masking Joined Tables

• Parameters for Joining
• Example of Joining
• Join Types
• Join Conditions
• Join Aliases

When masking a table, you can specify a list of joins (or dictionary mapping keys to joins) that will join the rows of a target table to rows from one or more additional tables, providing you with the additional joined values to use in your masking rules. A join can also be used to control which rows get masked.

Parameters

• target_table (required): The name of the new table you wish to join into the masking data. The target table can be prefixed with a schema name to reference a table in another schema.
• target_key (required): The key on target_table to use when performing the join. Specify a single column name (for composite joins, see Join Conditions).
• source_table (required): The name of the table you wish join the target_table with. This could be the table being masked, or another table earlier in the list of joins (allowing you to perform multistep joins). The source table can be prefixed with a schema name to reference a table in another schema.
• source_key (required): The key on source_table to use when performing the join. Specify a single column name.
• type (optional): inner or left (default left). Note that an inner join masks only matched rows, leaving unmatched rows unmasked.
• alias (optional): An alias for the joined table, used to reference its columns in join conditions and masking rules. See Join Aliases.
• on (optional): An extra SQL condition for the join, applied in addition to matching the keys. See Join Conditions.

Example

In this example, we have two tables: Customers and Membership. We would like to mask the first_name and last_name columns of the Customers table, but only if the customer's membership status is Active.

The Customers contains data on customers, including their name and membership ID. The Membership table contains the status of the membership of each Customer: either Active or Inactive. The membership_id column of the Customers has a foreign key relation with the id column of the Membership table.

Customers Table

customer_id	first_name	last_name	membership_id
1	Anastasia	Rose	10001
2	Bill	Jones	10002
3	Chris	Yang	10003
4	Judith	Taylor	10004
5	Gordon	Smith	10005

Membership Table

id	membership_status
10000	Active
10001	Active
10002	Inactive
10003	Active
10004	Inactive

In order to access the membership_status column of the Membership table, we need to define a join in our ruleset from the Customers table to the Membership table.

version: "1.0"
tasks:
  - type: mask_table
    table: Customers
    key: customer_id
    joins:
      - target_table: Membership
        target_key: id
        source_table: Customers
        source_key: membership_id
    rules:
      - if:
        - column: '"Membership".membership_status'
          equals: Active
        rules:
          - column: first_name
            masks:
              - type: from_file
                seed_file: DataMasque_firstNames_mixed.csv
                seed_column: firstname-mixed
          - column: last_name
            masks:
              - type: from_file
                seed_file: DataMasque_lastNames.csv
                seed_column: lastnames

After performing the join, this will allow us to reference the membership_status column of the Membership table in our ruleset. In this example, we can reference the column with Membership.membership_status. Using this column, we can use Conditional Masking to only mask the rows of Customers where the status of the membership is 'Active'.

Note: To reference a column in a joined table, the table name of joined table must be added as a prefix to the column name

The example below utilises the from_file mask type detailed here to select a random first name from the DataMasque_firstNames-mixed.csv and a random last name from the DataMasque_lastNames.csv files that can be found on our Supplementary Files user guide. It will first check if the membership_status for the customer is 'Active', and if so, masks the two name columns: otherwise, these columns are left unmasked.

version: "1.0"
tasks:
  - type: mask_table
    table: Customers
    key: customer_id
    joins:
      - target_table: Membership
        target_key: id
        source_table: Customers
        source_key: membership_id
    rules:
      - if:
        - column: '"Membership".membership_status'
          equals: Active
        rules:
          - column: first_name
            masks:
              - type: from_file
                seed_file: DataMasque_firstNames_mixed.csv
                seed_column: firstname-mixed
          - column: last_name
            masks:
              - type: from_file
                seed_file: DataMasque_lastNames.csv
                seed_column: lastnames

This example will produce the following results in the Customers table. Customers with customer_id 2 and 4 are not masked because their membership status is 'Inactive', and customer 5 is not masked because it has no matching row in the joined Membership table.

customer_id	first_name	last_name	membership_id
1	Tia	Pallin	10001
2	Bill	Jones	10002
3	Liam	Pollard	10003
4	Judith	Taylor	10004
5	Gordon	Smith	10005

Join Types

A join's type controls which rows of the masked table get masked:

• left (default): every row is masked, whether or not it matches a row in the joined table.
• inner: only rows that match a row in the joined table are masked; unmatched rows are left unmasked.

The following ruleset masks name in the table created by joining customers with orders on customer_id. The optional type field specifies the type of join.

- type: mask_table
  table: customers
  key: id
  joins:
    - type: left # or inner
      target_table: orders
      target_key: customer_id
      source_table: customers
      source_key: id
  rules:
    - column: name
      masks:
        - type: from_fixed
          value: "MASKED"

The left join masks every customer. If type is set to inner, the join masks only customers who have an order — customer 2 has no matching orders row, so it is left unmasked:

id	name	has order?	left result	inner result
1	Alice	yes	MASKED	MASKED
2	Bob	no	MASKED	Bob
3	Carol	yes	MASKED	MASKED

Join Conditions

The main join is always on source_table.source_key = target_table.target_key. The optional on parameter allows adding further conditions on top. This is useful for specifying extra conditions or joining on composite keys:

version: "1.0"
tasks:
  - type: mask_table
    table: orders
    key: id
    joins:
      - target_table: customers
        target_key: id
        source_table: orders
        source_key: customer_id
        on: "orders.store_id = customers.store_id"
    rules:
      - column: email
        masks:
          - type: from_column
            column: customers.email

Each order joins to the customer with the same customer_id and store_id, then copies that customer's email.

Join Aliases

Give a join an alias to refer to the joined table. The alias then becomes the only name for that join — use it in hash_columns, conditions, where, on, and as the source_table of a later join. Note that an alias can be quoted (e.g. '"Sender"') if you want to preserve its exact case; if quoted, make sure to quote in where/on.

In the example below, the transactions table is aliased t and joined to users, aliased as sender, on each transaction's sender_id:

version: "1.0"
tasks:
  - type: mask_table
    table: transactions
    alias: t
    key: id
    joins:
      - type: inner
        alias: sender
        target_table: users
        target_key: id
        source_table: t
        source_key: sender_id
        on: "sender.status = 'active'"
    where: "t.amount > 100"
    rules:
      - column: sender_name
        hash_columns: ["sender.id"]
        masks:
          - type: from_file
            seed_file: DataMasque_firstNames_mixed.csv
            seed_column: firstname-mixed

Aliases are also what let you join the same table more than once — for example, joining users again as manager to mask the sender's manager's name:

version: "1.0"
tasks:
  - type: mask_table
    table: transactions
    key: id
    joins:
      - alias: sender
        target_table: users
        target_key: id
        source_table: transactions
        source_key: sender_id
      - alias: manager
        target_table: users
        target_key: id
        source_table: sender
        source_key: manager_id
    rules:
      ...

Note: DataMasque rejects a ruleset at validation time if:

• two joins share the same alias, or the same table is joined twice without aliases;
• an alias is the same as the masked table's name or alias;
• the masked table has an alias and a join references the masked table by its real name.

Note:

• For Microsoft SQL Server (Linked Server), joins are not currently supported.

• For Amazon DynamoDB and MongoDB, joins, where and alias are not supported, and a run using them will fail to start.

• For Amazon Redshift, the where clause is not supported in mask_table tasks, so joins that rely on a where clause cannot run on Redshift. Use an inner join or an on condition instead.

• For Microsoft SQL Server, when using temporary table, the name of the temporary table must be wrapped in quotation marks, as the # symbol in the YAML editor denotes the beginning of a comment (e.g. target_table: '##my_temporary_table' or '##my_temporary_table.column').

• To reference a temporary table column (e.g. the table_filter_column parameter of the from_file mask type or as a part of hash_columns) you must prefix the column name with its table name (e.g. table.column).

• Any column name specified without a table prefix is assumed to belong to the table being masked (as specified by the table parameter for the task). You cannot specify tables that belong to other schemas.