I verified partial masking with Snowflake tag-based masking

This is Kawabata.

In this article, I will verify a configuration that uses SYSTEM$GET_TAG_ON_CURRENT_COLUMN in Snowflake's tag-based masking policies to switch the granularity of partial masking based on tag values.

https://docs.snowflake.com/en/sql-reference/functions/system_get_tag_on_current_column

https://docs.snowflake.com/en/user-guide/tag-based-masking-policies

Note: This article reflects verification results as of August 21, 2026.

【Addendum】
I was selected as a finalist in the "RISING COMMUNITY LEADER OF THE YEAR" category, APJ region, of the Snowflake Community Awards.
Please see the link below for details.

https://dev.classmethod.jp/articles/snowflake-community-awards-finalist-activities-review/

Overview of Partial Masking

The concept of showing data at reduced granularity

Partial masking is not a binary choice between hiding everything or showing everything — it means showing data at reduced granularity. For dates of birth, this breaks down into the following levels.

Since the day is lost, it becomes impossible to calculate exact ages, but if precise day-level age calculation is not required and the requirement is analysis by generation or age group, simply suppressing the day is sufficient. The implementation can be done with DATE_TRUNC.

DATE_TRUNC('MONTH', VAL)   -- 2001-03-26 -> 2001-03-01
DATE_TRUNC('YEAR',  VAL)   -- 2001-03-26 -> 2001-01-01

Switching granularity based on tag values

If you create a separate policy for each granularity level, re-tagging work arises every time a column is added. That is where SYSTEM$GET_TAG_ON_CURRENT_COLUMN comes in. This function reads the value of the tag assigned to the column being evaluated from within the masking policy. A single policy with a CASE expression can branch based on the tag value.

CASE
  WHEN SYSTEM$GET_TAG_ON_CURRENT_COLUMN('...DATA_CLASS') = 'PII_YEARMONTH'
    THEN DATE_TRUNC('MONTH', VAL)
  WHEN SYSTEM$GET_TAG_ON_CURRENT_COLUMN('...DATA_CLASS') = 'PII_YEAR'
    THEN DATE_TRUNC('YEAR', VAL)
  ELSE DATE '1900-01-01'
END

With this configuration, the only operational task is assigning tags to columns. When changing granularity, you simply update the tag value — there is no need to touch the policy itself.

Tag values become a catalog of granularities

Only one masking policy per data type can be set on a single tag. This means VARCHAR columns — whether phone numbers, email addresses, or addresses — are all handled by the same MASK_TEXT, and the policy cannot distinguish column attributes on its own. Therefore, express the attribute type within the tag value name itself.

For this verification, I defined them as follows.

The tag's ALLOWED_VALUES functions as a catalog of granularities approved by the organization. Since values not listed here cannot be assigned, it also prevents custom granularities from proliferating in the field.

Prerequisites

• Snowflake Enterprise Edition or higher is required
• Use a role equivalent to ACCOUNTADMIN for creating tags and policies and granting roles
• Verification was conducted on a trial account (Enterprise Edition) in the AWS Tokyo region

Preparation

Create a dedicated database BLOG_MASK_DB so that everything is self-contained without affecting the existing environment.

USE ROLE ACCOUNTADMIN;
USE WAREHOUSE COMPUTE_WH;

CREATE OR REPLACE DATABASE BLOG_MASK_DB;
CREATE OR REPLACE SCHEMA   BLOG_MASK_DB.GOV;   -- Tags and policies
CREATE OR REPLACE SCHEMA   BLOG_MASK_DB.MART;  -- Verification data

CREATE OR REPLACE TAG BLOG_MASK_DB.GOV.DATA_CLASS
  ALLOWED_VALUES
    'PUBLIC',
    'UNCLASSIFIED',
    'PII',
    'PII_YEARMONTH',
    'PII_YEAR',
    'PII_PHONE_LAST4',
    'PII_EMAIL_DOMAIN',
    'PII_ADDR_CITY',
    'PII_AMOUNT_BAND'
  COMMENT = 'Tag representing partial masking granularity';

By specifying ALLOWED_VALUES, an error is raised at the point of attempting to assign a mistyped value.

