Masking

Hide sensitive values before they reach a target with partial masks, keyed hashes or constant replacements, with worked examples of each.

A mask replaces a column's value before it is written to the target. The source keeps the real value; the target only ever receives the masked one, in the initial load and in every change.

Before you begin

• See Validation and limits for the source settings that rules need.
• For a keyed hash, you need access to the agent host to store a secret key.

Mask a column in one table

1. Open Configure for the target and select the table.
2. Tick Choose output columns and list the columns to keep. See Output and computed columns.
3. For the column to mask, enter the mask as its Source expression, and keep the column's name as its Output name.

For example, to send dbo.Customers with its contact details masked:

Mask a column in every table

1. Open Configure and select All tables.
2. Open Output columns and masks for all tables (optional) and tick Mask columns globally.
3. Select Add column mask. Enter the column's name in Existing output name and the mask in Source expression.

Each global mask replaces the named column in every table that has it, and leaves the other columns unchanged. Names match without regard to case. A table that chooses its own output columns ignores global masks, so put the mask in its own list.

For example, these global masks hash every email column and mask every phone column:

A global mask doesn't skip a key column. If email is a primary key, unique index or foreign key column in one of the tables, the start is refused with protected-column-missing-or-transformed; give that table its own output columns.

Partial mask

PARTIAL_MASK(value, keep_start, keep_end) keeps the first keep_start and the last keep_end characters of a text value and replaces each character in between with *. The length of the value doesn't change.

• value must be text. Cast a number to text first: PARTIAL_MASK(account_number, 0, 4) on a number column is refused with string-type.
• keep_start and keep_end must be whole-number literals.
• When keep_start plus keep_end is at least the value's length, the value is unchanged, so choose counts that hide enough of your shortest values.
• NULL stays NULL.

Keyed hash

HASH_MASK(value, 'alias') replaces a value with an HMAC-SHA256 of the value under a secret key, written as 64 lowercase hexadecimal characters. The same value always gives the same hash under the same key and version, so masked columns can still be joined, grouped and counted at the target. Without the key, the original can't be recovered or guessed by hashing candidate values.

• value can be text or binary. Cast a number to text first: HASH_MASK(national_id, 'customer') on a number column is refused with mask-input-type.
• 'alias' is a text literal naming the key, using letters, digits, dots, hyphens or underscores, at most 128 characters.
• NULL stays NULL.

The key itself never leaves the agent host. The rule holds only a reference to it.

The hash is exact: values that differ in case or spaces hash differently. Normalize them first if they should match:

HASH_MASK(LOWER(TRIM(email)), 'customer')
HASH_MASK(CAST(national_id AS VARCHAR(20)), 'customer')
HASH_MASK(document_fingerprint, 'customer')

Use the same alias wherever hashes must match across tables, for example customer on both dbo.Customers.email and dbo.Orders.email, and a different alias where they must not match, for example HASH_MASK(tax_id, 'finance').

Provide the key

1. Generate a random key of 32 to 4,096 bytes, encoded as base64.

   WindowsLinux

   $bytes = New-Object byte[] 32
   [System.Security.Cryptography.RandomNumberGenerator]::Create().GetBytes($bytes)
   [Convert]::ToBase64String($bytes)

2. Store the base64 text on each agent that runs the pipeline. With relay, store it on both the capture agent and the apply agent. Use either:

   • the environment variable SQLFLOW_SECRET_<name> of the agent service, or
   • the file secrets/<name> under the agent's data directory. A trailing newline is ignored.

3. In Configure, select All tables. For each alias your rules use, a pair of fields appears. Enter an immutable version in the field labeled with the alias and key version, such as customer: key version, for example v1, and the reference in Operator secret reference, such as env:CUSTOMER_MASK_V1 or file:CUSTOMER_MASK_V1.

Enter the reference, never the key. See Database credentials and secret references for the reference format.

For example, rules that use the aliases customer and finance need two keys:

The alias and version are part of every hash, so the same key gives different hashes under a different alias or version. Treat a version as fixed. To rotate a key, store the new key under a new name, enter a new version and reference, and save the rules. Saving reloads the pipeline, so every row is hashed again with the new key.

Constant replacement

To replace every value with the same text, cast a literal to the type you want at the target, or use REDACT with a text literal:

CAST('[redacted]' AS VARCHAR(32))
REDACT('[redacted]')

REDACT accepts only a text literal: REDACT(tax_id) is refused with redact-constant-required, or mask-constant-count-required in a global list. The cast form also sets the target column's type.

A constant replaces NULL values too. To keep NULL where the source has no value, test for it with CASE:

CASE WHEN tax_id IS NULL THEN NULL ELSE '***-**-****' END

Where masks can't be used

• Row conditions. A condition reads the real source values. HASH_MASK in a condition is refused with mask-in-predicate, and PARTIAL_MASK with processing_source_predicate_unsupported.
• Key columns. Primary key, unique index and foreign key columns must pass through unchanged. See Columns that must pass through unchanged.
• Non-literal arguments. PARTIAL_MASK counts and the HASH_MASK alias must be literals. PARTIAL_MASK(email, prefix_length, 3) is refused with mask-constant-count-required, and HASH_MASK(email, key_name) with mask-key-alias-constant-required.
• Other hash functions. HASH and KEYED_HASH are refused with immutable-mask-key-version-required. Use HASH_MASK.

See also

• Output and computed columns
• Expression reference
• Database credentials and secret references
• Security considerations