Account Management and Data Governance · Topic 5 of 9
Data Masking and External Tokenization
Protect sensitive columns with Dynamic Data Masking and external tokenization, and decide which to use based on business needs, edition and sharing.
- 5 min
- Masking and tokenization need Enterprise Edition or higher.
- Masking acts at query time on real values; tokenization stores tokens and detokenizes through an external function.
IS_ROLE_IN_SESSIONis preferred in policy conditions; conditional masking usesUSING.
What it is
Data governance in Snowflake means knowing what data you hold, who may see it and who did. Security policies are the part that enforces it: they are schema-level objects attached to columns or tables, and they apply the same rules to every query path, including views and shared data.
Column-level security comes in two forms. A masking policy (Dynamic Data Masking) loads the real value and hides it at query time for roles that should not see it. External tokenization replaces values with tokens before they are loaded, and a masking policy calls an external service to turn tokens back into real values for authorized roles. Both require Enterprise Edition or higher.
How it works
Masking policies
A masking policy has one data type, one or more conditions and one or more masking functions. It can be applied to many columns of the same data type, and the data is not changed in storage.
CREATE MASKING POLICY email_mask AS (val STRING) RETURNS STRING ->
CASE
WHEN IS_ROLE_IN_SESSION('HR_ANALYST') THEN val
ELSE REGEXP_REPLACE(val, '.+@', '*****@')
END;
ALTER TABLE hr.public.employees
MODIFY COLUMN email SET MASKING POLICY email_mask;
Points to know:
- The input and output data types in the policy definition must match.
- Prefer
IS_ROLE_IN_SESSIONoverCURRENT_ROLE, because it honors role inheritance and secondary roles. - Conditional masking adds a
USINGclause with other columns of the same table or view, so one column's value depends on another column. - Privileges:
CREATE MASKING POLICYon the schema creates a policy. A role needs the globalAPPLY MASKING POLICYprivilege, orOWNERSHIPof the object andAPPLYon the policy, to set or unset it. By default, object owners cannot see the masked column data. - A policy must be unset from every column before it is dropped.
- Future grants on masking policies are not supported.
- Policies can be attached to tags, so a masking policy follows the tag (covered on the tagging page). A policy set directly on a column wins over a tag-based one.
External tokenization
With external tokenization the sensitive value is tokenized by a third-party provider before it is loaded. A masking policy calls an external function at query time. Snowflake makes a REST call to the provider, which applies its own policy and returns either the token or the detokenized value.
CREATE MASKING POLICY ssn_detokenize AS (val STRING) RETURNS STRING ->
CASE
WHEN IS_ROLE_IN_SESSION('PAYROLL') THEN my_detokenize_fn(val)
ELSE val
END;
Requirements and facts:
- It needs a tokenization provider and an external function (through an API integration).
- Role mappings must exist in both Snowflake and the provider.
- Unauthorized users never see real values, because the real values were never loaded.
- A given value gets a unique token, so analysts can still group and count records by the token without seeing the data.
- Each query that needs detokenization makes calls to an outside service, which adds latency.
Masking versus external tokenization
| Requirement | Dynamic Data Masking | External tokenization |
|---|---|---|
| Data stored in Snowflake as | Real values | Tokens |
| Needs a third party | No | Yes |
| Built-in functions only | Yes | No, external function |
| Secure Data Sharing | Supported | Not supported |
| Real values exposed to Snowflake storage | Yes | No |
| Setup effort | Low | High |
Dynamic Data Masking supports data sharing, but not for policies that call external functions. External tokenization does not support sharing at all, because external functions cannot run in a share.
Look for the words "tokenized before loading", "third-party provider" or "the real value must never be stored". They point to external tokenization. "Different roles see different formats" or "share the table with a consumer" points to Dynamic Data Masking.
Limits to remember
- A materialized view limits policy use: a policy cannot be set on a table column if a materialized view exists on the table.
- External tables cannot be used as lookup tables in a policy.
- Nested objects run the table policy first, then the view policy.
- A column cannot be in both a row access policy and a masking policy signature.
Common mistakes
Using CURRENT_ROLE when roles are inherited. A parent role of HR_ANALYST fails the equality test; IS_ROLE_IN_SESSION handles inheritance.
Changing the policy output type. The return type must match the input type.
Expecting masking to protect against anyone with the policy privilege. Roles that can unset a policy can remove the protection; restrict APPLY MASKING POLICY.
Choosing tokenization for a table that is shared. It is not supported with Secure Data Sharing.
Dropping a policy still attached to a column. Unset it first.
What to remember for the exam
- Masking and tokenization need Enterprise Edition or higher.
- Masking acts at query time on real values; tokenization stores tokens and detokenizes through an external function.
IS_ROLE_IN_SESSIONis preferred in policy conditions; conditional masking usesUSING.- Tokenization does not support sharing; masking does, except with external functions.
- Privileges:
CREATE MASKING POLICY,APPLY MASKING POLICY,APPLYon the policy.
Check yourself
Which approach keeps real values out of Snowflake entirely?
External tokenization, because data is tokenized by a third party before it is loaded.
How does a masking policy depend on another column's value?
Use conditional masking with a
USINGclause that references columns in the same table or view.Which privilege lets a role attach a masking policy to a column anywhere in the account?
The global
APPLY MASKING POLICYprivilege.
Topic 5 of 9 in Account Management and Data Governance
Test yourself on Account Management and Data Governance
The full Snowflake ADA-C02 question bank has 814 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.