Accounts and Security · Topic 6 of 17
Data Governance and Column-Level Security - Masking and Tokenization
Protect sensitive columns with Dynamic Data Masking and external tokenization: policy syntax, conditional and tag based masking, and when to choose each.
- 5 min
- Data governance combines column security, row security, privacy policies, tags, lineage and auditing.
- Masking policies are schema objects, evaluated at runtime, one per column, same data type in and out; Enterprise Edition or higher.
- Conditional masking passes extra columns with
USING; tag based masking scales the policy across columns.
What it is
Data governance in Snowflake means knowing what data you hold, controlling who sees which part of it, and proving it afterward. The governance toolbox has three families: column-level security (masking and tokenization), row-level security (row access policies) and privacy policies (aggregation and projection), plus tags, lineage and access history to describe and audit it.
Column-level security hides or transforms the value of a column for some users while leaving the stored data intact. Snowflake offers two forms: Dynamic Data Masking and external tokenization. Both are policy objects attached to a column and both need Enterprise Edition or higher.
How it works
Dynamic Data Masking
A masking policy is a schema level object. It takes the column value (and optionally other columns) as input and returns a value of the same data type. It is evaluated at query time, so the table always holds the real data.
CREATE MASKING POLICY email_mask AS (val STRING) RETURNS STRING ->
CASE
WHEN IS_ROLE_IN_SESSION('HR_ADMIN') THEN val
ELSE REGEXP_REPLACE(val, '.+@', '*****@')
END;
ALTER TABLE customers MODIFY COLUMN email SET MASKING POLICY email_mask;
ALTER TABLE customers MODIFY COLUMN email UNSET MASKING POLICY;
Properties to know:
- Same policy, many columns. One policy can protect any column with a matching data type, which keeps rules consistent.
- One policy per column. A column cannot have two masking policies at once.
- Same return type. The policy must return the data type of the column it masks.
- Conditional masking passes extra columns as arguments, for example hiding a value unless another column has a given value.
CREATE MASKING POLICY region_mask AS (val STRING, region STRING) RETURNS STRING ->
CASE WHEN region = 'EU' THEN '***' ELSE val END;
ALTER TABLE customers MODIFY COLUMN phone
SET MASKING POLICY region_mask USING (phone, region);
- Privileges. Creating a policy needs
CREATE MASKING POLICYon the schema; attaching it needsAPPLY MASKING POLICYon the account, orAPPLYon the policy together with ownership of the table. Policy administration is often given to a separate security role, so table owners cannot remove masking. - Role checks. Prefer
IS_ROLE_IN_SESSIONoverCURRENT_ROLE, so inherited and secondary roles count. - Tag based masking. A masking policy can be set on a tag. Every column carrying that tag, and a matching data type, is masked automatically, which scales better than attaching policies one column at a time.
"Mask sensitive columns across hundreds of tables with the least effort" points to tag based masking. "Different values for different roles" points to a conditional masking policy.
Masking does not change storage. Joins and filters on a masked column still use the policy result, so a masked value can break an equality join for unauthorized roles. Plan which roles see real values.
External tokenization
With external tokenization, sensitive values are replaced by tokens before they are loaded, by a tokenization provider outside Snowflake. The table stores tokens. At query time a masking policy calls an external function that asks the provider to detokenize the value for roles that are allowed to see it.
CREATE MASKING POLICY ssn_detokenize AS (val STRING) RETURNS STRING ->
CASE
WHEN IS_ROLE_IN_SESSION('COMPLIANCE') THEN detokenize_udf(val)
ELSE val
END;
Here detokenize_udf is an external function behind an API integration and a remote service. Authorized roles get real values; everyone else sees the token.
Choosing between them
| Aspect | Dynamic Data Masking | External tokenization |
|---|---|---|
| Stored value | Real data | Tokens only |
| Where the logic runs | Inside Snowflake | At the provider through an external function |
| Real data in Snowflake | Yes, protected by policy | No, unless detokenized at query time |
| Setup effort | Low | High: provider, API integration, external function |
| Query speed | Fast | Slower, because each query calls out |
| Best for | Role based display of partial or hidden values | Strict rules that real values must never rest in Snowflake |
Tokenization adds a network round trip per query and depends on the external service. If the service is down, authorized users cannot see real values. Choose it only when a rule forbids storing real values.
Common mistakes
Assuming masking changes the stored data. It does not. Users with the right role, or a role that can change the policy, can reach the original values.
Giving table owners control of policies. A table owner can then unset the policy. Centralize policy management.
Returning a different data type. The policy fails validation. Match the column type.
Using CURRENT_ROLE and ignoring role hierarchy. An administrator above the allowed role may still see masked data.
What to remember for the exam
- Data governance combines column security, row security, privacy policies, tags, lineage and auditing.
- Masking policies are schema objects, evaluated at runtime, one per column, same data type in and out; Enterprise Edition or higher.
- Conditional masking passes extra columns with
USING; tag based masking scales the policy across columns. - External tokenization stores tokens, and a masking policy calls an external function to detokenize.
- Use tokenization when real values must not be stored in Snowflake; otherwise masking is simpler and faster.
Check yourself
How can one policy protect all columns tagged as PII?
Set the masking policy on the tag; columns carrying the tag with a matching data type are masked automatically.
What does the table store under external tokenization?
Tokens produced by the provider; the masking policy detokenizes them through an external function for allowed roles.
Why prefer
IS_ROLE_IN_SESSIONin a masking policy?It also recognizes inherited and secondary roles, while
CURRENT_ROLEsees only the primary role.
Topic 6 of 17 in Accounts and Security
Test yourself on Accounts and Security
The full Snowflake ARA-C01 question bank has 687 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.