snowflake column masking policies for agents

TL;DR

If an agent needs to explore real tables but shouldn't see real PII, Snowflake masking policies are the clean answer: you attach a policy to a column that returns the real value for privileged roles and a masked value (hash, partial, or NULL) for everyone else. The agent gets a realistic schema and real distributions to work with, but emails and names come back as ***. Set it once on the column and it applies to every query path, including the ones agents use.

The query

snowflake column masking policies for agents

Use this when

• a data agent needs read access to production tables containing PII
• analysts and agents share tables but should see different levels of detail
• you want masking enforced in the warehouse, not trusted to each client

Not for

• replacing role-based access entirely (masking complements grants, it does not replace them)
• warehouses other than Snowflake (the syntax here is Snowflake-specific)
• hiding data from account admins (they can always see through policies)

Steps

1. Identify the sensitive columns. Start with the obvious PII: email, full name, phone, address, government IDs. Document the list; you will reuse it across tables.

Expected output: a short list of columns per table that need masking.

1. Create the masking policy. The standard pattern returns the real value for privileged roles and a masked value otherwise:

CREATE OR REPLACE MASKING POLICY email_mask AS (val STRING)
RETURNS STRING ->
  CASE
    WHEN CURRENT_ROLE() IN ('PII_READER') THEN val
    ELSE '***masked***'
  END;

Expected output: the policy exists and DESCRIBE MASKING POLICY email_mask shows the definition.

1. Attach it to the columns with ALTER TABLE ... MODIFY COLUMN ... SET MASKING POLICY. One policy can attach to many columns of the same type.

Expected output: SHOW COLUMNS or the table DDL shows the policy attached.

1. Create the agent role with SELECT on the tables but without the privileged PII role, then query as that role to verify masking.

Expected output: querying as the agent role returns masked values; as PII_READER it returns real ones.

1. Decide on the masked shape per column type. Emails often keep the domain (***@example.com) so joins still behave; IDs can be hashed deterministically so the agent can still group by them without seeing the original.

Expected output: the agent can run realistic analysis (group-bys, joins on hashed keys) while PII stays hidden.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_InBWnK7WS93nprmOLh68-w