Snowflake Dynamic Data Masking

Dynamic Data Masking is a column-level security feature that uses masking policies to selectively mask data at query time that was previously loaded in plain-text into Snowflake.

Using Dynamic Data Masking

CREATE ROLE if not exists masking_admin;
grant role masking_admin to user mask_user;
-- grant privileges to masking_admin role.
grant create masking policy on schema US_STAGE to role masking_admin;
grant create masking policy on schema US to role masking_admin;
grant apply masking policy on account to role masking_admin;
-- allow table_owner role to set or unset the ssn_mask masking policy (optional)
grant apply on masking policy company_id_mask to role LOADER;

-- create masking policy
create or replace masking policy company_id_mask as (val number) returns number ->
  case
    when current_role() in ('LOADER') then val
    else '00000000'
  end;
  
  use role loader;
  
  create table stage.companies
  as select * from us.companies limit 100000;
  
  select * from db_raw.stage.companies;
  
  alter table db_raw.stage.companies 
  modify column id 
  set masking policy company_id_mask;