> ## Documentation Index
> Fetch the complete documentation index at: https://docs.maia.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Data masking in transformation pipelines

export const designer = "Designer";

export const maia_team = "Maia Team";

Mask sensitive columns during transformation so downstream tables never contain the original values. The source data is unchanged—masking applies only to the output.

<Warning>
  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.
</Warning>

***

## 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 {designer} 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](/docs/components/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.

<Note>
  *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.
</Note>

<Note>
  If you're using Databricks and need to mask sensitive entities embedded in unstructured text, use the [AI Mask](/docs/components/databricks-ai-mask) component, which is Databricks-specific.
</Note>

***

## 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](/docs/components/table-input) component (or any other input component) and configure it to read from the table containing the sensitive column.

2. Add a [Calculator](/docs/components/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.

   <Warning>
     The calculation name must match the input column name exactly. If it does not, the original values will not be overwritten.
   </Warning>

3. Add a [Rewrite Table](/docs/components/rewrite-table) or [Table Output](/docs/components/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.

```sql theme={null}
'REDACTED'
```

**Output:** `REDACTED`

* Irreversible: ✅
* Data preserved: None

### SHA-256 hash

Produces a fixed-length, one-way cryptographic hash.

| Warehouse       | Syntax                  |
| --------------- | ----------------------- |
| Snowflake       | `SHA2("email", 256)`    |
| Databricks      | ``sha2(`email`, 256)``  |
| Amazon Redshift | `SHA2("email", 256)`    |
| Google BigQuery | `TO_HEX(SHA256(email))` |

**Output:** `ff8d9819fc0e12bf0d24892e45987e249a28dce836a85cad60e28eaaa8c6d976`

* Irreversible: ✅ (see [Brute-force risk](#brute-force-risk-for-hashing))
* Data preserved: Deterministic—the same input always produces the same hash, so hashed values can still be used for joins and deduplication

<Note>
  BigQuery's `SHA256()` returns `BYTES`. Wrap it in `TO_HEX()` to get a hex string equivalent to the other warehouses.
</Note>

### Salted SHA-256 hash

Prepends a secret value before hashing to defend against dictionary attacks.

| Warehouse       | Syntax                                              |
| --------------- | --------------------------------------------------- |
| Snowflake       | `SHA2(CONCAT('your_secret_salt', "email"), 256)`    |
| Databricks      | ``sha2(CONCAT('your_secret_salt', `email`), 256)``  |
| Amazon Redshift | `SHA2(CONCAT('your_secret_salt', "email"), 256)`    |
| Google BigQuery | `TO_HEX(SHA256(CONCAT('your_secret_salt', email)))` |

* 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.

| Warehouse       | Syntax               |
| --------------- | -------------------- |
| Snowflake       | `MD5("email")`       |
| Databricks      | ``md5(`email`)``     |
| Amazon Redshift | `MD5("email")`       |
| Google BigQuery | `TO_HEX(MD5(email))` |

**Output:** `c160f8cc69a4f0bf2b0362752353d060`

* Irreversible: ✅ (see [Brute-force risk](#brute-force-risk-for-hashing))
* Data preserved: Deterministic, like SHA-256

<Note>
  BigQuery's `MD5()` returns `BYTES`, like `SHA256()`. Wrap it in `TO_HEX()` to get a hex string equivalent to the other warehouses.
</Note>

### Partial mask

Preserves part of the value while hiding the rest.

| Warehouse       | Syntax                               |
| --------------- | ------------------------------------ |
| Snowflake       | `CONCAT('***', RIGHT("email", 4))`   |
| Databricks      | ``CONCAT('***', RIGHT(`email`, 4))`` |
| Amazon Redshift | `CONCAT('***', RIGHT("email", 4))`   |
| Google BigQuery | `CONCAT('***', RIGHT(email, 4))`     |

**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()`](https://docs.databricks.com/en/sql/language-manual/functions/mask.html) function, which is not available on other warehouses.

```sql theme={null}
mask(`email`)
```

**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:

```sql theme={null}
mask(`phone_number`, NULL, NULL, '#')
```

**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

| Protected                                   | Not protected                                            |
| ------------------------------------------- | -------------------------------------------------------- |
| Downstream consumers of the target table    | Users with access to the source table                    |
| BI tools and reports querying masked output | Pipeline designers who can sample pre-masking components |
| Exported or shared datasets                 | Query history that may reference unmasked data           |

***

## Automating masking with Maia Team using skills and context files

You can instruct {maia_team} 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 {maia_team} behaves across your project.

<Note>
  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.
</Note>

### Context files

A [context file](/docs/guides/maia-context-files) is a Markdown file in your project that {maia_team} reads on *every prompt*. Use one to define which columns or tables must always be masked, and how.

Example context file:

```markdown theme={null}
# Data Masking Rules

## Always mask these columns

Whenever you create a transformation that reads from or writes to a table containing any of the following columns, mask them using the specified strategy:

| Column name pattern | Masking strategy | Expression (Snowflake) |
|---|---|---|
| `email` | Salted SHA-256 | `SHA2(CONCAT('${hash_salt}', "email"), 256)` |
| `phone`, `phone_number` | Full redaction | `'REDACTED'` |
| `ssn`, `social_security` | Full redaction | `'REDACTED'` |
| `ip_address` | Partial mask | `CONCAT(SPLIT_PART("ip_address", '.', 1), '.xxx.xxx.xxx')` |

## Always mask these tables entirely

When reading from the following tables, mask _all VARCHAR columns_ using full redaction unless the column is a primary key:

- `RAW.CUSTOMER_PII`
- `RAW.EMPLOYEE_RECORDS`

## General rules

- Always add a Calculator component between the source and output to apply masking.
- Never write unmasked PII columns to any target table.
- Use `${hash_salt}` for any salted hash — never hardcode a salt value.
```

Because context files apply to every interaction, {maia_team} 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](/docs/guides/maia-skills) is a reusable instruction set that {maia_team} 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 {maia_team} 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. {maia_team} creates a `SKILL.md` file in `.matillion/maia/skills/data-masking/SKILL.md`.

An example skill file:

```markdown theme={null}
---
name: data-masking
description: Apply column masking rules when building transformations that read from or write to tables containing PII or sensitive data.
---

# Data Masking Skill

When building a transformation pipeline that reads from a table containing sensitive columns, add a Calculator component to mask those columns before any output component.

## Columns to mask

- `email` → `SHA2(CONCAT('${hash_salt}', "email"), 256)`
- `phone`, `phone_number` → `'REDACTED'`
- `ssn` → `'REDACTED'`

## Rules

- Set **Include input columns** to **Yes** on the Calculator.
- Name each calculation identically to the source column to overwrite it.
- Place the Calculator immediately before the output component.
- If multiple columns need masking, handle them all in a single Calculator.
```

Unlike context files, skills only activate when {maia_team} determines they are relevant to your request. This makes them better suited for rules that only apply to certain tasks.

### When to use which

|              | Context file                             | Skill                                                                     |
| ------------ | ---------------------------------------- | ------------------------------------------------------------------------- |
| **Applied**  | Every prompt                             | Only when relevant                                                        |
| **Best for** | Blanket rules that must never be skipped | Task-specific guidance that would add noise to unrelated prompts          |
| **Example**  | "Never write unmasked PII to any table"  | "When building a customer transformation, mask email with salted SHA-256" |

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

***

## Example pipelines

Working examples are available to download and import:

* [Snowflake and Google BigQuery](https://docs-team.s3.eu-west-1.amazonaws.com/attachments/data-masking-transformation/data-masking-transformation-sf-bq.zip)
* [Databricks](https://docs-team.s3.eu-west-1.amazonaws.com/attachments/data-masking-transformation/data-masking-transformation-dbx.zip)
* [Amazon Redshift](https://docs-team.s3.eu-west-1.amazonaws.com/attachments/data-masking-transformation/data-masking-transformation-rs.zip)

The examples use a [Fixed Flow](/docs/components/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.