COF-C03 Question 31
Single answerViewsA data engineering team maintains a table named SALES_RAW that contains all customer transactions, including internal cost columns and rows for every region. Business analysts in the FINANCE role need query access only to revenue-related columns for the EMEA region. The team also wants future changes to SALES_RAW data to be reflected automatically without copying data into another table. Which solution best meets these requirements?
- A
Create a standard view that selects only the required revenue columns from SALES_RAW and filters rows with REGION = 'EMEA', then grant the FINANCE role access to the view.
- B
Create a materialized view on SALES_RAW with the required columns and EMEA filter, then grant the FINANCE role access to the materialized view because materialized views are the primary security mechanism for column and row restriction.
- C
Clone SALES_RAW into a new table for FINANCE, delete non-EMEA rows and sensitive columns, and grant the FINANCE role access to the cloned table.
- D
Create a temporary view with the required columns and EMEA filter, then grant the FINANCE role access so analysts can use it across sessions without exposing the base table.
Show answer and explanation
Correct answer: A
Explanation
The best solution is to create a standard view that exposes only the needed columns and filters rows to REGION = 'EMEA'. In Snowflake, views are commonly used to present a subset of table data for security and simplification purposes because they store SQL logic rather than duplicate underlying table data. This means updates to the base table are visible through the view automatically. Materialized views are mainly for performance optimization of repeated query patterns, not the default choice for access control scenarios. Cloned or copied tables create additional objects to manage and do not provide the same live abstraction as a view. Temporary views are not persistent and therefore are not suitable for shared analyst access. This aligns with Snowflake best practices around using views to restrict visible columns and rows while avoiding unnecessary data duplication.
- A. Correct.
Correct. A standard view is appropriate when you want to expose only selected columns and rows from a base table without duplicating data. Because a view stores the query definition rather than a separate copy of the table data, changes in SALES_RAW are reflected automatically when the view is queried. Granting access to the view lets analysts query the restricted projection of the data instead of the full table.
- B. Incorrect.
Incorrect. A materialized view stores precomputed query results to improve performance for certain workloads, but it is not the primary mechanism for implementing simple access restriction requirements. It also introduces maintenance and eligibility considerations. While it can contain filtered and projected data, using it here for security is unnecessary and not the best-fit answer compared with a standard view.
- C. Incorrect.
Incorrect. Cloning creates a separate table object that initially shares storage at the micro-partition level, but it is still a separate table and does not automatically remain synchronized with subsequent changes to the source table in the way a view does. This approach also adds administrative overhead and does not meet the requirement to avoid copying data into another table for ongoing access.
- D. Incorrect.
Incorrect. Temporary views are session-scoped objects and are not suitable for persistent shared access by business analysts across sessions. They are intended for short-lived work within a session, not for governed, ongoing departmental reporting access.