CREATE OR REPLACE ROLE BLOG_PII_FULL_ROLE COMMENT = 'Privileged role that can see raw values';
CREATE OR REPLACE ROLE BLOG_ANALYST_ROLE  COMMENT = 'General role with partial masking applied';

GRANT USAGE ON WAREHOUSE COMPUTE_WH   TO ROLE BLOG_PII_FULL_ROLE;
GRANT USAGE ON WAREHOUSE COMPUTE_WH   TO ROLE BLOG_ANALYST_ROLE;
GRANT USAGE ON DATABASE  BLOG_MASK_DB TO ROLE BLOG_PII_FULL_ROLE;
GRANT USAGE ON DATABASE  BLOG_MASK_DB TO ROLE BLOG_ANALYST_ROLE;
GRANT USAGE ON SCHEMA    BLOG_MASK_DB.MART TO ROLE BLOG_PII_FULL_ROLE;
GRANT USAGE ON SCHEMA    BLOG_MASK_DB.MART TO ROLE BLOG_ANALYST_ROLE;

GRANT ROLE BLOG_PII_FULL_ROLE TO USER <verification username>;
GRANT ROLE BLOG_ANALYST_ROLE  TO USER <verification username>;

Note: In this article, judgment is made using CURRENT_ROLE(). CURRENT_ROLE() returns only the current primary role and does not account for role hierarchies or active secondary roles. To include role hierarchies and secondary roles in the evaluation, use IS_ROLE_IN_SESSION('BLOG_PII_FULL_ROLE').

Creating Masking Policies

Create one policy per data type. Branches are written in the order: "role check → PUBLIC check → branch by granularity → ELSE", with ELSE set to full masking. This ensures that a missing tag assignment falls to the "full mask" side rather than "raw value is exposed".

-- DATE
CREATE OR REPLACE MASKING POLICY BLOG_MASK_DB.GOV.MASK_DATE AS (VAL DATE)
RETURNS DATE ->
CASE
  WHEN CURRENT_ROLE() = 'BLOG_PII_FULL_ROLE' THEN VAL
  WHEN COALESCE(SYSTEM$GET_TAG_ON_CURRENT_COLUMN('BLOG_MASK_DB.GOV.DATA_CLASS'), 'UNCLASSIFIED') = 'PUBLIC'
    THEN VAL
  WHEN COALESCE(SYSTEM$GET_TAG_ON_CURRENT_COLUMN('BLOG_MASK_DB.GOV.DATA_CLASS'), 'UNCLASSIFIED') = 'PII_YEARMONTH'
    THEN DATE_TRUNC('MONTH', VAL)
  WHEN COALESCE(SYSTEM$GET_TAG_ON_CURRENT_COLUMN('BLOG_MASK_DB.GOV.DATA_CLASS'), 'UNCLASSIFIED') = 'PII_YEAR'
    THEN DATE_TRUNC('YEAR', VAL)
  ELSE DATE '1900-01-01'
END;

-- VARCHAR
CREATE OR REPLACE MASKING POLICY BLOG_MASK_DB.GOV.MASK_TEXT AS (VAL VARCHAR)
RETURNS VARCHAR ->
CASE
  WHEN CURRENT_ROLE() = 'BLOG_PII_FULL_ROLE' THEN VAL
  WHEN COALESCE(SYSTEM$GET_TAG_ON_CURRENT_COLUMN('BLOG_MASK_DB.GOV.DATA_CLASS'), 'UNCLASSIFIED') = 'PUBLIC'
    THEN VAL
  WHEN COALESCE(SYSTEM$GET_TAG_ON_CURRENT_COLUMN('BLOG_MASK_DB.GOV.DATA_CLASS'), 'UNCLASSIFIED') = 'PII_PHONE_LAST4'
    THEN IFF(LENGTH(VAL) >= 4, '****-****-' || RIGHT(VAL, 4), '***MASKED***')
  WHEN COALESCE(SYSTEM$GET_TAG_ON_CURRENT_COLUMN('BLOG_MASK_DB.GOV.DATA_CLASS'), 'UNCLASSIFIED') = 'PII_EMAIL_DOMAIN'
    THEN IFF(POSITION('@' IN VAL) > 0, '***@' || SPLIT_PART(VAL, '@', 2), '***MASKED***')
  WHEN COALESCE(SYSTEM$GET_TAG_ON_CURRENT_COLUMN('BLOG_MASK_DB.GOV.DATA_CLASS'), 'UNCLASSIFIED') = 'PII_ADDR_CITY'
    THEN IFF(POSITION('市' IN VAL) > 0, LEFT(VAL, POSITION('市' IN VAL)), '***MASKED***')
  ELSE '***MASKED***'
