ARA-C01 Question 64
Single answerProjection policiesA healthcare company stores patient records in a Snowflake table that includes highly sensitive columns such as SSN, DATE_OF_BIRTH, and HOME_ADDRESS. Data engineers want analysts to continue querying the table directly, but the security team requires that analysts only be able to project approved columns in ad hoc SELECT statements. The team also wants a control that is enforced at query time without creating separate secured views for every consumer group. Which approach best meets this requirement?
- A
Create a projection policy that allows only approved columns to be included in query SELECT lists, and attach it to the table or relevant columns.
- B
Create a masking policy on SSN, DATE_OF_BIRTH, and HOME_ADDRESS so those columns cannot be referenced in queries by analysts.
- C
Create a row access policy that filters rows returned to analysts based on role membership, which also prevents selection of unapproved columns.
- D
Grant analysts SELECT on only the approved columns by using column-level privileges, because this natively restricts which columns can appear in projections.
Show answer and explanation
Correct answer: A
Explanation
Projection policies are intended for scenarios where architects need to control whether specific columns may appear in query projections. This makes them appropriate when users should continue querying a base table directly, but only approved columns should be selectable in ad hoc SQL. By contrast, masking policies protect the displayed value of a column rather than stopping the column from being referenced, and row access policies filter rows rather than columns. In practice, projection policies can reduce operational overhead compared with managing many secure views for different audiences. This aligns with Snowflake governance best practices of applying the narrowest control that matches the requirement: projection policies for projected-column control, masking policies for value obfuscation, and row access policies for row filtering.
- A. Correct.
Correct. Projection policies are designed to control which columns can be projected in query results. This is the most direct solution when the requirement is to allow access to the base table while restricting what can appear in ad hoc SELECT statements. It provides query-time enforcement without needing to create and maintain separate secure views for each consumer group.
- B. Incorrect.
Incorrect. Masking policies protect the values returned from sensitive columns by transforming or obscuring the data at query time, but they do not prevent users from referencing those columns in the SELECT list. An analyst could still project the column; they would just see masked output. This does not satisfy the requirement to restrict projection itself.
- C. Incorrect.
Incorrect. Row access policies control which rows are visible based on conditions such as role or session context. They do not control which columns can be selected. This option reflects a common confusion between row-level and column-level/query projection controls.
- D. Incorrect.
Incorrect. Snowflake does not solve this requirement through a general-purpose column-level SELECT privilege model that restricts projected columns in the way described here. Even where access design can limit exposure through other constructs, the stated requirement is specifically about controlling projected columns in ad hoc queries without building separate views, which is what projection policies address.