DAA-C01 Question 250
Single answerOperationalize data for consumptionA retail analytics team has built a curated SALES_DAILY table in Snowflake that is consumed by finance dashboards and ad hoc analyst queries. During month-end close, dashboard response times become inconsistent because many users run repeated aggregations on the same daily and monthly metrics. The team wants to operationalize this dataset for consumption so that query performance is more predictable, downstream SQL remains simple, and near-real-time updates continue to be available as new sales records arrive every few minutes. Which approach BEST meets these requirements?
- A
Create a dynamic table that pre-aggregates SALES_DAILY into a dashboard-ready summary table, and point dashboards to the dynamic table.
- B
Create a standard view on top of SALES_DAILY with the required aggregations, because views store the computed result and reduce repeated processing.
- C
Enable Search Optimization Service on SALES_DAILY for all columns used in aggregations, because it is designed to accelerate GROUP BY and SUM workloads across repeated dashboard queries.
- D
Replace SALES_DAILY with an external table so Snowflake does not need to manage storage, improving dashboard concurrency and aggregation performance.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to create a dynamic table that maintains a dashboard-ready aggregate for consumption. This is an operationalization pattern: transform curated data into a consumable, precomputed structure that downstream BI tools can query simply and consistently. Dynamic tables are designed for declarative pipeline-style maintenance of derived tables with defined freshness expectations, which fits the requirement for near-real-time updates. By contrast, standard views do not materialize results and therefore do not reduce repeated computation. Search Optimization Service is valuable for selective query patterns, but it is not the right primary solution for repeated aggregate-heavy dashboards. External tables are for external data access, not for improving analytic aggregation performance on curated internal datasets. This aligns with Snowflake best practices for serving consumption-ready data models and using the appropriate object type for maintained derived datasets.
- A. Correct.
Correct. A dynamic table is a good fit when Snowflake needs to maintain a derived table from upstream data on an ongoing basis. In this scenario, the team has repeated aggregations over the same curated sales data, wants dashboard-facing SQL to stay simple, and needs fresh results as new records arrive every few minutes. A dynamic table can materialize the pre-aggregated result set and refresh it incrementally according to a target lag, making performance more predictable than recalculating the same aggregations in every dashboard query.
- B. Incorrect.
Incorrect. A standard view stores only the query definition, not the computed result set. Each dashboard query against the view still requires Snowflake to execute the underlying aggregation logic at query time. This keeps downstream SQL simple, but it does not address repeated processing or improve performance predictability for heavy, repeated analytical workloads.
- C. Incorrect.
Incorrect. Search Optimization Service is intended primarily to improve the performance of selective point-lookups and certain predicate-based access patterns, such as equality searches, substring/regex text searches, and some semi-structured data access. It is not the primary feature for accelerating repeated broad aggregation workloads like daily and monthly dashboard summaries across many rows.
- D. Incorrect.
Incorrect. External tables are used to query data stored externally, such as in cloud object storage, without loading it fully into native Snowflake tables. They are generally not a performance optimization for high-concurrency dashboard aggregations and would likely make this workload less efficient, not more. They also do not solve the need for maintained pre-aggregated, near-real-time dashboard-ready consumption layers.