ARA-C01 Question 42
Single answerData AccessA financial services company stores customer transaction data in a shared Snowflake database. Internal analysts in different business units must see only the rows for their own business unit, and external audit users must see masked account numbers while still being able to query the same tables. The data engineering team wants a solution that minimizes duplicated objects and is maintainable as new business units and users are added. Which approach best meets these requirements?
- A
Create separate physical tables for each business unit and grant each role access only to its own table; create a second set of tables for auditors with masked values applied during ETL.
- B
Apply a row access policy to restrict rows by business unit and a masking policy to mask account numbers for auditor roles; attach the policies to the relevant columns and tables.
- C
Use a secure view for each business unit and a different secure view for auditors, because Snowflake does not support applying row-level and column-level controls directly to base tables.
- D
Use network policies to identify auditor logins and dynamic data masking to filter rows by business unit based on the client IP address.
Show answer and explanation
Correct answer: B
Explanation
For this scenario, Snowflake's policy-based data access features are the most appropriate architectural choice. Row access policies enforce row-level security by evaluating the query context, commonly using role, current user, or mapping-table logic to determine which rows are visible. Masking policies provide dynamic column-level protection so the same table can return masked or unmasked values depending on the querying role. Together, these features allow a single copy of the data to serve multiple audiences with different visibility requirements.
This aligns with Snowflake best practices for centralized governance and reduced object sprawl. While secure views can still be useful in some architectures, they are not required here because Snowflake supports native row access and masking policies. Network policies are unrelated to data-level authorization and should not be confused with row- or column-level access controls.
Relevant Snowflake documentation areas include Row Access Policies, Dynamic Data Masking, and Access Control / role-based access design.
- A. Incorrect.
This approach would work functionally in some environments, but it creates significant object duplication, adds ETL complexity, and is harder to maintain as business units and user populations change. The scenario specifically calls for minimizing duplicated objects and improving maintainability. Snowflake provides policy-based controls that are better suited for this requirement.
- B. Correct.
This is the best answer. Snowflake supports row access policies for row-level security and masking policies for column-level protection. Row access policies can evaluate the current role or context to determine which rows a user can see, such as restricting access by business unit. Masking policies can dynamically mask sensitive columns, such as account numbers, for specific roles like external auditors. This approach centralizes governance, avoids duplicating tables, and scales better as users and business units are added.
- C. Incorrect.
Secure views can help protect underlying logic and are often used in data sharing scenarios, but the statement that Snowflake does not support row-level and column-level controls directly on base tables is incorrect. Snowflake supports row access policies and masking policies natively. Creating many secure views per audience may also increase administrative overhead compared with policy-based access controls.
- D. Incorrect.
Network policies control where users can connect from by restricting allowed IP addresses or network rules; they do not provide row-level filtering. Dynamic data masking in Snowflake addresses column masking, not row filtering. Using client IP address to determine business-unit row visibility is also not an appropriate or maintainable authorization design.