ADA-C01 Question 165
Single answer2.2 Implement and manage data governance in Snowflake.A healthcare company stores patient data in a Snowflake table named PATIENTS. The SECURITYADMIN has been asked to enforce the following governance requirements with minimal application changes: analysts in the ANALYST role must be able to query the table, but they must see masked values for the SSN column; users in the COMPLIANCE role must see the full SSN; and if users query through a secure view or directly from the table, the same protection must apply consistently. Which approach should the Snowflake administrator implement?
- A
Create a masking policy on PATIENTS.SSN that uses CURRENT_ROLE() to return the full value for COMPLIANCE and a masked value for other roles, then attach the policy directly to the SSN column.
- B
Create a row access policy on the PATIENTS table to hide the SSN values from ANALYST users while allowing COMPLIANCE users to see all rows and columns.
- C
Create a secure view over PATIENTS that masks SSN for ANALYST and grant access only to the view; direct table access will still be governed by the same masking rules automatically.
- D
Encrypt the SSN column with client-side encryption so only COMPLIANCE can decrypt it inside Snowflake queries, while ANALYST sees masked plaintext values.
Show answer and explanation
Correct answer: A
Explanation
The best solution is to implement a dynamic data masking policy and assign it to the sensitive column. In Snowflake, masking policies provide column-level protection and can evaluate query context such as CURRENT_ROLE() to determine whether to reveal or redact data. This directly satisfies the requirement that COMPLIANCE sees full SSN values while ANALYST sees masked data. Applying the policy to PATIENTS.SSN ensures consistent enforcement across access paths, including direct table queries and queries through views. By contrast, row access policies are for row filtering, not column masking. Secure views can be part of a defense-in-depth strategy, but they do not by themselves enforce masking on direct table access. This aligns with Snowflake data governance best practices for protecting PII using masking policies.
- A. Correct.
Correct. A dynamic data masking policy is the appropriate Snowflake governance feature for protecting sensitive column values such as SSN based on the querying role or context. Attaching the masking policy directly to the SSN column ensures the protection is enforced wherever that column is queried, including direct table access and through views. Using CURRENT_ROLE() or another supported context function in the policy body is a common implementation pattern for role-based masking.
- B. Incorrect.
Incorrect. A row access policy controls which rows are visible to a user, not how individual column values are transformed or obfuscated. It cannot selectively mask the SSN column while leaving the rest of the row visible. This option reflects a common confusion between row-level governance and column-level governance.
- C. Incorrect.
Incorrect. A secure view can help limit exposure of underlying logic and can present transformed data, but it does not automatically protect direct access to the base table. If users retain table privileges, they can bypass the view. The requirement states that protection must apply consistently whether querying directly or through a secure view, so the policy must be bound to the column itself rather than relying only on a view layer.
- D. Incorrect.
Incorrect. Snowflake supports encryption for data at rest and in transit, and external/client-side encryption patterns exist, but this is not the native governance mechanism for role-based masking during query execution. Client-side encryption would also require significant application changes and would not natively produce masked values for one role and cleartext for another within standard SQL query results.