END;

-- NUMBER
CREATE OR REPLACE MASKING POLICY BLOG_MASK_DB.GOV.MASK_NUMBER AS (VAL NUMBER(38,2))
RETURNS NUMBER(38,2) ->
CASE
  WHEN CURRENT_ROLE() = 'BLOG_PII_FULL_ROLE' THEN VAL
  WHEN COALESCE(SYSTEM$GET_TAG_ON_CURRENT_COLUMN('BLOG_MASK_DB.GOV.DATA_CLASS'), 'UNCLASSIFIED') = 'PUBLIC'
    THEN VAL
  WHEN COALESCE(SYSTEM$GET_TAG_ON_CURRENT_COLUMN('BLOG_MASK_DB.GOV.DATA_CLASS'), 'UNCLASSIFIED') = 'PII_AMOUNT_BAND'
    THEN SIGN(VAL) * FLOOR(ABS(VAL) / 100000) * 100000
  ELSE 0
END;

Note that NULL handling differs by type and branch. DATE_TRUNC for DATE and SIGN/FLOOR for NUMBER propagate NULL and return NULL as-is, but for VARCHAR partial masking, when the IFF condition evaluates to NULL it proceeds to the else side and is replaced with ***MASKED***. Since the presence or absence of NULL itself can be an attribute, whether to preserve NULL or align it to a fixed value should be standardized as a data classification requirement for each type, rather than left to incidental behavior.

Attach all three to the same tag.

ALTER TAG BLOG_MASK_DB.GOV.DATA_CLASS SET MASKING POLICY BLOG_MASK_DB.GOV.MASK_DATE;
ALTER TAG BLOG_MASK_DB.GOV.DATA_CLASS SET MASKING POLICY BLOG_MASK_DB.GOV.MASK_TEXT;
ALTER TAG BLOG_MASK_DB.GOV.DATA_CLASS SET MASKING POLICY BLOG_MASK_DB.GOV.MASK_NUMBER;

Creating Verification Data and Assigning Tags

CREATE OR REPLACE TABLE BLOG_MASK_DB.MART.DIM_CUSTOMER (
  CUSTOMER_ID   NUMBER(10,0),
  CUSTOMER_NAME VARCHAR(100),
  BIRTH_EXACT   DATE,          -- Column assigned PUBLIC for comparison purposes (do not use in production)
  BIRTH_YM      DATE,
  BIRTH_Y       DATE,
  BIRTH_FULL    DATE,
  PHONE         VARCHAR(20),
  EMAIL         VARCHAR(100),
  ADDRESS       VARCHAR(200),
  BALANCE       NUMBER(38,2),
  REGION        VARCHAR(50)    -- Column intentionally left without a tag
);

INSERT INTO BLOG_MASK_DB.MART.DIM_CUSTOMER VALUES
  (1, 'Kawabata Taro',
   '2001-03-26', '2001-03-26', '2001-03-26', '2001-03-26',
   '090-1234-5678', 'taro.kawabata@example.co.jp', 'Shibuya, Jinnan 1-1-1, Tokyo',
   1234567.89, 'KANTO'),
  (2, 'Sato Hanako',
   '1987-11-04', '1987-11-04', '1987-11-04', '1987-11-04',
   '080-9876-5432', 'hanako@sample.org', 'Nishi-ku, Minatomirai 2-2-2, Yokohama, Kanagawa',
   -5000.00, 'KANTO');

Tag-based masking is applied to columns that have a tag assigned and for which a masking policy corresponding to the column's data type is set on the tag. Columns with missing tag assignments return raw values, so assign UNCLASSIFIED to the database to default to full masking. In this article, policies are set for DATE, VARCHAR, and NUMBER, so this default only takes effect for columns of these types.

ALTER DATABASE BLOG_MASK_DB SET TAG BLOG_MASK_DB.GOV.DATA_CLASS = 'UNCLASSIFIED';

