Explain Dynamic Data Masking in Databases

62 / 66

Snowflake

Dynamic Data Masking

Conditionally obscure sensitive column values based on the querying user's role.

Dynamic data masking lets you define a masking policy on a column so that the actual stored value is only shown in full to authorized roles, while unauthorized roles see a masked, redacted, or transformed version — all without maintaining separate copies of the data or duplicating tables per audience. The underlying data is unchanged; masking is applied dynamically at query time based on the querying session's role.

Dynamic data masking is like a document that shows full detail to a manager but automatically redacts sensitive fields with black bars when the same document is handed to an intern — the underlying page never changes, only what different readers are permitted to see.

Key Concepts

A masking policy is a schema-level object defining a CASE-like expression: it typically checks CURRENT_ROLE() (or more robust context functions like IS_ROLE_IN_SESSION() to account for role hierarchies) and returns either the original value or a masked substitute (e.g., 'XXX-XX-' || RIGHT(ssn, 4), full redaction, or NULL). Once attached to a column via ALTER TABLE ... MODIFY COLUMN ... SET MASKING POLICY, the masking is enforced everywhere that column is queried — directly, through views, through joins — with no way to bypass it except by having a role the policy explicitly authorizes.

CASECURRENT_ROLE()IS_ROLE_IN_SESSION()'XXX-XX-' || RIGHT(ssn, 4)NULL

A single masking policy can be applied to multiple columns across multiple tables (as long as the data types match), which is efficient for enforcing a consistent masking rule (e.g., "always mask PII columns the same way") across an entire schema rather than writing bespoke logic per table.

Masking policies are commonly paired with row access policies to build defense-in-depth: row policies control which rows are visible at all, while masking policies control what those visible rows reveal — together forming Snowflake's core column- and row-level data governance toolkit, often audited together under compliance frameworks like GDPR, HIPAA, or PCI-DSS.