COF-C03 Question 32
Single answerViewsA Snowflake administrator needs to give an external audit team read-only access to a subset of customer order data. The auditors should only see rows for the current fiscal year, and the underlying base table structure should remain hidden from them. The solution should minimize data duplication and be easy to maintain as new rows are added to the source table. Which object should the administrator create?
- A
A standard view that filters the base table to the current fiscal year and grant the auditors access to the view
- B
A materialized view that filters the base table to the current fiscal year and grant the auditors access to the materialized view
- C
A transient table populated with current fiscal year data and grant the auditors access to that table
- D
A clone of the source table and grant the auditors access only to the clone
Show answer and explanation
Correct answer: A
Explanation
The best choice is a standard view. In Snowflake, views are commonly used to restrict data exposure by presenting only specific rows and columns from underlying tables, while abstracting the base table from consumers. Because a standard view stores only the SQL definition, it avoids duplicating data and automatically reflects changes in the base table when queried. This aligns with the scenario's requirements: provide read-only access, limit visibility to current fiscal year rows, hide the underlying structure, and reduce maintenance. Materialized views are designed mainly for query performance and involve stored results with additional maintenance considerations. Transient tables and clones are not ideal because they do not inherently provide dynamic row filtering with minimal administrative overhead. This is consistent with Snowflake guidance on using views for logical data abstraction and controlled access.
- A. Correct.
Correct. A standard view is appropriate when the goal is to expose only selected rows and columns from a base table while hiding the underlying table definition from end users. Views do not store duplicated data; they store the query definition and return current results from the underlying table each time they are queried. This makes them well suited for read-only access patterns where the subset should stay current as new rows are added.
- B. Incorrect.
Incorrect. Although materialized views can improve performance for certain repeated queries, they are not the best answer for this requirement. The scenario focuses on controlled read-only exposure, hiding the base table, minimizing duplication, and easy maintenance. Materialized views physically store precomputed results and are generally used for performance optimization, not as the primary mechanism for simple secure data presentation to an external team.
- C. Incorrect.
Incorrect. A transient table would require copying or reloading data into another table, which increases maintenance effort and introduces data duplication. It also creates an extra object that must be refreshed as new current-year rows arrive. This does not meet the requirement to minimize duplication and simplify ongoing maintenance.
- D. Incorrect.
Incorrect. A clone is a separate table-like object based on zero-copy cloning at creation time, but it still exposes the full table structure unless additional objects are added on top of it. It also does not automatically enforce the fiscal-year row filter requirement. While cloning avoids immediate physical duplication, it is not the correct object for presenting a filtered, read-only subset that stays aligned with the source through query logic.