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
In a nutshell
  • 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.

Queryfrom a rolereadsColumn with policymasking or tokenizationpolicy decidesReal valueauthorized rolesorMasked or detokenized valueeveryone else

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 POLICY on the schema; attaching it needs APPLY MASKING POLICY on the account, or APPLY on 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_SESSION over CURRENT_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.
TagPII on columnhasMasking policyset on the tagapplies toAll tagged columnsmatching data type
Exam tip

"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;
Source datatokenized byProvideroutside Snowflakeloaded asTabletokens onlyquery + policyExternal functioncalls providerreturnsReal valuefor allowed roles

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

AspectDynamic Data MaskingExternal tokenization
Stored valueReal dataTokens only
Where the logic runsInside SnowflakeAt the provider through an external function
Real data in SnowflakeYes, protected by policyNo, unless detokenized at query time
Setup effortLowHigh: provider, API integration, external function
Query speedFastSlower, because each query calls out
Best forRole based display of partial or hidden valuesStrict rules that real values must never rest in Snowflake
Watch out

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_SESSION in a masking policy?

    It also recognizes inherited and secondary roles, while CURRENT_ROLE sees 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.