AdvancedLast updated: 2026-04-09 • 3 sections
Expert questions on dynamic data masking, row access policies, tag-based governance, ACCESS_HISTORY, and data protection at scale.
Q: How do masking policies work vs encryption?
Masking: per-query role-based SQL transform on columns. CASE WHEN CURRENT_ROLE() IN (PII_ADMIN) THEN val ELSE masked END. Encryption: at-rest, same encrypted value for all users. Masking supports partial masking, hashing, tokenization. Snowflake encrypts all data by default anyway.
Q: Tag-based masking and scaling?
Assign masking policy to a TAG instead of individual columns. 500 tables, 2000 PII columns = 3-4 tag-level policies vs 2000 column-level assignments. New PII column? Just tag it. Query TAG_REFERENCES for full PII inventory across account.
Q: Engineer needs unmasked for ETL, masked for ad-hoc?
Options: (1) Role-based: ETL_ROLE unmasked, AD_HOC_ROLE masked via CURRENT_ROLE(). (2) Warehouse-based: ETL_WH unmasked, ADHOC_WH masked. (3) IS_ROLE_IN_SESSION() for full hierarchy check. Never use CURRENT_USER() for access control.
Q: Masking limitations and bypass risks?
One policy per column, no joins in policy body, 10-20% query overhead, no external tables. ACCOUNTADMIN sees unmasked by design. Cannot bypass via EXPLAIN, CTAS, or views. Risk: GROUP BY on masked column reveals uniqueness. Combine masking with row access policies.
Q: How do row access policies compose with masking?
Row access filters ROWS, masking transforms COLUMNS. Independent composition: filter first, then mask. Both can coexist on same table. Row access policies CAN reference other tables (mapping tables) for data-driven access control.
Q: Design multi-tenant access for 50 business units?
Mapping table: bu_access(role_name, business_unit_id). Policy: EXISTS(SELECT 1 FROM bu_access WHERE role_name=CURRENT_ROLE() AND bu_id=val) OR IS_ROLE_IN_SESSION(GLOBAL_ADMIN). Apply to all tables. Adding new BU = insert rows in mapping table, no DDL changes needed.
Q: What is ACCESS_HISTORY and why does it matter?
Records every read/write: user, role, query, columns accessed, objects modified (including base objects through views). Critical for GDPR right-to-access, SOX compliance, security investigations. 2-3 hour latency. Enterprise edition required.
Q: Object tagging for governance at scale?
Key-value metadata on any Snowflake object. Use cases: data classification (PII/PHI/PUBLIC), cost attribution (department on warehouses), compliance (retention/regulation). Tags propagate to views. Tag-based policies bind masking and row access to tags.
ACCOUNTADMIN sees unmasked data by default (admin override by design). Can add explicit CURRENT_ROLE() checks but risks locking out last-resort admin. Best practice: dedicated security admin role.
RBAC (Role-Based Access Control) is primary: permissions granted to roles, roles to users. DAC via ownership. Newer features move toward ABAC (Attribute-Based) where access depends on data attributes.