SnowPro Advanced: Security Engineer Question 110
Single answerDesign and apply Dynamic Data Masking policiesA healthcare company stores patient records in a Snowflake table named PATIENTS, including a column SSN of type STRING. The security team needs the following behavior using Dynamic Data Masking: users with the role PII_ADMIN should see full SSN values, users with the role SUPPORT_ANALYST should see only the last 4 digits, and all other roles should see a fully masked value. The solution must be enforced centrally at the column level so existing BI tools do not need to change their queries. Which approach best meets the requirement?
- A
Create a masking policy on the SSN column that uses CURRENT_ROLE() in a CASE expression to return the full value for PII_ADMIN, a partially masked value for SUPPORT_ANALYST, and a default masked string for all other roles; then apply the policy directly to PATIENTS.SSN.
- B
Create a row access policy on the PATIENTS table that filters rows based on CURRENT_ROLE(), because row access policies can also transform sensitive column values returned to each role.
- C
Create separate secure views for each role and grant each role access to its own view, because Dynamic Data Masking cannot return different masked formats for different roles from the same base column.
- D
Encrypt the SSN column with Tri-Secret Secure and grant the decryption key only to PII_ADMIN and SUPPORT_ANALYST, because Dynamic Data Masking does not support conditional logic based on role.
- E
Use a tag-based masking policy only, because direct masking policies cannot be applied to existing columns without recreating the table.
Show answer and explanation
Correct answer: A
Explanation
The best solution is to use a Dynamic Data Masking policy attached to the SSN column. Snowflake masking policies are schema-level objects that evaluate at query time and can use role context to determine what value to return. This makes them well suited for scenarios where one role needs full access, another needs partial reveal, and everyone else gets a default mask. In practice, the policy could use logic such as CASE WHEN IS_ROLE_IN_SESSION('PII_ADMIN') THEN val WHEN IS_ROLE_IN_SESSION('SUPPORT_ANALYST') THEN CONCAT('XXX-XX-', RIGHT(val, 4)) ELSE 'XXX-XX-XXXX' END. This approach aligns with Snowflake best practices for centralized, declarative data protection and avoids forcing BI teams to use separate objects or rewrite SQL. Snowflake documentation distinguishes masking policies from row access policies: masking policies transform column values, while row access policies determine row visibility. Snowflake also supports applying masking policies directly to columns or through tag-based masking, but direct application is fully valid for this requirement.
- A. Correct.
Correct. A masking policy is the appropriate Snowflake feature for centrally enforcing role-based redaction at the column level. The policy body can use conditional logic such as CASE with CURRENT_ROLE() or IS_ROLE_IN_SESSION() to return different representations of the same value. Applying the masking policy directly to PATIENTS.SSN ensures downstream tools continue to query the same table and column while receiving role-appropriate results.
- B. Incorrect.
Incorrect. A row access policy controls which rows are visible, not how individual column values are transformed. It is a common confusion to treat row access policies as a general-purpose data protection mechanism, but they do not perform dynamic masking of column contents.
- C. Incorrect.
Incorrect. Secure views can sometimes be used to expose different projections, but they are not the best fit here because the requirement is centralized enforcement at the column level without changing existing query patterns. Dynamic Data Masking is specifically designed for this use case and can return different masked formats based on role from the same base column.
- D. Incorrect.
Incorrect. Tri-Secret Secure is related to encryption key management and protection of data at rest, not conditional presentation of data to query users. It does not replace masking policies and does not provide role-based partial display logic such as showing only the last 4 digits.
- E. Incorrect.
Incorrect. Tag-based masking is supported in Snowflake, but it is not the only valid method. Direct masking policies can be applied to existing columns using ALTER TABLE ... MODIFY COLUMN ... SET MASKING POLICY or during table definition. Recreating the table is not required.