SnowPro Advanced: Security Engineer Question 104
Single answer2.1 Implement data security features.A healthcare company stores PHI in a Snowflake table named PATIENT_VISITS. Analysts in the ANALYST role should be able to query diagnosis trends by region, but they must not see direct identifiers such as patient name, SSN, or full date of birth. A small compliance team in the COMPLIANCE role must retain full access to all columns. The security engineer wants to enforce this inside Snowflake with minimal application changes and ensure protection remains in place regardless of which BI tool or worksheet is used. Which approach BEST meets these requirements?
- A
Create a secure view that excludes sensitive columns and grant ANALYST access only to the view, while granting COMPLIANCE access to the base table.
- B
Apply masking policies to the sensitive columns and configure the policy logic to reveal clear text only for the COMPLIANCE role and return masked or transformed values for other roles.
- C
Encrypt the sensitive columns with client-side encryption before loading them into Snowflake so only COMPLIANCE can query the original values.
- D
Use a row access policy on PATIENT_VISITS to hide columns such as patient name and SSN from the ANALYST role.
Show answer and explanation
Correct answer: B
Explanation
The best solution is to use Snowflake dynamic data masking through masking policies on sensitive columns. This directly addresses column-level protection for PHI/PII while allowing different roles to see different representations of the same data. In this scenario, COMPLIANCE can see clear text, while ANALYST can receive masked values or less identifying transformations. This approach is enforced within Snowflake and applies regardless of the client tool, which matches the requirement for centralized, consistent enforcement with minimal application changes.
Why not the others? Secure views can help by projecting only non-sensitive columns, and secure views are useful when you want to prevent exposure of underlying query details and control object access. However, they often require consumers to switch to a different object and are less flexible when the same table must serve multiple audiences with different column visibility rules. Row access policies are for filtering rows, not masking columns. Client-side encryption is not a practical replacement for Snowflake-native conditional access control because it shifts complexity to applications and removes Snowflake's ability to apply policy logic by role.
Relevant Snowflake guidance includes the use of masking policies for dynamic data masking and row access policies for row-level security. Secure views are also documented for controlled data sharing and limiting exposure, but for this scenario, masking policies are the most appropriate data security feature to implement.
- A. Incorrect.
This can reduce exposure, but it is not the best answer for the stated requirement. A secure view can hide columns if analysts are restricted to the view and denied direct access to the base table. However, the requirement is to enforce protection inside Snowflake with minimal application changes and ensure consistent protection regardless of access path. Dynamic data masking is designed specifically for column-level protection and can be applied directly to sensitive columns on the base table, avoiding the need to refactor consumers to use a separate object. Secure views are valuable, but they are not the most direct fit for protecting specific columns while preserving base-table access patterns.
- B. Correct.
This is correct. Masking policies in Snowflake provide dynamic data masking at the column level. The policy can inspect the current role or context and return full values for COMPLIANCE while masking, nulling, or partially transforming values for ANALYST and other roles. Because the policy is attached to the columns themselves, the protection is enforced consistently across supported query interfaces, tools, and SQL clients. This is a common best practice for protecting PII/PHI when different roles need different levels of visibility into the same table.
- C. Incorrect.
This is incorrect. Client-side encryption before loading would make the original values unreadable inside Snowflake unless the application decrypts them externally. That adds operational complexity and does not align with the requirement for minimal application changes. It also prevents Snowflake-native policy-based conditional reveal by role. Snowflake already encrypts data at rest and in transit, but conditional column visibility for different users is typically implemented with masking policies rather than external client-side encryption schemes.
- D. Incorrect.
This is incorrect. Row access policies control which rows are visible to a query based on context; they do not mask or hide specific columns. Someone might choose this option because row access policies are another Snowflake governance feature, but they solve a different problem: row-level filtering, not column-level redaction of identifiers like SSN or DOB.