Then, declare the granularity for each column. Column-level tag assignments take priority over inheritance from the database.

ALTER TABLE BLOG_MASK_DB.MART.DIM_CUSTOMER MODIFY
  COLUMN CUSTOMER_ID   SET TAG BLOG_MASK_DB.GOV.DATA_CLASS = 'PUBLIC',
  COLUMN CUSTOMER_NAME SET TAG BLOG_MASK_DB.GOV.DATA_CLASS = 'PII',
  COLUMN BIRTH_EXACT   SET TAG BLOG_MASK_DB.GOV.DATA_CLASS = 'PUBLIC',
  COLUMN BIRTH_YM      SET TAG BLOG_MASK_DB.GOV.DATA_CLASS = 'PII_YEARMONTH',
  COLUMN BIRTH_Y       SET TAG BLOG_MASK_DB.GOV.DATA_CLASS = 'PII_YEAR',
  COLUMN BIRTH_FULL    SET TAG BLOG_MASK_DB.GOV.DATA_CLASS = 'PII',
  COLUMN PHONE         SET TAG BLOG_MASK_DB.GOV.DATA_CLASS = 'PII_PHONE_LAST4',
  COLUMN EMAIL         SET TAG BLOG_MASK_DB.GOV.DATA_CLASS = 'PII_EMAIL_DOMAIN',
  COLUMN ADDRESS       SET TAG BLOG_MASK_DB.GOV.DATA_CLASS = 'PII_ADDR_CITY',
  COLUMN BALANCE       SET TAG BLOG_MASK_DB.GOV.DATA_CLASS = 'PII_AMOUNT_BAND';

GRANT SELECT ON TABLE BLOG_MASK_DB.MART.DIM_CUSTOMER TO ROLE BLOG_PII_FULL_ROLE;
GRANT SELECT ON TABLE BLOG_MASK_DB.MART.DIM_CUSTOMER TO ROLE BLOG_ANALYST_ROLE;

No tag is assigned to REGION. Check the policy application status with POLICY_REFERENCES.

SELECT REF_COLUMN_NAME AS "Column", POLICY_NAME AS "Policy", TAG_NAME AS "Via Tag"
FROM TABLE(BLOG_MASK_DB.INFORMATION_SCHEMA.POLICY_REFERENCES(
       REF_ENTITY_NAME   => 'BLOG_MASK_DB.MART.DIM_CUSTOMER',
       REF_ENTITY_DOMAIN => 'TABLE'))
ORDER BY 1;

Policies were applied to all 11 columns. REGION is covered only through inheritance from the database.

Note that CUSTOMER_ID is NUMBER(10,0), but MASK_NUMBER defined with NUMBER(38,2) was applied. This confirms that, at least among NUMBER types, an exact match of precision and scale is not required.

Whether each column's tag is directly assigned or inherited can be checked with TAG_REFERENCES_ALL_COLUMNS. The APPLY_METHOD column displays MANUAL (directly assigned) or INHERITED (inherited).

SELECT COLUMN_NAME, TAG_VALUE, APPLY_METHOD, LEVEL
FROM TABLE(BLOG_MASK_DB.INFORMATION_SCHEMA.TAG_REFERENCES_ALL_COLUMNS(
       'BLOG_MASK_DB.MART.DIM_CUSTOMER', 'TABLE'))
WHERE TAG_NAME = 'DATA_CLASS'
ORDER BY COLUMN_NAME;

Let's Try It

Display with the privileged role

USE ROLE BLOG_PII_FULL_ROLE;
SELECT CURRENT_ROLE() AS "Role", CUSTOMER_ID, CUSTOMER_NAME, BIRTH_EXACT,
       PHONE, EMAIL, ADDRESS, BALANCE, REGION
FROM BLOG_MASK_DB.MART.DIM_CUSTOMER
ORDER BY CUSTOMER_ID;

All columns show raw values as expected.

Comparing date of birth across 4 granularity levels

USE ROLE BLOG_ANALYST_ROLE;

SELECT CUSTOMER_ID,
       BIRTH_EXACT AS "Raw value",
       BIRTH_YM    AS "Up to year-month",
       BIRTH_Y     AS "Up to year",
       BIRTH_FULL  AS "Full mask"
