SnowPro Advanced: Security Engineer Question 102
Single answerDomain 2.0: Data Protection, Data Privacy, and Data Governance (30%)A healthcare company stores patient records in Snowflake. Analysts in the ANALYST role need to query a PATIENT_VISITS table for operational reporting, but they must not see full Social Security numbers or full dates of birth. A small compliance team in the COMPLIANCE role must retain access to the unmasked values for investigations. The security engineer wants a solution that is centrally managed in Snowflake, applies automatically at query time, and minimizes changes to analyst SQL. Which approach best meets these requirements?
- A
Create a masking policy that conditionally reveals SSN and DOB only when CURRENT_ROLE() is COMPLIANCE, and apply the policy to the sensitive columns in PATIENT_VISITS.
- B
Encrypt the SSN and DOB columns with client-side encryption before loading, then grant analysts access to the encrypted columns and let COMPLIANCE decrypt values outside Snowflake when needed.
- C
Create a secure view over PATIENT_VISITS that excludes the SSN and DOB columns for analysts, and grant COMPLIANCE direct access to the base table.
- D
Use row access policies to hide rows containing SSN and DOB from analysts while allowing COMPLIANCE to see all rows.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use a masking policy applied to the sensitive columns. In Snowflake, dynamic data masking is the appropriate control when different users or roles should see different representations of the same column values at query time. This is a strong fit for privacy-sensitive environments such as healthcare, where analysts often need operational access without exposure to direct identifiers, while authorized compliance personnel need full visibility.
Why this is the best fit:
- It is centrally managed in Snowflake as a policy object.
- It applies automatically at query time to protected columns.
- It minimizes application and SQL changes because users continue querying the same table.
- It supports role-aware logic, commonly implemented with context functions such as CURRENT_ROLE() or policy mappings.
Why the other choices are weaker:
- Client-side encryption protects data differently and is not a substitute for role-based masking inside Snowflake.
- Secure views can help with data sharing and governance, but they are not the most direct or scalable answer when the requirement is conditional masking of specific columns with minimal query changes.
- Row access policies are for row-level visibility decisions, not for masking column values.
This aligns with Snowflake best practices for protecting sensitive data using dynamic data masking for column-level protection and row access policies for row-level restrictions. Secure views are valuable in some architectures, but the exam typically expects candidates to match the control to the requirement precisely: masking policy for conditional column obfuscation, row access policy for row filtering.
- A. Correct.
Correct. Dynamic data masking in Snowflake is designed for exactly this use case: protecting sensitive column values at query time based on the querying context, such as role. A masking policy can return fully visible values for the COMPLIANCE role and masked or transformed values for the ANALYST role, with minimal or no changes to analyst queries because the policy is attached directly to the columns. This provides centralized governance and consistent enforcement wherever the protected columns are queried.
- B. Incorrect.
Incorrect. Client-side encryption can protect data before it enters Snowflake, but it does not provide centralized, query-time role-based masking within Snowflake. Analysts would still see encrypted data rather than usable masked values, and COMPLIANCE would need external decryption workflows. This adds operational complexity and does not meet the requirement to minimize SQL changes while handling access natively in Snowflake.
- C. Incorrect.
Incorrect. A secure view can be used to restrict exposed columns, but it is less flexible for this requirement because analysts need to query the table for reporting while still potentially using the columns in masked form. A secure view that excludes SSN and DOB removes the columns entirely rather than masking them. Also, maintaining parallel access paths between views and base tables is less centralized and can increase governance complexity compared to attaching masking policies directly to the sensitive columns.
- D. Incorrect.
Incorrect. Row access policies determine which rows are visible, not how individual column values are transformed. In this scenario, analysts should still see the patient visit rows, just not the full SSN or DOB values. Using a row access policy would address row-level filtering, which is the wrong control for a column-level privacy requirement.