Build a temporary table (build_temp_table)

If you need to repeatedly join multiple tables when masking tables, or you need to perform some custom SQL transformations to column values, then you may wish to use the build_temp_table task type to create a temporary table which can then be accessed via a join during a mask_table task:

• Parameters
• Example (sql_select_statement)
• Example (sql_select_file)
• Example (table_reference)

Parameters

• table_name (required): The name of the temporary table to create in the database. You will need to use this name when referencing this temporary table later (e.g. in joins and masking rules). The table name can be prefixed with a schema name if the temporary table should be created in a schema other than the user's default schema.
• sql_select_statement (one of sql_select_statement, sql_select_file, or table_reference required): A string containing a SELECT statement to define the contents of the temporary table. To break the statement across multiple lines, you may use multi-line YAML syntax (|- or >-).
• sql_select_file (one of sql_select_statement, sql_select_file, or table_reference required): The name of a user uploaded SQL script file containing a SELECT query to define the contents of the temporary table. See the Files guide for more information on uploading SQL script files. Use this parameter if you have a complex / long query, or you wish to share the same query between many rulesets.
• table_reference (one of sql_select_statement, sql_select_file, or table_reference required): The name of a configured table reference to materialise into the temporary table — the map's rows are loaded into a typed table in the target database, so a large map can then be joined natively in a later mask_table task rather than looked up per row. Use this when the map is too large to hold in memory, or you want masking driven by a native SQL join onto a stored map. Supported on relational database targets except Amazon Redshift. See Materialising a map into a temporary table for the matching semantics.

Example (sql_select_statement)

version: '1.0'
tasks:
  - type: build_temp_table
    table_name: my_temporary_table
    sql_select_statement: >-
        SELECT accounts.account_id, address.city
        FROM accounts
        INNER JOIN address
        ON accounts.address_id = address.id
        WHERE accounts.country = 'New Zealand';
  - type: mask_table
    table: my_temporary_table
    key: id
    rules:
      - column: city
        masks:
          - type: from_file
            seed_file: DataMasque_NZ_addresses.csv
            seed_column: city

Example (sql_select_file)

version: '1.0'
tasks:
  - type: build_temp_table
    table_name: my_temporary_table
    sql_select_file: create_temp_table.sql
  - type: mask_table
    table: my_temporary_table
    key: id
    rules:
      - column: city
        masks:
          - type: from_file
            seed_file: DataMasque_NZ_addresses.csv
            seed_column: city

Example (table_reference)

Materialise the customer_accounts table reference into a temporary table, then mask customers.account_no by joining onto it and copying the mapped value with a from_column mask:

version: '1.0'
tasks:
  - type: build_temp_table
    table_name: account_map
    table_reference: customer_accounts
  - type: mask_table
    table: customers
    key: id
    joins:
      - type: inner
        source_table: customers
        source_key: account_no
        target_table: account_map
        target_key: real_account
    rules:
      - column: account_no
        masks:
          - type: from_column
            source_column: account_map.fake_account

Notes:

• DataMasque will create the temporary tables before applying any masks, and delete them after all tables have been masked. DataMasque will also ensure the temporary tables do not already exist in the database (removing existing temporary tables with the same name if needed). The temporary tables you define will only be available for use in joins, and cannot be masked themselves.

• For Microsoft SQL Server databases, temporary table names must begin with the ## characters, as they will be created as "Global Temporary Tables" so that they are visible to all parallel masking connections. However, in YAML the # character begins an inline comment, so the temporary table name must be wrapped in double or single quotes (e.g. table: '##my_temporary_table').

  • If using the temporary table name in any other tasks, it must also be quoted to avoid the # character acting as a YAML comment. For example, in a run_sql task, quote the entire SQL query:
    sql: "CREATE INDEX IDX_name ON ##my_temporary_table(name);"
    

• For Oracle, PostgreSQL, MySQL, MariaDB, Amazon Redshift, IBM Db2, IBM Db2 for i, SAP HANA, Snowflake, Databricks, and Databricks Lakebase databases, "temporary tables" are created as regular tables so that temporary tables are visible to all parallel masking connections. A build_temp_table task with a table_reference also creates a regular table on IBM Informix. If a run stops before the table is dropped, the table remains in the schema, and the connection user must be able to create and drop tables there.

• The build_temp_table task type is not currently supported for Amazon DynamoDB databases.

---