FROM BLOG_MASK_DB.MART.DIM_CUSTOMER
ORDER BY CUSTOMER_ID;

2001-03-26 became 2001-03-01, with only the day suppressed while the year and month are retained. This is the result of the same table, same query, and same role — only the tag values assigned to the columns differ.

Partial masking of strings and numbers

SELECT CUSTOMER_ID,
       CUSTOMER_NAME AS "Name (PII)",
       PHONE, EMAIL, ADDRESS, BALANCE,
       REGION        AS "REGION (no tag assigned)"
FROM BLOG_MASK_DB.MART.DIM_CUSTOMER
ORDER BY CUSTOMER_ID;

There are three things to note.

• The address in the first row fell to full masking. Since 東京都渋谷区神南1-1-1 does not contain "市", the process of retaining up to "市" could not be performed and it safely fell through (see Verification Point 1 for details)
• REGION has no tag assigned, but it was masked through inheritance of UNCLASSIFIED assigned to the database
• The balance in the second row is 0.00 against the raw value of -5000.00

Behavior with ACCOUNTADMIN

USE ROLE ACCOUNTADMIN;

SELECT CURRENT_ROLE() AS "Role", CUSTOMER_NAME, BIRTH_YM, PHONE, BALANCE
FROM BLOG_MASK_DB.MART.DIM_CUSTOMER
ORDER BY CUSTOMER_ID;

Even ACCOUNTADMIN sees masked values. Masking policies are not bypassed by privilege level — only the roles written in the policy's CASE expression can access raw values.

Changing granularity by reassigning the tag

Without touching the policy, change only the tag value.

USE ROLE ACCOUNTADMIN;
ALTER TABLE BLOG_MASK_DB.MART.DIM_CUSTOMER MODIFY
  COLUMN BIRTH_YM SET TAG BLOG_MASK_DB.GOV.DATA_CLASS = 'PII_YEAR';

USE ROLE BLOG_ANALYST_ROLE;
SELECT CUSTOMER_ID, BIRTH_YM FROM BLOG_MASK_DB.MART.DIM_CUSTOMER ORDER BY CUSTOMER_ID;

The column that was 2001-03-01 is now 2001-01-01. Since granularity changes with just a single ALTER TABLE ... SET TAG statement, policy reviews become unnecessary for granularity change requests.

After confirming, revert to PII_YEARMONTH since subsequent verification uses year-month granularity.

USE ROLE ACCOUNTADMIN;
ALTER TABLE BLOG_MASK_DB.MART.DIM_CUSTOMER MODIFY
  COLUMN BIRTH_YM SET TAG BLOG_MASK_DB.GOV.DATA_CLASS = 'PII_YEARMONTH';

Verification Point 1: In implementations that retain the prefix when no delimiter is found, the full value is exposed

For the process of truncating an address to "市" (city), the first thing that comes to mind is an implementation that splits on "市" and takes the first part.

SPLIT_PART(ADDR, '市', 1) || '市'

Verify this with boundary values.

WITH T AS (
  SELECT '神奈川県横浜市西区みなとみらい2-2-2' AS ADDR UNION ALL
  SELECT '東京都渋谷区神南1-1-1'                       UNION ALL
  SELECT '大阪府大阪市北区梅田3-3-3'                   UNION ALL
  SELECT ''                                            UNION ALL
  SELECT NULL
)
SELECT
  COALESCE(ADDR, '(NULL)')          AS "Original value",
  SPLIT_PART(ADDR, '市', 1) || '市' AS "Dangerous implementation",
  IFF(POSITION('市' IN ADDR) > 0,
      LEFT(ADDR, POSITION('市' IN ADDR)),
      '***MASKED***')               AS "Safe implementation"
FROM T;

In the second row, the full address including the street number was output. This is because SPLIT_PART returns the entire input as the first element when the delimiter is not found. Since Tokyo's 23 ward addresses do not contain "市", the entire untruncated string ends up concatenated with "市".

Note: SPLIT_PART / SUBSTR / REGEXP_SUBSTR do not return errors when a delimiter or pattern is not found. When implementing partial masking, always check the output for inputs that do not contain the delimiter. To err on the side of safety, use POSITION to verify the existence of the delimiter and fall back to full masking if it is not found.

