If the transformation is permanent (i.e. the original data is fine to be lost), CREATE TABLE AS SELECT (CTAS) or INSERT SELECT would be a good option.
If you want to keep the original data but want to mask sensitive data only for specific people, you can leverage the Dynamic Data Masking feature:
Understanding Dynamic Data Masking — Snowflake Documentation:
https://docs.snowflake.com/en/user-guide/security-column-ddm-intro.html
Dynamic Data Masking is a feature allowing you to apply dynamic transformations with a query or a UDF to a table based on the current context (account, user, role, tag, etc.) by attaching a "Masking Policy" that you can arbitrarily define.
Below are examples of how you can mask the data with CTAS and Dynamic Data Masking.
Sample data/masking UDF preparation
create or replace table data (
first_name varchar,
last_name varchar,
email varchar,
address varchar,
address2 varchar,
address3 varchar,
dob varchar
);
insert into data values
('Mike', 'Johnson', 'mike@gmail.com', '1650 Jackson St', 'Apt 704', null, '09/10'),
('John', 'Dunn', 'john101@gmail.com', '320 Day St', null, null, '01/20'),
('Bill', 'Rinitin', 'rinitin@gmail.com', '108 Marietta Dr', null, null, '04/11'),
('Chris', 'Talbot', 'chris2@gmail.com', '401 Harrison St', null, null, '07/17'),
('Teresa', 'Middleton', 'teresa.middleton@yahoo.com', '181 Fremont St', 'Unit 62C', null, '11/21'),
;
create or replace function udf_mask_name (name varchar)
returns varchar
language sql
as $$
select left(name, 1) || repeat('*', len(name)-1)
$$;
create or replace function udf_mask_email (email varchar)
returns varchar
language sql
as $$
select left(split_part(email, '@', 1), 1) ||
repeat('*', len(split_part(email, '@', 1))-1) ||
'@' ||
split_part(email, '@', 2)
$$;
create or replace function udf_mask_dob (dob varchar)
returns varchar
language sql
as $$
select split_part(dob, '/', 1) ||
'/' ||
repeat('*', len(split_part(dob, '/', 2)))
$$;
Example 1: CTAS
create or replace table data_masked (
first_name varchar,
last_name varchar,
email varchar,
address varchar,
address2 varchar,
address3 varchar,
dob varchar
) as
select
udf_mask_name(first_name),
udf_mask_name(last_name),
udf_mask_email(email),
address, address2, address3,
udf_mask_dob(dob)
from data
;
select * from data_masked;
/*
FIRST_NAME LAST_NAME EMAIL ADDRESS ADDRESS2 ADDRESS3 DOB
M*** J****** m***@gmail.com 1650 Jackson St Apt 704 09/**
J*** D*** j******@gmail.com 320 Day St 01/**
B*** R****** r******@gmail.com 108 Marietta Dr 04/**
C**** T***** c*****@gmail.com 401 Harrison St 07/**
T***** M******** t***************@yahoo.com 181 Fremont St Unit 62C 11/**
*/
Example 2: Dynamic Data Masking
create or replace masking policy mp_mask_name as
(val varchar) returns varchar ->
case
when current_role() = 'ACCOUNTADMIN' then val
else udf_mask_name(val)
end
;
create or replace masking policy mp_mask_email as
(val varchar) returns varchar ->
case
when current_role() = 'ACCOUNTADMIN' then val
else udf_mask_email(val)
end
;
create or replace masking policy mp_mask_dob as
(val varchar) returns varchar ->
case
when current_role() = 'ACCOUNTADMIN' then val
else udf_mask_dob(val)
end
;
alter table data modify column first_name set masking policy mp_mask_name;
alter table data modify column last_name set masking policy mp_mask_name;
alter table data modify column email set masking policy mp_mask_email;
alter table data modify column dob set masking policy mp_mask_dob;
use role accountadmin;
select * from data;
/*
FIRST_NAME LAST_NAME EMAIL ADDRESS ADDRESS2 ADDRESS3 DOB
Mike Johnson mike@gmail.com 1650 Jackson St Apt 704 09/10
John Dunn john101@gmail.com 320 Day St 01/20
Bill Rinitin rinitin@gmail.com 108 Marietta Dr 04/11
Chris Talbot chris2@gmail.com 401 Harrison St 07/17
Teresa Middleton teresa.middleton@yahoo.com 181 Fremont St Unit 62C 11/21
*/
use role nonadminrole;
select * from data;
/*
FIRST_NAME LAST_NAME EMAIL ADDRESS ADDRESS2 ADDRESS3 DOB
M*** J****** m***@gmail.com 1650 Jackson St Apt 704 09/**
J*** D*** j******@gmail.com 320 Day St 01/**
B*** R****** r******@gmail.com 108 Marietta Dr 04/**
C**** T***** c*****@gmail.com 401 Harrison St 07/**
T***** M******** t***************@yahoo.com 181 Fremont St Unit 62C 11/**
*/
