AdvancedLast updated: 2026-04-27 • 4 sections
Expert questions on dynamic data masking, row access policies, tag-based governance, ACCESS_HISTORY, and data protection at scale.
Q: How do dynamic data masking policies work and how do they differ from encryption?
Masking: a policy is attached to a column; at query time, a SQL expression transforms the value based on the calling role — CASE WHEN CURRENT_ROLE() IN ('PII_ADMIN') THEN val ELSE '***' END. Encryption: at-rest AES-256, the same encrypted value stored for all users, decrypted transparently by Snowflake. Masking supports partial masking, hashing, tokenization, and format-preserving output. Snowflake encrypts all data at rest by default — masking adds the query-time access control layer.
Q: How does tag-based masking scale governance across hundreds of tables?
Instead of assigning a masking policy to each of 2000 PII columns individually, create 3–4 tag-level policies and assign the policy to a TAG (e.g., PII_TAG). Any column tagged as PII_TAG automatically inherits the policy. New PII column? Just add the tag — no policy reassignment needed. Query TAG_REFERENCES to get a full PII column inventory across the account.
Q: An ETL role needs unmasked data but an ad-hoc analyst role needs masking — how do you implement this?
Option 1 (role-based): CASE WHEN CURRENT_ROLE() IN ('ETL_ROLE') THEN val ELSE MASKED_VAL END. Option 2 (warehouse-based): CASE WHEN CURRENT_WAREHOUSE() = 'ETL_WH' THEN val ELSE MASKED END. Option 3: IS_ROLE_IN_SESSION('ETL_ROLE') for full role hierarchy check. Best practice: use role-based, never CURRENT_USER(). Combine with row access policies if ETL also needs cross-region row access.
Q: What are the limitations and bypass risks of dynamic masking policies?
Limitations: one policy per column, no JOINs in policy body, 10–20% query performance overhead, no support on external tables. ACCOUNTADMIN sees unmasked data by design. Cannot be bypassed via EXPLAIN, CTAS, or views — policies propagate to derived objects. Risk: GROUP BY or COUNT DISTINCT on a masked column can reveal cardinality/uniqueness. Mitigate by combining column masking with row access policies for full protection.
Q: How do row access policies compose with masking policies on the same table?
Row access policies filter ROWS (which rows the role can see). Masking policies transform COLUMN VALUES (what values a role sees in the rows it can access). They compose independently: Snowflake first applies row filtering, then column masking. Both can be attached to the same table without conflict. This allows defense-in-depth: restrict row visibility by region/department via RAP, then mask PII columns with DMP.
Q: How do you use ACCESS_HISTORY for GDPR compliance audits?
SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY logs every query, the base objects accessed (tables/views), and the columns read. For GDPR: query ACCESS_HISTORY to identify all roles and users who accessed a specific PII column in the last 90 days. Combine with QUERY_HISTORY for full context. For data subject access requests: trace all tables containing a user ID, then check ACCESS_HISTORY for who accessed those columns.
Q: What is Snowflake Data Classification and how does SYSTEM$CLASSIFY work?
Data Classification analyzes column names and sample values to assign SEMANTIC_CATEGORY (e.g., NAME, EMAIL, SSN) and PRIVACY_CATEGORY (IDENTIFIER, QUASI_IDENTIFIER, SENSITIVE) tags automatically. SYSTEM$CLASSIFY(table_name) runs the classification and returns recommendations. SYSTEM$CLASSIFY_SCHEMA() applies across a schema. After classification, tag-based masking policies can be applied automatically to all newly identified PII columns.
Q: How do you govern data sharing with external consumers?
For data shared via a Snowflake Share: masking policies do NOT propagate to consumers — consumers see raw data. To mask shared data: create secure views with masking logic, share the secure view instead of the raw table. Row access policies DO work if set on the shared object at the provider side. For fine-grained consumer control: use listing-level access controls and separate shares per consumer tier.
One masking policy per column (current limitation). A table with 20 columns can have up to 20 different masking policies, one per column. However, the recommended approach for scale is tag-based masking: assign a single policy to a classification tag, then tag columns rather than assigning policies column by column. This reduces policy management from O(columns) to O(sensitivity categories).
Yes — ACCOUNTADMIN sees unmasked data by design because masking policies evaluate role membership, and ACCOUNTADMIN inherits all roles. This is by design for administration and emergency access. Mitigate with: (1) minimize ACCOUNTADMIN grant — only grant to break-glass accounts, (2) enable MFA on all admin accounts, (3) audit ACCOUNTADMIN queries via QUERY_HISTORY and ACCESS_HISTORY, (4) use Snowflake's Trust Center for anomaly detection on privileged role usage.
Secure views: expose only the columns a role should see, mark as SECURE to prevent plan inspection. Column-level grants: GRANT SELECT on specific columns (Snowflake supports column-level privileges). Projection policies: prevent a column from being returned in SELECT * without explicit column reference. For most use cases, dynamic masking is more flexible because it allows format-preserving partial reveal (e.g., last 4 digits of SSN) rather than complete column exclusion.