Skip to main content
Mask sensitive columns during transformation so downstream tables never contain the original values. The source data is unchanged—masking applies only to the output.
The approaches in this guide are not a substitute for your cloud data warehouse’s own masking policies. Consult your warehouse’s documentation to see whether a native masking policy better suits your governance requirements before relying on masking within a transformation pipeline.

​
Security considerations

  • Masking is one layer of security. Combine it with warehouse-level access controls on source tables for defense in depth.
  • Masking protects the output table only. Users with access to the source table or can still see unmasked data.
  • Unsalted hashes of predictable data (emails, phone numbers) can be reversed by brute-force comparison. Use a salted hash or full redaction for sensitive fields.
  • Hashing is deterministic, not random. The same input always produces the same output, which enables join and deduplication but also enables matching attacks.
  • Partial masking leaks information. Only use it when some visibility is an accepted trade-off.
  • Query history and audit logs may retain unmasked values from source table queries. Masking does not retroactively protect prior access.

​
How it works

Use a Calculator component between your input and output components. Give the calculation the same name as the source column to overwrite it in place. This approach works across all supported cloud data warehouses (Snowflake, Databricks, Amazon Redshift, and Google BigQuery). Some SQL functions have syntax differences—these are noted per strategy below.
Column quoting differs by warehouse: Snowflake and Amazon Redshift use "column", Databricks uses `column`, and BigQuery uses unquoted column names (or `column` if the name needs escaping). Each syntax table below uses the correct quoting for its warehouse.
If you’re using Databricks and need to mask sensitive entities embedded in unstructured text, use the AI Mask component, which is Databricks-specific.

​
How to mask a column

This walkthrough uses full redaction as an example. The same steps apply to any masking strategy—only the Calculator expression changes.
  1. Add a Table Input component (or any other input component) and configure it to read from the table containing the sensitive column.
  2. Add a Calculator component and connect it to your input component.
    1. Leave Include input columns set to Yes (the default) to pass all existing columns through unchanged.
    2. Click Calculations to open the dialog and add a new expression:
      1. In the field that reads “Add a name for your expression”, enter the exact name of the column you want to mask (for example, email).
      2. In the large text editor, write your masking expression, for example, 'REDACTED' for full redaction.
    3. Click Save to close the dialog.
    Because the calculation name matches the input column name, the original value is overwritten.
    The calculation name must match the input column name exactly. If it does not, the original values will not be overwritten.
  3. Add a Rewrite Table or Table Output component and connect it to the Calculator component. Configure it to write to your target table.
  4. Validate the pipeline, then sample the Calculator component to confirm the masking is applied before running. The target table will contain all original columns, but the values of the masked column will be replaced with REDACTED.

​
Masking strategies

Below are six strategies for masking sensitive data. Full redaction was used in the above example in step 2.b.ii. For any other strategy, replace the expression in that step with the appropriate syntax.

​
Full redaction

Replaces the value with a static string. The original is completely destroyed. Works identically on all warehouses.
Output: REDACTED
  • Irreversible: ✅
  • Data preserved: None

​
SHA-256 hash

Produces a fixed-length, one-way cryptographic hash. Output: ff8d9819fc0e12bf0d24892e45987e249a28dce836a85cad60e28eaaa8c6d976
  • Irreversible: ✅ (see Brute-force risk)
  • Data preserved: Deterministic—the same input always produces the same hash, so hashed values can still be used for joins and deduplication
BigQuery’s SHA256() returns BYTES. Wrap it in TO_HEX() to get a hex string equivalent to the other warehouses.

​
Salted SHA-256 hash

Prepends a secret value before hashing to defend against dictionary attacks.
  • Irreversible: ✅ (stronger than unsalted)
  • Data preserved: Deterministic per salt—joinable only if both sides use the same salt

​
MD5 hash

Shorter hash output. Known collision vulnerabilities, but acceptable for masking non-security-critical data. Output: c160f8cc69a4f0bf2b0362752353d060
  • Irreversible: ✅ (see Brute-force risk)
  • Data preserved: Deterministic, like SHA-256
BigQuery’s MD5() returns BYTES, like SHA256(). Wrap it in TO_HEX() to get a hex string equivalent to the other warehouses.

​
Partial mask

Preserves part of the value while hiding the rest. Output: ***.com
  • Irreversible: ❌—partial original data is exposed
  • Data preserved: Trailing characters visible

​
Character mask (Databricks only)

Replaces characters by category—uppercase letters, lowercase letters, digits, and other characters—with configurable substitutes. The original string’s length and structure are preserved, making masked values recognizable by format without revealing the actual data. This strategy uses Databricks’ built-in mask() function, which is not available on other warehouses.
Output: xxxx.xxx@xxxxxxx.xxx By default, mask() replaces uppercase letters with X, lowercase with x, and digits with n. You can override any of these by passing custom replacement characters:
Output: +#-###-###-#### Passing NULL for a character category leaves those characters unmasked.
  • Irreversible: ❌—the string length, structure, and any unmasked character categories are exposed
  • Data preserved: Format and length visible; unmasked categories (if NULL is passed) retain original values

​
Brute-force risk for hashing

Hashing is one-way, but not encryption. For low-entropy data (email addresses, phone numbers), an attacker with a list of known values can hash each one and compare against your table. Salting mitigates this—without the salt, the hashes cannot be reproduced.

​
What masking does and does not protect

​
Automating masking with Maia Team using skills and context files

You can instruct to automatically apply masking rules whenever it builds or modifies transformation pipelines. This is done through context files and skills—two features that shape how behaves across your project.
The context file and skill file examples below use Snowflake-specific syntax, but the same principles apply to other warehouses. Adjust the expressions as needed for your warehouse—Maia Team can do this for you with a single prompt.

​
Context files

A context file is a Markdown file in your project that reads on every prompt. Use one to define which columns or tables must always be masked, and how. Example context file:
Because context files apply to every interaction, will follow these rules whenever you ask it to build a transformation—even if you don’t mention masking in your prompt.

​
Skills

A skill is a reusable instruction set that activates only when relevant—for example, when a prompt involves building a transformation or mentions sensitive data. To create a masking skill:
  1. In the bottom left of the chat panel, click View and manage skills (the settings icon).
  2. Click Skills.
  3. Click Add new skill.
  4. Describe what the skill should do—for example, “Apply data masking rules when building transformations that involve PII columns.”
  5. creates a SKILL.md file in .matillion/maia/skills/data-masking/SKILL.md.
An example skill file:
Unlike context files, skills only activate when determines they are relevant to your request. This makes them better suited for rules that only apply to certain tasks.

​
When to use which

Both can be used together. A context file sets the baseline policy, while a skill provides detailed implementation steps that activates when it encounters a matching task.

​
Example pipelines

Working examples are available to download and import: The examples use a Fixed Flow component as a sample data source and branch into five (six for Databricks with character mask) parallel paths—one per strategy—each writing to a separate target table.