Snowflake provides a straightforward but not well documented function that allows you to mask and protect fields. When you have analysts working with PII or other sensitive data points, there is rarely a purpose to make those data points available. For analytics, you may simply suppress access to the column.
There are other scenarios where you may need access to all columns but simply mask the data.
Step 1: Create a Masking Policy
As an example, birthdate may be PII that you want to suppress. This example truncates the birth date to a birth year for all user roles except ACCOUNTADMIN:
create or replace masking policy "Mask_TruncateDateTime" as (val date) returns date->
case
when current_role() in ('ACCOUNTADMIN') then val
else DATE_TRUNC( 'year',val )
end COMMENT = 'Truncate dates for year';Functions Explained
To break down the previous function we are creating a policy called “Mask_TruncateDateTime” that can be applied to a date field. If it were a datetime, you would simply change the masking policy to datetime.
The masking policy is applied to all roles except “ACCOUNTADMIN”. In other words if you are logged in as ACCOUNTADMIN, you see the actual birthdate. All other roles would see the birth year formatted as a date.
The comment is very important to ensure there is some record for what the masking policy does and why it exists.
Example: SSN
Another example is SSN. Here is another masking policy that will mask a social security number with * while maintaining the correct format.
create or replace masking policy "Mask_SSN" as (val string) returns string ->
case
when current_role() in ('ACCOUNTADMIN') then val
else '***-**-****'
end COMMENT = 'Format the SSN as text with *';Step 2: Apply a Masking Policy to a Column
With the masking policy created, now you need to apply that policy to a column.
alter table if exists GG_DEVELOPMENT_OPPS.SFDC."Lead" modify column "LastModifiedDate" SET masking policy "Mask_TruncateDateTime";Managing Masking Policies
The masking policy functions work well, but in my experience they can get out of control fast if you don’t keep track where your policies are applied. I keep a document that includes source code for every masking policy in existence and the business purpose. Masking poli First, to see what Masking policies exist you can use the following SQL:
show masking policies;Un-Apply the Masking Policy to a Column
To reverse course and remove a masking policy from a single column, you can see an example here where for a given column we unset the masking policy.
alter table if exists GG_DEVELOPMENT_OPPS.SFDC."Lead" modify column "LastModifiedDate" UNSET masking policy;Drop a Masking Policy
drop masking policy "Mask_TruncateDateTime";Do you have other tricks for making masking policies easier to manage and implement? Share your findings and I am happy to keep this article up to date.