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
In a nutshell
  • 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_SESSION is preferred in policy conditions; conditional masking uses USING.

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.

Column with sensitive datahasMasking policyconditions and functionsevaluated atQuery timereturnsReal value or masked value

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_SESSION over CURRENT_ROLE, because it honors role inheritance and secondary roles.
  • Conditional masking adds a USING clause with other columns of the same table or view, so one column's value depends on another column.
  • Privileges: CREATE MASKING POLICY on the schema creates a policy. A role needs the global APPLY MASKING POLICY privilege, or OWNERSHIP of the object and APPLY on 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

RequirementDynamic Data MaskingExternal tokenization
Data stored in Snowflake asReal valuesTokens
Needs a third partyNoYes
Built-in functions onlyYesNo, external function
Secure Data SharingSupportedNot supported
Real values exposed to Snowflake storageYesNo
Setup effortLowHigh
Need to hide values in Snowflake onlychooseDynamic Data MaskingReal values must never be loadedchooseExternal tokenizationData must be sharedprefersDynamic Data Masking

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.

Exam tip

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_SESSION is preferred in policy conditions; conditional masking uses USING.
  • Tokenization does not support sharing; masking does, except with external functions.
  • Privileges: CREATE MASKING POLICY, APPLY MASKING POLICY, APPLY on 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 USING clause 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 POLICY privilege.

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.