ARA-C01 Question 52
Single answerColumn-level securityA healthcare company stores member information in a Snowflake table named PATIENTS, including columns MEMBER_ID, NAME, EMAIL, DOB, and SSN. Analysts in the ANALYST role need full access to all rows but must see SSN masked unless they are in the PRIVACY_OFFICER role. Data engineers loading the table must not have to rewrite ETL logic, and architects want a solution that scales if additional sensitive columns are added later. Which approach best meets these requirements?
- A
Create a masking policy on the SSN column that checks the current role and reveals the value only for PRIVACY_OFFICER; optionally use a tag-based masking strategy later to extend the same control pattern to other sensitive columns.
- B
Create a row access policy on the PATIENTS table so that rows with SSN values are filtered out for ANALYST, but visible to PRIVACY_OFFICER.
- C
Grant SELECT on the SSN column only to PRIVACY_OFFICER and deny the grant to ANALYST, while keeping table-level SELECT for all other columns.
- D
Create a secure view that excludes SSN for ANALYST and a second standard view with SSN for PRIVACY_OFFICER, then require ETL jobs to query the appropriate view instead of the base table.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use a masking policy, because Snowflake's native mechanism for column-level security is dynamic data masking. A masking policy can be set on the SSN column and return either the original value or a masked value based on context such as the active role. This satisfies the requirement to preserve row visibility while protecting only the sensitive field. It also keeps ETL unchanged because queries against the base table continue to work, with masking enforced transparently at runtime. For broader scalability, Snowflake best practices often include tag-based masking so architects can classify columns such as SSN, DOB, or EMAIL with tags and attach masking behavior consistently. By contrast, row access policies are for row-level filtering, not column protection, and view-based designs are generally less maintainable for enterprise-scale column-level security. Relevant Snowflake documentation includes Dynamic Data Masking, Masking Policies, and Tag-Based Masking Policies.
- A. Correct.
Correct. Dynamic data masking is the Snowflake feature designed for column-level security. A masking policy can be attached directly to the SSN column and can evaluate context such as CURRENT_ROLE() to decide whether to return the real value or a masked value. This preserves row visibility while protecting only the sensitive column, which aligns with the requirement that analysts still see all rows. It also avoids ETL rewrites because the policy is enforced at query time on the base table. For future scale, Snowflake supports tag-based masking, allowing architects to associate masking policies to tags and apply them consistently across additional sensitive columns.
- B. Incorrect.
Incorrect. Row access policies control which rows are visible, not how individual column values are masked. Using a row access policy here would remove entire records from ANALYST users instead of masking just the SSN field. That conflicts with the requirement that analysts must retain access to all rows.
- C. Incorrect.
Incorrect. Snowflake does not implement column-level security simply by granting or denying SELECT on individual columns in the same way some traditional databases do. The standard Snowflake approach for protecting sensitive column values is masking policies. A candidate might choose this because column grants are a common pattern in other platforms, but it does not address Snowflake's policy-based column masking requirement.
- D. Incorrect.
Incorrect. Views can sometimes be used to present different projections of data, but this approach does not best meet the requirements. It introduces administrative overhead, requires consumers and possibly ETL processes to switch from the base table to specific views, and scales poorly as more sensitive columns and role-based rules are added. It is less flexible and less centralized than masking policies.