ADA-C01 Question 167
Single answerProtect sensitive data with security policiesA healthcare company stores patient records in a Snowflake table named PATIENTS. The table includes columns for PATIENT_ID, FULL_NAME, SSN, DIAGNOSIS, and REGION. Analysts in the US and EU should only see SSN values for records in their own region, while support users should never see SSN in clear text. The company wants to enforce this centrally so that the policy applies regardless of which BI tool or SQL client is used. Which solution best meets this requirement with the least ongoing maintenance?
- A
Create a masking policy on the SSN column that uses CURRENT_ROLE() and REGION to determine whether to reveal or mask the value, and attach the policy to the SSN column.
- B
Create a row access policy on the PATIENTS table to hide rows from unauthorized users, because row access policies can also dynamically redact individual column values.
- C
Encrypt the SSN column using client-side encryption before loading it into Snowflake, because masking policies only work in Snowsight and not through external tools.
- D
Create secure views for each user group and region combination, and require all users and tools to query only those views instead of the base table.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use a masking policy on the SSN column. Snowflake masking policies are built specifically to protect sensitive data at the column level and can evaluate query context, such as the active role, to determine whether to display the original value or a masked value. This is the most direct way to protect sensitive fields like SSN while keeping enforcement centralized and independent of the client or tool. In practice, organizations commonly combine role-based logic with conditional expressions and, where needed, use policy logic that considers other column values such as REGION to support context-aware masking behavior. Row access policies solve a different problem: filtering which rows are visible. Secure views can work, but they typically require more maintenance and governance discipline. Snowflake documentation and best practices distinguish these controls clearly: use masking policies for sensitive column protection and row access policies for row-level filtering.
- A. Correct.
Correct. Dynamic data masking is designed to protect sensitive column data centrally at query time. A masking policy can be attached directly to the SSN column and can use context such as CURRENT_ROLE() and conditional logic to determine whether the value should be shown or masked. When appropriate, conditional masking can also reference other column values in the same table, such as REGION, to implement region-based behavior. Because the policy is enforced by Snowflake on the column itself, it applies consistently across supported query interfaces and tools, which minimizes ongoing maintenance compared with managing many separate views.
- B. Incorrect.
Incorrect. Row access policies control which rows are visible to a query; they do not mask or redact individual column values within rows that are returned. A candidate might choose this option because the requirement includes region-based restrictions, but the primary need is to protect SSN values, not just filter records. If support users can still see rows, a row access policy alone would not prevent them from seeing SSN unless combined with a separate masking mechanism.
- C. Incorrect.
Incorrect. Client-side encryption may protect data before it reaches Snowflake, but it does not provide Snowflake-native, role-aware dynamic reveal/mask behavior for authorized versus unauthorized users. Also, the statement that masking policies only work in Snowsight is false. Snowflake security policies are enforced by the platform at query time, regardless of whether users access data through Snowsight, JDBC/ODBC clients, or BI tools. This option also creates operational complexity because authorized users would need an external decryption workflow.
- D. Incorrect.
Incorrect. Secure views can be used to expose restricted data safely, but creating and maintaining separate views for each group and region combination increases administrative overhead and is not the least-maintenance approach. It also depends on every user and tool consistently using the correct views instead of the base table. By contrast, attaching a masking policy directly to the sensitive column provides centralized enforcement on the data object itself.