ARA-C01 Question 49
Single answerData GovernanceA healthcare company stores patient data in Snowflake and must enforce strict data governance controls across multiple analytics teams. The security team wants to ensure that analysts in different departments can query the same PATIENTS table, but only see rows for their own department and only see unmasked personally identifiable information (PII) when their role has explicit approval. The solution must be centrally governed, scalable across many tables and columns, and minimize custom code in individual queries. Which approach should the architect recommend?
- A
Create secure views for each department and require analysts to query only those views; grant direct access to PII columns to approved roles.
- B
Implement a row access policy on the PATIENTS table for department-based filtering and a masking policy on PII columns, using role context to determine who can see unmasked values.
- C
Use dynamic tables to create one copy of the PATIENTS data per department and apply object privileges so each team can access only its departmental copy.
- D
Encrypt PII columns with client-side encryption before loading into Snowflake and distribute decryption keys only to approved analysts.
Show answer and explanation
Correct answer: B
Explanation
For Snowflake data governance, the most appropriate architecture for this scenario is to combine a row access policy with masking policies. Row access policies enforce row-level security so users only see the subset of rows they are entitled to access, often by evaluating CURRENT_ROLE(), IS_ROLE_IN_SESSION(), mapping tables, or other context. Masking policies provide dynamic data masking at query time so sensitive columns can return either masked or unmasked values depending on the querying role. This approach is more scalable and maintainable than creating many department-specific secure views or duplicating data into separate objects.
Snowflake best practices generally favor policy-based governance for centralized enforcement and reuse. In larger environments, architects may further improve manageability by combining these controls with tag-based masking, which allows masking policies to be associated through tags across many sensitive columns. However, for the stated requirement, the key capabilities are row access policies for department filtering and masking policies for conditional PII exposure. This design minimizes custom SQL logic in individual queries and keeps governance controls close to the data, which is consistent with Snowflake's native data governance model.
- A. Incorrect.
This approach can work in limited cases, but it is not the most scalable or centrally governed design for the stated requirements. Creating separate secure views for each department increases operational overhead, especially across many tables and departments. It also splits governance logic across multiple objects instead of enforcing it directly on the base table and columns. Granting direct access to PII columns is not sufficient by itself to provide conditional reveal behavior; masking policies are the intended Snowflake feature for dynamic column-level protection.
- B. Correct.
This is the best answer. A row access policy is designed to enforce row-level filtering based on context such as the current role, mapping tables, or session context, which fits the requirement that analysts only see rows for their own department. A masking policy is designed to protect sensitive columns such as PII while allowing approved roles to see cleartext values and unapproved roles to see masked values. This combination is centrally managed, reusable, and minimizes application or query-level custom logic. It aligns with Snowflake data governance best practices for policy-based access control.
- C. Incorrect.
This is a plausible but incorrect design. Dynamic tables are intended for incremental pipeline transformations and derived-table maintenance, not as a primary governance mechanism for row-level entitlements. Creating separate copies per department increases storage and management complexity, introduces duplication, and makes governance harder to maintain consistently. Object privileges on separate copies do not provide the same centralized, policy-based control as row access and masking policies.
- D. Incorrect.
This option overcorrects and does not satisfy the practical analytics requirement. Client-side encryption prevents Snowflake from applying native masking logic and generally makes querying and analytics on encrypted fields much more difficult unless additional decryption handling is implemented outside Snowflake. The requirement is for centrally governed, scalable controls within Snowflake that minimize custom code. Native masking policies are specifically built for this scenario, whereas client-side encryption is more appropriate when the platform itself must not have access to plaintext.