Note that the "safe implementation" is also a simplified version that only retains up to the first "市" and does not handle designated city wards, counties, towns, villages, or variant spellings. For accurate handling at the municipality level, a practical approach is to prepare separate columns with normalized prefecture and municipality data and set granularity on those columns.

Boundary values for phone numbers and email addresses

Check the behavior of email addresses, which also use SPLIT_PART like addresses.

WITH T AS (
  SELECT '090-1234-5678' AS PHONE, 'taro.kawabata@example.co.jp' AS EMAIL UNION ALL
  SELECT '123',            'no-at-sign-here'                              UNION ALL
  SELECT '',               ''                                            UNION ALL
  SELECT NULL,             NULL
)
SELECT
  COALESCE(PHONE, '(NULL)') AS "Phone_original",
  IFF(LENGTH(PHONE) >= 4, '****-****-' || RIGHT(PHONE, 4), '***MASKED***') AS "Phone_masked",
  COALESCE(EMAIL, '(NULL)') AS "Email_original",
  '***@' || SPLIT_PART(EMAIL, '@', 2) AS "Email_dangerous",
  IFF(POSITION('@' IN EMAIL) > 0, '***@' || SPLIT_PART(EMAIL, '@', 2), '***MASKED***') AS "Email_safe"
FROM T;

For the email address "dangerous implementation," the full value was not output — instead it became ***@. The difference from addresses lies in which part after the delimiter is retained.

• The address retains the 1st part with SPLIT_PART(..., 1), and when there is no delimiter, the 1st part is the entire string
• The email address retains the 2nd part with SPLIT_PART(..., 2), and when there is no delimiter, the 2nd part is an empty string

The problem is not SPLIT_PART itself, but implementations that retain the prefix. Partial masking that retains the prefix — such as addresses, names, postal codes, or employee number prefixes — behaves the same way and should be considered a priority review target.

Can columns be used in GROUP BY and JOIN after masking?

Masking policies are applied at query execution time to wherever masked columns are referenced. Not only in SELECT results, but also in JOIN conditions, WHERE, GROUP BY, ORDER BY — general roles evaluate the masked values. Add two more tables to verify.

USE ROLE ACCOUNTADMIN;

-- Purchase fact (join key and amount are non-PII, set as PUBLIC)
CREATE OR REPLACE TABLE BLOG_MASK_DB.MART.FCT_PURCHASE (
  PURCHASE_ID     NUMBER(10,0),
  CUSTOMER_ID     NUMBER(10,0),
  PURCHASE_AMOUNT NUMBER(38,2)
);

INSERT INTO BLOG_MASK_DB.MART.FCT_PURCHASE VALUES
  (101, 1, 12000.00),
  (102, 1,  3500.00),
  (103, 2,  8000.00),
  (104, 2,  1500.00),
  (105, 2,   700.00);

ALTER TABLE BLOG_MASK_DB.MART.FCT_PURCHASE MODIFY
  COLUMN PURCHASE_ID     SET TAG BLOG_MASK_DB.GOV.DATA_CLASS = 'PUBLIC',
  COLUMN CUSTOMER_ID     SET TAG BLOG_MASK_DB.GOV.DATA_CLASS = 'PUBLIC',
  COLUMN PURCHASE_AMOUNT SET TAG BLOG_MASK_DB.GOV.DATA_CLASS = 'PUBLIC';

