BankOrg Data Platform / Data Engineering
PII Masking
Tag-based dynamic data masking of the PII columns: the PII tag, two masking policies, the RAW and staging columns tagged and the role that sees clear values.
The PII columns of the core banking data are masked with Snowflake dynamic data masking (Enterprise edition). A tag, PII, marks a column, and two masking policies linked to the tag mask it for every role except DBT_ROLE and PII_READER_ROLE.
Create the role
Create PII_READER_ROLE as SECURITYADMIN and grant it to SYSADMIN. It is the role that sees PII in clear.
USE ROLE SECURITYADMIN;
CREATE ROLE IF NOT EXISTS PII_READER_ROLE;
GRANT ROLE PII_READER_ROLE TO ROLE SYSADMIN;Create the tag
Create the database PRD_GOVERNANCE, the schema MASKING and the tag PII as SYSADMIN. The tag value selects the masking style, and ALLOWED_VALUES rejects any other value.
USE ROLE SYSADMIN;
CREATE DATABASE IF NOT EXISTS PRD_GOVERNANCE;
CREATE SCHEMA IF NOT EXISTS PRD_GOVERNANCE.MASKING;
USE SCHEMA PRD_GOVERNANCE.MASKING;
CREATE TAG IF NOT EXISTS PII
ALLOWED_VALUES 'email', 'identifier', 'text', 'date'
COMMENT = 'PII column; the value selects the masking style (see policies MASK_PII_STRING / MASK_PII_DATE)';| Tag value | Masked value | Example |
|---|---|---|
| ***@ and the domain | nam.phan263@example.com becomes ***@example.com | |
| identifier | All characters hidden except the last 3 | 0992880321 becomes *******321 |
| text | *** | Quoc Nam becomes *** |
| date | Shifted by 1 to 365 days, forward or back | A date of birth moves within a year of the real date |
Create the policies
One policy per data type. MASK_PII_STRING covers the string columns of RAW and staging and reads the tag value on the column to pick the style. MASK_PII_DATE covers real DATE columns. A NULL stays NULL, and an unknown tag value on a string column returns ***.
CREATE MASKING POLICY IF NOT EXISTS MASK_PII_STRING AS (val STRING) RETURNS STRING ->
CASE
WHEN val IS NULL THEN NULL
WHEN CURRENT_ROLE() = 'DBT_ROLE' OR IS_ROLE_IN_SESSION('PII_READER_ROLE') THEN val
WHEN SYSTEM$GET_TAG_ON_CURRENT_COLUMN('PRD_GOVERNANCE.MASKING.PII') = 'email'
THEN '***@' || SPLIT_PART(val, '@', 2)
WHEN SYSTEM$GET_TAG_ON_CURRENT_COLUMN('PRD_GOVERNANCE.MASKING.PII') = 'identifier'
THEN REPEAT('*', GREATEST(LENGTH(val) - 3, 0)) || RIGHT(val, 3)
WHEN SYSTEM$GET_TAG_ON_CURRENT_COLUMN('PRD_GOVERNANCE.MASKING.PII') = 'date'
THEN COALESCE(TO_CHAR(DATEADD(day,
(MOD(ABS(HASH(TRY_TO_DATE(val), 'bankorg-dob-shift')), 365) + 1)
* IFF(MOD(ABS(HASH(TRY_TO_DATE(val), 'bankorg-dob-sign')), 2) = 0, 1, -1),
TRY_TO_DATE(val))), '***')
ELSE '***'
END;CREATE MASKING POLICY IF NOT EXISTS MASK_PII_DATE AS (val DATE) RETURNS DATE ->
CASE
WHEN val IS NULL THEN NULL
WHEN CURRENT_ROLE() = 'DBT_ROLE' OR IS_ROLE_IN_SESSION('PII_READER_ROLE') THEN val
ELSE DATEADD(day,
(MOD(ABS(HASH(val, 'bankorg-dob-shift')), 365) + 1)
* IFF(MOD(ABS(HASH(val, 'bankorg-dob-sign')), 2) = 0, 1, -1),
val)
END;Date of birth
DATE_OF_BIRTH is a VARCHAR in RAW, so the string policy masks it there, and the date policy masks it in staging, where it is a DATE. Both shift the date by 1 to 365 days, forward or back. The shift comes from a salted HASH of the date, so the same date gives the same masked date in RAW and in staging and on every query. A value that is not a date returns ***.
Link the policies to the tag
Link both policies to the tag. DBT_ROLE gets USAGE on the database and schema and APPLY on the tag, which is all it needs to tag the columns of its models.
ALTER TAG PII SET MASKING POLICY MASK_PII_STRING;
ALTER TAG PII SET MASKING POLICY MASK_PII_DATE;
GRANT USAGE ON DATABASE PRD_GOVERNANCE TO ROLE DBT_ROLE;
GRANT USAGE ON SCHEMA PRD_GOVERNANCE.MASKING TO ROLE DBT_ROLE;
GRANT APPLY ON TAG PRD_GOVERNANCE.MASKING.PII TO ROLE DBT_ROLE;Tag the RAW columns
The PII columns of the RAW tables carry the tag in the CREATE TABLE statements.
CREATE TABLE IF NOT EXISTS CUSTOMERS (
CUSTOMER_ID VARCHAR,
FIRST_NAME VARCHAR WITH TAG (PRD_GOVERNANCE.MASKING.PII = 'text'),
LAST_NAME VARCHAR WITH TAG (PRD_GOVERNANCE.MASKING.PII = 'text'),
DATE_OF_BIRTH VARCHAR WITH TAG (PRD_GOVERNANCE.MASKING.PII = 'date'),
NATIONAL_ID VARCHAR WITH TAG (PRD_GOVERNANCE.MASKING.PII = 'identifier'),
EMAIL VARCHAR WITH TAG (PRD_GOVERNANCE.MASKING.PII = 'email'),
PHONE VARCHAR WITH TAG (PRD_GOVERNANCE.MASKING.PII = 'identifier'),
ADDRESS_LINE VARCHAR WITH TAG (PRD_GOVERNANCE.MASKING.PII = 'text'),
...
);The tag references of the three tables list nine columns:
| Table | Column | Tag value |
|---|---|---|
| PRD_RAW.CORE_BANKING.CUSTOMERS | FIRST_NAME | text |
| PRD_RAW.CORE_BANKING.CUSTOMERS | LAST_NAME | text |
| PRD_RAW.CORE_BANKING.CUSTOMERS | DATE_OF_BIRTH | date |
| PRD_RAW.CORE_BANKING.CUSTOMERS | NATIONAL_ID | identifier |
| PRD_RAW.CORE_BANKING.CUSTOMERS | ||
| PRD_RAW.CORE_BANKING.CUSTOMERS | PHONE | identifier |
| PRD_RAW.CORE_BANKING.CUSTOMERS | ADDRESS_LINE | text |
| PRD_RAW.CORE_BANKING.ACCOUNTS | ACCOUNT_NUMBER | identifier |
| PRD_RAW.CORE_BANKING.TRANSACTIONS | COUNTERPARTY_ACCOUNT | identifier |
Tag the staging columns with dbt
dbt replaces the staging views on every run, and a replaced view loses its tags. The macro apply_pii_masking sets the tag again on every column marked pii in the model yml, and it runs as a post-hook of the staging models.
{% macro apply_pii_masking() -%}
{%- if execute -%}
{%- set object_type = 'view' if config.get('materialized') == 'view' else 'table' -%}
{%- for column in model.columns.values() -%}
{%- set kind = (column.config.meta or column.meta or {}).get('pii') -%}
{%- if kind -%}
alter {{ object_type }} {{ this }} modify column {{ adapter.quote(column.name | upper) }}
set tag PRD_GOVERNANCE.MASKING.PII = '{{ kind }}';
{%- endif -%}
{%- endfor -%}
{%- endif -%}
{%- endmacro %}models:
bankorg:
staging:
core_banking:
+materialized: view
+database: PRD_STAGING
+schema: CORE_BANKING
+post-hook: "{{ apply_pii_masking() }}"The same nine columns are marked in _core_banking__models.yml, with the value of the tag under config: meta: pii.
- name: email
description: Email address (PII, masked for roles other than DBT_ROLE and PII_READER_ROLE).
config:
meta: {pii: email}Check the masking in RAW
To check the masking I created ANALYST_USER with the role ANALYST_ROLE. The role can read every table and view in PRD_RAW and PRD_STAGING, is not PII_READER_ROLE and does not inherit it, and the user has no secondary roles.
Logged in as ANALYST_USER, I queried the customers in RAW.
SELECT first_name, date_of_birth, national_id, email, phone
FROM PRD_RAW.CORE_BANKING.CUSTOMERS LIMIT 3;