DAA-C01 Question 42
Single answerCreate tables and viewsA retail analytics team needs to expose order data to business analysts while minimizing storage cost and ensuring analysts only see current records. The source table RAW_ORDERS contains one row per order version and includes ORDER_ID, CUSTOMER_ID, STATUS, TOTAL_AMOUNT, UPDATED_AT, and IS_CURRENT. The team wants a reusable object that analysts can query directly without copying data, and it must return only the latest version of each order where IS_CURRENT = TRUE. Which solution best meets these requirements?
- A
Create a standard view that selects from RAW_ORDERS with a filter WHERE IS_CURRENT = TRUE.
- B
Create a materialized view on RAW_ORDERS with all columns and filter WHERE IS_CURRENT = TRUE to avoid storing duplicate data.
- C
Create a temporary table from RAW_ORDERS with only current rows and grant analysts access to that table.
- D
Create a secure view that selects from RAW_ORDERS with a filter WHERE IS_CURRENT = TRUE because secure views store no data and are required whenever current rows are exposed.
Show answer and explanation
Correct answer: A
Explanation
The key requirements are: no copying of data, a reusable object for analyst queries, and a filter that exposes only current rows. In Snowflake, a standard view is a logical object defined by a query and does not store the underlying result set, making it the most appropriate choice for minimizing storage. A materialized view, by contrast, stores precomputed results and is intended primarily for performance optimization, not for avoiding storage. Temporary tables are physical tables with session-limited scope, so they are not suitable for shared analyst access. Secure views are valid Snowflake objects and also do not store data, but they are used when additional security characteristics are needed, such as limiting exposure of underlying definitions and certain internal details; they are not inherently required by this scenario. This aligns with Snowflake documentation and best practices for choosing between tables, views, and materialized views based on storage, reuse, and governance needs.
- A. Correct.
Correct. A standard view is the best fit because it provides a reusable query abstraction over the base table without duplicating the underlying data. Since the requirement is to minimize storage cost and expose only current records, a view with WHERE IS_CURRENT = TRUE satisfies both. Analysts can query the view directly, and the data remains current because the view resolves against the underlying table at query time.
- B. Incorrect.
Incorrect. Materialized views do physically store precomputed query results and therefore do not meet the requirement to minimize storage cost as well as a standard view. They are useful for performance optimization in specific cases, but the scenario emphasizes avoiding copied data rather than accelerating repeated queries. Choosing this option reflects the misconception that materialized views are purely logical like standard views.
- C. Incorrect.
Incorrect. A temporary table would create another physical copy of the current rows, increasing storage usage and introducing maintenance overhead because it would need to be rebuilt or refreshed to stay current. Temporary tables are also session-scoped, so they are not appropriate as a reusable object for analysts across sessions.
- D. Incorrect.
Incorrect. A secure view also does not store data and can be appropriate when stronger metadata and definition protection is needed, especially for data sharing or to prevent exposure of underlying query details. However, the scenario does not state a need for secure-view-specific protections. The claim that secure views are required whenever current rows are exposed is false. A standard view is sufficient for the stated requirements.