ARA-C01 Question 339
Single answerViews and tablesA retail company stores raw customer data in a table named PROD.CRM.CUSTOMERS, including PII columns such as EMAIL and PHONE. Multiple analyst teams in different business units need read access to customer attributes for reporting, but they must not see PII unless they are in a specifically approved role. The data engineering team also wants a design that minimizes data duplication and keeps downstream reporting objects current as new rows are added to the base table. Which solution best meets these requirements?
- A
Create a secure view on PROD.CRM.CUSTOMERS that exposes only non-PII columns to most users, and grant approved roles access to the base table or to a separate secure view that includes PII.
- B
Create a materialized view on PROD.CRM.CUSTOMERS with all required columns, then grant SELECT on the materialized view to all analysts and rely on warehouse-level access controls to prevent exposure of PII.
- C
Create a transient table populated nightly from PROD.CRM.CUSTOMERS without PII for analysts, and give approved roles access to query Time Travel on the transient table when they need PII.
- D
Clone PROD.CRM.CUSTOMERS for each business unit and drop the PII columns from each clone so analysts can query their own current copy without affecting production.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use a secure view-based access pattern. Views are well suited for exposing a governed subset of columns from a base table while avoiding data duplication, and they stay current because they query the underlying table at runtime. In Snowflake, access control should be enforced through roles and object grants, not through warehouses. Materialized views are designed for performance acceleration and come with restrictions; they are not a replacement for column-level governance. Cloning and transient-table copies can be useful in some architectures, but they add management overhead and are less aligned with the requirement to minimize duplication while keeping data current. Snowflake documentation and best practices around views, secure views, materialized views, cloning, and access control support choosing a secure view for this scenario.
- A. Correct.
Correct. A secure view is appropriate when you want to present filtered or restricted table data without duplicating storage, while ensuring consumers see current data from the underlying table. Exposing only non-PII columns through the view satisfies broad reporting access, and approved roles can be granted access either to the base table or to another restricted object that includes PII. Secure views are particularly relevant when you want stronger metadata and definition protection compared with standard views in data-sharing and governance-sensitive scenarios.
- B. Incorrect.
Incorrect. A materialized view stores precomputed results and is primarily used for performance optimization, not as the primary mechanism for column-level security. Granting all analysts access to a materialized view that includes PII would expose the sensitive data. Also, warehouse-level controls do not provide column-level data protection; warehouses control compute usage, not data visibility.
- C. Incorrect.
Incorrect. A transient table would duplicate data and require ETL/ELT maintenance, which conflicts with the requirement to minimize duplication and keep downstream objects automatically current as new rows are added. In addition, Time Travel cannot be used by analysts to recover omitted PII that was never loaded into the transient table. This option reflects a misconception that transient tables are a good substitute for governed logical access patterns.
- D. Incorrect.
Incorrect. Zero-copy cloning minimizes initial storage overhead, but cloning per business unit still creates separate table objects that must be governed and managed independently. Dropping PII columns from clones also creates operational complexity and does not provide the cleanest current-data abstraction for many consumers. This approach is less suitable than a view-based design for broad read access with centralized governance.