-- Contact list (mixing in another person's address with the example.co.jp domain)
CREATE OR REPLACE TABLE BLOG_MASK_DB.MART.MKT_CONTACT (
  CONTACT_ID NUMBER(10,0),
  EMAIL      VARCHAR(100)
);

INSERT INTO BLOG_MASK_DB.MART.MKT_CONTACT VALUES
  (901, 'taro.kawabata@example.co.jp'),   -- Exact match with customer 1
  (902, 'another-user@example.co.jp'),    -- Different person but same domain
  (903, 'hanako@sample.org');             -- Exact match with customer 2

ALTER TABLE BLOG_MASK_DB.MART.MKT_CONTACT MODIFY
  COLUMN CONTACT_ID SET TAG BLOG_MASK_DB.GOV.DATA_CLASS = 'PUBLIC',
  COLUMN EMAIL      SET TAG BLOG_MASK_DB.GOV.DATA_CLASS = 'PII_EMAIL_DOMAIN';

GRANT SELECT ON TABLE BLOG_MASK_DB.MART.FCT_PURCHASE TO ROLE BLOG_PII_FULL_ROLE;
GRANT SELECT ON TABLE BLOG_MASK_DB.MART.FCT_PURCHASE TO ROLE BLOG_ANALYST_ROLE;
GRANT SELECT ON TABLE BLOG_MASK_DB.MART.MKT_CONTACT  TO ROLE BLOG_PII_FULL_ROLE;
GRANT SELECT ON TABLE BLOG_MASK_DB.MART.MKT_CONTACT  TO ROLE BLOG_ANALYST_ROLE;

Since the database-level UNCLASSIFIED inheritance applies, DATE, VARCHAR, and NUMBER columns for which a policy is set will fall to full masking if no tag is assigned. Columns used as join keys and aggregation targets need to be explicitly declared as PUBLIC.

First, GROUP BY. MKT_CONTACT has data where all 3 raw values differ, with 2 of them sharing the same domain. If aggregation is performed on masked values, these 2 rows should merge into 1 group.

USE ROLE BLOG_ANALYST_ROLE;
SELECT EMAIL AS "Email (after masking)", COUNT(*) AS "Count"
FROM BLOG_MASK_DB.MART.MKT_CONTACT
GROUP BY EMAIL
ORDER BY 1;

The 2 rows with different raw values merged into 1 group as ***@example.co.jp. This is evidence that GROUP BY is evaluated on masked values. Running the same query with the privileged role returns 3 groups × 1 row each using the raw values.

Next, JOIN. If the join key (CUSTOMER_ID) is PUBLIC, the join works as expected with raw values, and can be combined with aggregation on masked dimension attributes.

SELECT YEAR(D.BIRTH_Y) - MOD(YEAR(D.BIRTH_Y), 10) AS "Decade",
       COUNT(DISTINCT D.CUSTOMER_ID)              AS "Customer count",
       COUNT(P.PURCHASE_ID)                       AS "Purchase count",
       SUM(P.PURCHASE_AMOUNT)                     AS "Total purchase amount"
FROM BLOG_MASK_DB.MART.DIM_CUSTOMER D
JOIN BLOG_MASK_DB.MART.FCT_PURCHASE P
  ON D.CUSTOMER_ID = P.CUSTOMER_ID
GROUP BY 1
ORDER BY 1;

The main use case for partial masking — "hide the date of birth, but enable decade-based purchase analysis" — works even across a JOIN.

On the other hand, joining on masked columns causes false matches. Try joining customers and contacts on EMAIL.

SELECT D.CUSTOMER_ID, D.EMAIL AS "Customer EMAIL (after masking)",
       C.CONTACT_ID,  C.EMAIL AS "Contact EMAIL (after masking)"
FROM BLOG_MASK_DB.MART.DIM_CUSTOMER D
JOIN BLOG_MASK_DB.MART.MKT_CONTACT C
  ON D.EMAIL = C.EMAIL
ORDER BY D.CUSTOMER_ID, C.CONTACT_ID;

Customer 1 also joined with contact 902, which belongs to a different person. Since both values are collapsed to ***@example.co.jp, they match simply because they share the same domain. Running the same query with the privileged role returns only the 2 rows that are exact matches on raw values — 901 and 903.

If the columns on both sides are fully masked, all rows are collapsed to ***MASKED***, resulting in a cross join. Do not use masked columns as JOIN keys or as the basis for COUNT(DISTINCT ...). Design your schema to join using surrogate keys that can be set to PUBLIC.

Limitations and Notes

Here is a summary of the limitations and notes confirmed through verification and official documentation.

Closing

By configuring granularity switching based on tag values, only one policy per data type is needed, and operations are reduced to simply assigning tags to columns. Since it allows age-range analysis to remain functional while avoiding direct exposure of birthdates, it can reduce the number of requests to lift masking in the first place.
On the other hand, partial masking is a process that retains values. Errors in what is retained—such as full exposure of input without delimiters, or incorrect matches in JOINs between masked columns—can change the meaning of results, so it is advisable to verify boundary values before implementation.

I hope this article serves as a helpful reference for someone!