COF-C03 Question 111
Single answerViews typesA data engineering team maintains a SALES_FACT table that is updated continuously throughout the day. Business analysts need a queryable object that returns near-real-time results, but they must not see customer PII columns such as EMAIL and PHONE. The team also wants to avoid storing duplicate data. Which Snowflake object best meets these requirements?
- A
Create a standard view on SALES_FACT that selects only the non-PII columns needed by analysts
- B
Create a materialized view on SALES_FACT with all columns, then grant analysts access only to the safe columns
- C
Create a temporary table populated from SALES_FACT without the PII columns and refresh it every hour
- D
Create an external table over exported SALES_FACT files that exclude the PII columns
Show answer and explanation
Correct answer: A
Explanation
The best answer is a standard view. In Snowflake, standard views are logical objects defined by a query and do not store the underlying result set as a separate copy of the table data. This makes them well suited for presenting a restricted subset of columns, such as excluding PII, while still providing current results from the base table. Materialized views, by contrast, persist precomputed results and are used to improve performance for specific query patterns, but they consume storage and are not the best answer when the main goals are data masking by column omission and avoiding duplicate stored data. Temporary tables and external tables also do not fit the scenario: temporary tables duplicate data and can become stale, while external tables are intended for externally staged data rather than as a direct abstraction over an internal Snowflake table. This aligns with Snowflake documentation and best practices for using standard views to simplify access and restrict visible columns.
- A. Correct.
Correct. A standard view stores the query definition rather than duplicating the underlying table data, so it avoids extra data storage for a copied dataset. It can expose only selected columns from SALES_FACT, which is a common way to hide sensitive fields such as EMAIL and PHONE from consumers. Because the view queries the base table at runtime, analysts see current data rather than a separately refreshed copy.
- B. Incorrect.
Incorrect. A materialized view stores precomputed query results and therefore does create additional storage. It is designed primarily for performance optimization of repeated queries, not as the best fit here for avoiding duplicate stored data. Also, including all columns in the materialized view does not align with the requirement to prevent analysts from seeing PII; column exposure should be restricted in the object definition or through appropriate controls.
- C. Incorrect.
Incorrect. A temporary table would physically store another copy of the data for the session or process that creates it, which conflicts with the goal of avoiding duplicate data storage. Refreshing it every hour also fails the near-real-time requirement because the data would be stale between refreshes.
- D. Incorrect.
Incorrect. An external table is used to query data stored externally, such as in cloud storage, and would require exporting data out of Snowflake. That adds operational complexity and does not fit the requirement for a queryable object directly over the continuously updated internal SALES_FACT table. It also does not inherently provide near-real-time access to the latest internal table updates.