ARA-C01 Question 27
Single answer1.2 Design an architecture that meets data security, privacy, compliance, and governance requirements.A healthcare analytics company stores PHI in Snowflake and must allow internal analysts to query de-identified patient data while restricting a small compliance team to fully identified records. The company also needs to ensure that data scientists cannot accidentally see sensitive values in ad hoc queries, but approved applications used by the compliance team must still retrieve clear-text data. The architect wants a solution that is centrally governed, minimizes duplicate tables, and supports role-based access control at query time. Which Snowflake design best meets these requirements?
- A
Create separate copies of the patient table for each user group and use object grants so analysts only access the masked copy while compliance users access the original table.
- B
Apply a masking policy to sensitive columns and use role-based conditional logic in the policy so approved roles can see unmasked values while other roles see masked values.
- C
Encrypt the PHI columns with client-side encryption before loading them into Snowflake so analysts see ciphertext and compliance applications decrypt the data after querying.
- D
Create a secure view for analysts and a standard view for compliance users, because secure views automatically prevent sensitive values from being exposed to unauthorized roles.
Show answer and explanation
Correct answer: B
Explanation
The best answer is to use a masking policy with role-aware logic. In Snowflake, Dynamic Data Masking enables architects to protect sensitive columns centrally and enforce data privacy at query time without creating multiple copies of the same data. This directly supports governance, compliance, and least-privilege design goals. In this scenario, analysts can query the same table but receive masked values, while compliance roles or approved application roles can receive clear-text values.
This is preferable to duplicating tables because duplication increases maintenance burden and can create inconsistent security enforcement. It is also preferable to relying on secure views alone, because secure views do not inherently mask data based on role; they protect view definitions and certain inference paths but are not a substitute for masking policies. Client-side encryption is useful in some security models, but it is not the most practical architecture when the requirement is role-based visibility control for interactive SQL and governed application access within Snowflake.
Relevant Snowflake guidance includes documentation on Dynamic Data Masking, role-based access control (RBAC), and data governance features such as masking policies and row access policies. Architects should favor centralized policy enforcement on shared data objects wherever possible to meet privacy and compliance requirements efficiently.
- A. Incorrect.
This approach is possible but does not best meet the stated requirements. Maintaining multiple physical copies increases storage, operational overhead, and governance complexity, and it creates risk that security rules drift between copies. The scenario explicitly calls for minimizing duplicate tables and enforcing controls centrally at query time. While separate tables can work, they are not the best architectural choice compared with dynamic data masking.
- B. Correct.
Correct. Snowflake Dynamic Data Masking is designed for centralized, policy-based protection of sensitive column values at query time. A masking policy can evaluate the current role or other context and return clear text for authorized roles while masking values for others. This allows a single source table to serve both de-identified and identified use cases without duplicating data. It aligns with Snowflake best practices for governance, privacy, and least-privilege access.
- C. Incorrect.
This is not the best choice for the stated need. Client-side encryption before loading would cause Snowflake to store ciphertext and would prevent Snowflake-native policy-based visibility controls from working directly on the plaintext values. It also complicates analytics because most SQL operations on the protected columns become impractical unless data is decrypted outside Snowflake. The requirement is for role-based access at query time inside Snowflake, which masking policies address more directly.
- D. Incorrect.
This is a common misconception. Secure views help protect underlying logic and can provide stronger metadata/privacy protections than standard views, especially in data sharing scenarios, but they do not automatically perform role-based masking of sensitive column values. To expose de-identified versus identified data correctly, the architect would still need masking policies, row access policies, or explicitly defined transformation logic. Simply making a view secure does not satisfy the requirement by itself.