Snowflake Data Masking, Row Access & Privacy Policies

Micro-Concept 1: Dynamic Data Masking, Column-Level Control

A masking policy is a schema-level object that hides values in a single column at query time. The underlying data is never changed on disk. The mask is applied as the result set is built, based on who is running the query.

The policy body is a CASE expression that almost always tests CURRENT_ROLE(). Privileged roles see the real value. Everyone else sees a masked one. The policy receives the column value as its single argument and returns a value of the same type.

CREATE MASKING POLICY mask_ssn AS (val STRING) RETURNS STRING ->
  CASE
    WHEN CURRENT_ROLE() IN ('ACCOUNTADMIN') THEN val
    ELSE '***-**-' || RIGHT(val, 4)
  END;

Two facts about masking policies are tested often. The policy is a schema-level object, so a database and schema must exist before you create one. And the data type in the policy signature has to match the column data type. A STRING policy cannot be applied to a NUMBER column.

Masking works on tables and views. When the masked column appears in a join, a filter, or a projection, the mask applies in every location. That is why a masked column can quietly change the result of a WHERE clause for an unprivileged role.

In training sessions I have run, the most common surprise is that masking is role-based, not user-based. The policy reads CURRENT_ROLE(), so a user inherits visibility from whichever role they have activated, not from their username.

Micro-Concept 2: Tag-Based Masking, One Policy for Many Columns

Tag-based masking combines object tagging with masking. You set a masking policy on a tag using ALTER TAG ... SET MASKING POLICY. Any column carrying that tag is then protected automatically, with no per-column ALTER TABLE needed.

The condition for automatic protection is a data-type match. When the tagged column’s type matches the masking policy signature, the column is masked. This is how one policy covers hundreds of columns across many tables.

A tag-based policy can read the tag’s string value inside its own body. The function for that is SYSTEM$GET_TAG_ON_CURRENT_COLUMN. That lets a single policy mask differently depending on the sensitivity label assigned to each column.

This is one of the harder distinctions in Domain 2. Plain masking attaches a policy to a column. Tag-based masking attaches a policy to a tag, and the column inherits it. The exam writes scenarios where tagging is the only scalable answer.

Micro-Concept 3: Row Access Policies, Row-Level Filtering

A row access policy is a schema-level object that decides whether a row is visible. It returns a boolean. Rows where the expression evaluates to true are returned. Rows where it evaluates to false are removed from the result, silently.

The policy has a signature: one or more column names with data types. Those columns feed the boolean expression. A simple policy compares them against CURRENT_ROLE() directly. A real one usually joins to a mapping table.

CREATE ROW ACCESS POLICY ra_region AS (region STRING) RETURNS BOOLEAN ->
  'SALES_EXECUTIVE' = CURRENT_ROLE()
  OR EXISTS (
    SELECT 1 FROM region_map
    WHERE role_name = CURRENT_ROLE()
      AND allowed_region = region
  );

The mapping table pattern is the one the exam favours. A small table maps roles to the values they may see. The policy joins to it. Adding a new role to the mapping table changes access without altering the policy.

Three rules carry exam weight. A table column can be protected by only one row access policy at a time. An external table cannot be used as a mapping table. And you cannot change a policy’s signature with ALTER: you must drop and recreate it.

Micro-Concept 4: One Column, Not Both Signatures

This rule catches careful candidates. A single column cannot appear in both a masking policy signature and a row access policy signature at the same time. Snowflake rejects the second assignment.

You can still protect one table with both feature types. The masking policy guards column ssn. The row access policy keys on column region. The constraint is per-column, not per-table. The same column cannot be the input to both.

When a query hits a table that has both kinds of policy, the order is fixed. The row access policy runs first, filtering rows. The masking policy runs after, on the surviving rows. Knowing the order explains why a masked column can still drive row filtering elsewhere.

Micro-Concept 5: Privacy Policies, Aggregation and Projection

The third feature family is privacy policies. These are newer than masking and row access. They reached general availability in May 2024. The C03 outline lists them under sub-objective 2.2 as a distinct item, separate from masking.

An aggregation policy forces queries to aggregate before they return data. It sets a MIN_GROUP_SIZE. A group smaller than that returns no individual rows. This stops an analyst from reading one person’s record while still allowing group statistics.

CREATE AGGREGATION POLICY agg_min5 AS () RETURNS AGGREGATION_CONSTRAINT ->
  AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => 5);

A projection policy is the other half. It prevents a column from being projected in a SELECT list. The body returns a PROJECTION_CONSTRAINT. Set ALLOW => false and the blocked role cannot pull that column into output, even though row filtering and masking may permit other access.

The distinction the exam tests is intent. Masking changes the value an unprivileged role sees. Row access removes rows. Aggregation blocks per-record reads and demands group queries. Projection blocks a column from appearing in output at all. Four different protections, four different result-set effects.

All of these privacy policies require Enterprise Edition, the same floor as masking and row access. Account-level access control through RBAC, by contrast, is available in every edition. That edition split is itself a question, covered in Q1 below.

Official docs for today’s topics. The exam pulls directly from these.

Community resources to reinforce today’s concepts.