ADA-C01 Question 164
Single answer2.2 Implement and manage data governance in Snowflake.A healthcare company stores patient data in Snowflake. A table named PATIENT_VISITS contains a column SSN and a column DIAGNOSIS. Analysts in the ANALYST role should be able to query the table, but they must see SSN masked unless they are in the PRIVACY_ADMIN role. The company also wants the masking rule to continue working if the table is cloned into another schema for testing. Which solution best meets these requirements with the least ongoing administrative effort?
- A
Create a masking policy on SSN that checks CURRENT_ROLE() and returns the original value only for PRIVACY_ADMIN, then attach the policy directly to the SSN column.
- B
Create a row access policy on PATIENT_VISITS that filters rows for ANALYST and allows all rows for PRIVACY_ADMIN, then attach it to the table.
- C
Create a secure view over PATIENT_VISITS that excludes SSN for ANALYST and exposes SSN for PRIVACY_ADMIN, and require all users to query the view instead of the base table.
- D
Encrypt the SSN values in the table using client-side encryption keys and grant PRIVACY_ADMIN access to the decryption key while ANALYST queries the encrypted values.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use a dynamic data masking policy attached directly to the SSN column. Snowflake masking policies are designed for column-level governance and allow administrators to define conditional logic so that sensitive values are revealed only to authorized roles and masked for others. This is preferable to a row access policy, which governs row visibility rather than column redaction, and preferable to a secure view because policies are centralized, harder to bypass when applied to the base column, and easier to maintain at scale. Snowflake cloning preserves metadata, including policy bindings, which supports the requirement that the masking behavior continue in cloned environments. This aligns with Snowflake best practices for implementing data governance using native policy-based controls such as masking policies for sensitive columns and row access policies for row-level filtering.
- A. Correct.
Correct. A masking policy is the native Snowflake governance feature for dynamically obfuscating sensitive column values while still allowing access to the rest of the row. Attaching the masking policy directly to the SSN column enforces the rule centrally for all queries against that column. Using a conditional expression based on the active role, such as CURRENT_ROLE() or role-aware logic, is a standard approach when access depends on role membership. Because masking policies are bound to the column metadata, they are retained when objects are cloned, which helps the rule continue to apply in test clones with minimal extra administration.
- B. Incorrect.
Incorrect. A row access policy controls which rows are visible, not how a specific column value is displayed. In this scenario, analysts should still see the patient visit rows but with SSN masked. Using a row access policy would solve a different problem and could unintentionally hide rows rather than protect the SSN column.
- C. Incorrect.
Incorrect. A secure view can be used to limit exposed columns, but it is not the best fit here. It adds an additional object layer and depends on users consistently querying the view instead of the base table. It also creates more administrative overhead when tables are cloned or when access patterns expand. The requirement is specifically to mask one sensitive column while preserving access to the table, which is what masking policies are designed to do.
- D. Incorrect.
Incorrect. Client-side encryption protects data before it reaches Snowflake, but it does not provide Snowflake-native role-based dynamic masking behavior for query results. Analysts would see ciphertext rather than a governed masked value, and key management would add significant operational complexity. This does not meet the requirement as cleanly as a masking policy.