SnowPro Advanced: Security Engineer Question 105
Single answer2.1 Implement data security features.A healthcare company stores patient data in Snowflake. Analysts in the ANALYST role must be able to query de-identified patient records, while a small PRIVACY_ADMIN role must be able to view full values for columns such as SSN and EMAIL when needed for approved investigations. The security team wants a solution that minimizes application changes, enforces protection centrally in Snowflake, and continues to protect data even if analysts create new views on top of the table. Which Snowflake approach best meets these requirements?
- A
Apply a masking policy to the sensitive columns and use CURRENT_ROLE() or IS_ROLE_IN_SESSION() logic so PRIVACY_ADMIN can see unmasked values while ANALYST sees masked values
- B
Encrypt the sensitive columns with client-side encryption before loading them into Snowflake, and let PRIVACY_ADMIN decrypt them outside Snowflake when needed
- C
Create a secure view that excludes sensitive columns for ANALYST, and grant direct access to the base table only to PRIVACY_ADMIN
- D
Use row access policies on the patient table to hide SSN and EMAIL from ANALYST while allowing PRIVACY_ADMIN to see all column values
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use Snowflake dynamic data masking via masking policies on the sensitive columns. This is the native Snowflake feature for column-level protection of sensitive data such as PII. It allows organizations to define a centralized policy once and apply it directly to columns, with logic based on role context or other conditions. This is especially appropriate when some users should see masked values while a privileged role can see full values. In Snowflake best practice, row access policies are for row-level filtering, while masking policies are for column-level obfuscation. Secure views can help with controlled exposure, but they are not a substitute for masking when the requirement is durable protection attached to the data element itself. Relevant Snowflake documentation areas include Dynamic Data Masking, Masking Policies, conditional policy expressions using role-context functions, and Row Access Policies for comparison.
- A. Correct.
Correct. Dynamic data masking in Snowflake is designed to protect sensitive column values centrally at query time. A masking policy can be attached directly to columns such as SSN and EMAIL, and the policy body can evaluate the active role context using functions such as CURRENT_ROLE() or IS_ROLE_IN_SESSION() to determine whether to reveal or mask the data. Because the policy is attached to the column, the protection persists through downstream queries and views, helping ensure analysts do not bypass masking by creating new views.
- B. Incorrect.
Incorrect. Client-side encryption can protect data before it reaches Snowflake, but it does not meet the requirement for centralized, role-based, in-platform selective disclosure with minimal application changes. It would require external decryption workflows and likely significant application or process changes. It also prevents Snowflake from natively applying policy-based, context-aware masking to the plaintext values.
- C. Incorrect.
Incorrect. A secure view can help restrict exposed columns, but this approach does not provide the same centralized column-level protection as masking policies attached to the base table columns. If analysts later gain access to other derived objects or if governance expands, the protection is not as inherently persistent as a masking policy on the columns themselves. Secure views are useful, but they are not the best fit for the stated requirement that protection continue even when users create new views on top of the table.
- D. Incorrect.
Incorrect. Row access policies control which rows are visible to a query based on a policy expression; they do not mask or selectively hide individual column values within visible rows. This option reflects a common misconception between row-level security and column-level protection. For SSN and EMAIL, a masking policy is the appropriate feature.