DAA-C01 Question 41
Single answerCreate tables and viewsA retail analytics team maintains a large SALES_FACT table that is refreshed hourly. Analysts from multiple departments need a simplified object that exposes only ORDER_DATE, REGION, PRODUCT_CATEGORY, and NET_REVENUE for reporting. The team also wants query results against this object to stay current as SALES_FACT changes, without requiring a separate data refresh process. Which Snowflake object should the data analyst create to best meet these requirements?
- A
A standard view on top of SALES_FACT selecting only the required columns
- B
A materialized view on top of SALES_FACT selecting only the required columns
- C
A transient table populated from SALES_FACT on an hourly task
- D
A temporary table populated from SALES_FACT at the start of each analyst session
Show answer and explanation
Correct answer: A
Explanation
The key requirement is to provide a simplified reporting object that stays current with changes in the underlying SALES_FACT table and does not require a separate refresh process. In Snowflake, a standard view is the appropriate object for this use case because it provides a logical layer over one or more tables, exposes only the needed columns, and returns current data from the base table at query time.
A materialized view may seem attractive because Snowflake automatically maintains it, but that object is intended for performance optimization through stored precomputed results. It adds storage and maintenance overhead and is not the default choice when the business requirement is simply controlled presentation of current data. Tables, whether transient or temporary, create copied data and therefore require explicit loading or refresh logic, which the scenario specifically wants to avoid.
This aligns with Snowflake best practices for semantic abstraction and governed access: use views to simplify schemas and restrict exposed columns, use materialized views selectively for performance, and use transient or temporary tables for persistence or session-based workflows rather than shared, always-current reporting layers.
- A. Correct.
Correct. A standard view is the best fit when the requirement is to present a simplified, current projection of data from a base table without creating a separate physical copy or refresh pipeline. Views store the query definition, not duplicated data, and queries against the view reflect the current state of SALES_FACT at query time. This is a common pattern for governed semantic access to a subset of columns.
- B. Incorrect.
Incorrect. A materialized view physically stores precomputed query results and is primarily used to improve performance for specific repetitive query patterns, not simply to expose a current logical subset of columns. While Snowflake maintains materialized views automatically as base data changes, they introduce storage and maintenance costs and have usage constraints. Given the scenario focuses on simplified access and freshness without a separate refresh process, a standard view is the more appropriate and economical choice.
- C. Incorrect.
Incorrect. A transient table populated hourly would create a separate copy of the data and require orchestration such as a task or ETL job. This violates the requirement to avoid a separate refresh process and would also mean the object is only as current as the latest load. Transient tables are useful for persistent non-fail-safe staging or intermediate data, not for a continuously current logical projection of a fact table.
- D. Incorrect.
Incorrect. A temporary table is session-scoped and disappears when the session ends, so it is unsuitable for shared departmental reporting. It also requires each analyst session to populate the table, which adds operational complexity and does not provide a centrally managed reporting object. This option reflects a misunderstanding of temporary table lifecycle and intended use.