3
0
Tag, Mask, Protect: PII Handling in Snowflake is no longer optional—it’s essential. As organizations grow, data governance at scale becomes critical — especially when handling personally identifiable information (PII). Manual tagging and masking do not scale across thousands of tables and columns. Imagine a global logistics company ingesting real-time shipment tracking data that includes:
- Customer contact details (email, phone, name, address)
- Geolocation (latitude, longitude)
- Shipment tracking info (shipment_id, status, timestamp)
These fields require proper classification as IDENTIFIER or QUASI_IDENTIFIER for internal privacy compliance.
In this post, I’m excited to share a real-time, metadata-driven framework for:
- Automatically detecting and classifying PII columns
- Dynamically applying Snowflake tags
- Enforcing masking policies using tags — all without hardcoding
This reusable framework is built 100% on native Snowflake capabilities using JavaScript stored procedures.
Solution Overview
At a high level, this Snowflake-native pipeline does the following:
- Lineage Metadata Table: Capture column-level PII classification info (LOGISTICS_STG.COLUMN_LINEAGE_INFO).
- PII Tag Catalog: Central repository storing tagging decisions (PII_TAG_CATALOG).
- Auto-Tagging Procedure: JavaScript stored procedure that reads lineage info, maps categories to tags, and populates the catalog.
- Tag Application Procedure: Another JS procedure that applies Snowflake tags to actual columns from the catalog.
- Dynamic Masking Policy: Defines masking logic based on applied tags to protect sensitive data for unauthorized roles.
- Masking Application Procedure: Applies the masking policy to all tagged sensitive columns dynamically.
Technical Walkthrough (In Brief)
- Lineage Table: Capture detected PII categories for all columns as they arrive.
- Tag Catalog: Store tagging decisions centrally for audit and control.
- Dynamic Tagging Procedure: Uses a JS map to classify and insert tags based on lineage.
- Apply Tags: Automate Snowflake tags on columns from the catalog.
- Dynamic Masking: Mask sensitive columns for unauthorized users, driven by tags.
- Enforce Masking: Apply masking policies to tagged columns dynamically.
Implementation:
Source Table:
2.TAG CATALOG: This table acts as the single source of truth for data privacy tagging.
CREATE OR REPLACE TABLE PII_TAG_CATALOG (
database_name STRING,
schema_name STRING,
table_name STRING,
column_name STRING,
tag_name STRING,
tag_value STRING,
classification STRING,
category STRING,
environment STRING,
load_dts TIMESTAMP
);
CALL SP_AUTO_TAG_PII_COLUMNS_JS(‘PROD’);
call APPLY_TAGS_FROM_CATALOG();
Verify in Snowflake if Tag has been applied:
5.Dynamic Masking: Mask sensitive columns for unauthorized users, driven by tags
CREATE OR REPLACE MASKING POLICY MASK_PII_DYNAMIC
AS (val STRING) RETURNS STRING ->
CASE
WHEN SYSTEM$GET_TAG_ON_CURRENT_COLUMN('PRIVACY_CATEGORY') IN ('IDENTIFIER', 'QUASI_IDENTIFIER')
AND CURRENT_ROLE() NOT IN ('ACCOUNTADMIN')
THEN '********'
ELSE val
END;
call APPLY_MASKING_POLICY_TO_TAGGED_COLUMNS();
Verify with Roles:
We’ve implemented an automated privacy tagging and masking system in Snowflake. It identifies sensitive fields like customer email, phone, and address using metadata, classifies them, tags them appropriately, and then applies dynamic masking policies. This ensures only authorized users can see PII, while others get masked values — helping us stay compliant with data protection regulations and internal security policies.
