COF-C03 Question 109
Single answerDynamicA data engineering team is building a near-real-time sales reporting pipeline in Snowflake. Raw orders land continuously in a base table, and analysts query an aggregated table of daily revenue by region. The team wants Snowflake to automatically keep the aggregated results up to date as source data changes, while avoiding manual task scheduling and custom merge logic. Which Snowflake feature is the BEST fit for this requirement?
- A
Create a dynamic table for the daily revenue aggregation and let Snowflake refresh it automatically based on its defined query
- B
Create a materialized view on the raw orders table and use it for all aggregation workloads regardless of query complexity
- C
Create a stream on the raw orders table and query the stream directly from BI tools for the latest aggregated revenue
- D
Create a standard view for the aggregation and configure auto-suspend on the warehouse so the view stays current
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use a dynamic table. In Snowflake, dynamic tables support declarative pipeline construction by automatically refreshing the results of a query as source data changes. This is especially useful for common transformation scenarios such as aggregations, filtering, joins, and multi-step ELT pipelines, where teams want Snowflake to manage freshness rather than building orchestration with streams, tasks, and MERGE statements.
Why the other options are less suitable:
- Materialized views are automatically maintained, but they are generally aimed at query acceleration for specific patterns rather than serving as the primary mechanism for broader transformation pipelines.
- Streams capture row-level changes, but they must be paired with downstream processing logic.
- Standard views do not materialize data and do not independently maintain transformed results.
This aligns with Snowflake best practices and product documentation distinguishing dynamic tables from streams/tasks and materialized views: dynamic tables are intended for automated data transformations and pipeline simplification, while streams/tasks are lower-level building blocks for custom orchestration.
- A. Correct.
Correct. Dynamic tables are designed to automatically maintain the results of a query over time, making them a strong fit for transformation pipelines such as aggregations on continuously arriving data. They reduce the need for manually orchestrated tasks and custom MERGE statements. For a scenario where Snowflake should keep derived table results current from changing source data, a dynamic table is the most appropriate feature.
- B. Incorrect.
Incorrect. Materialized views can improve performance for certain repeated query patterns, but they are not the best general-purpose choice for building transformation pipelines with broader query complexity and operational requirements. Dynamic tables are specifically intended for declarative pipeline-style maintenance of derived tables. A common misconception is that materialized views are the default solution for any automatically maintained result set, but they have narrower use cases and limitations compared with dynamic tables for ELT-style workflows.
- C. Incorrect.
Incorrect. Streams track change data capture information on a table, but they do not themselves provide aggregated query results for BI consumption. To use streams for this scenario, the team would still need additional logic such as tasks and DML processing to maintain the target aggregation table. This option conflicts with the requirement to avoid manual scheduling and custom merge logic.
- D. Incorrect.
Incorrect. A standard view stores only the query definition and does not persist or automatically maintain computed results. Auto-suspend affects warehouse compute behavior, not whether a view stays current. The view would reflect current base-table data only when queried, and it would not provide the automated maintained derived-table behavior requested in the scenario.