COF-C03 Question 236
Single answerDynamic tablesA data engineering team is building a near-real-time reporting pipeline in Snowflake. Raw clickstream data lands continuously in a base table. The team creates a dynamic table that joins the raw table to several reference tables and aggregates the results for dashboards. Business users require the dashboard data to be updated within 10 minutes of source changes, but the team also wants to minimize compute costs.
Which action should the engineer take to best meet these requirements when configuring the dynamic table?
- A
Create the dynamic table with TARGET_LAG = '10 minutes' and allow Snowflake to refresh it automatically
- B
Create the dynamic table with TARGET_LAG = DOWNSTREAM so that it refreshes every time any source table changes
- C
Replace the dynamic table with a standard view, because views always reflect the latest source data without compute cost
- D
Schedule a task to manually refresh the dynamic table every 10 minutes, because dynamic tables do not support automatic refresh behavior
Show answer and explanation
Correct answer: A
Explanation
Dynamic tables are designed to maintain the results of a query automatically over time, making them useful for incremental pipeline stages and dashboard-serving tables. The key configuration for freshness is TARGET_LAG, which specifies the acceptable delay between updates in the source data and the dynamic table. In this scenario, setting TARGET_LAG = '10 minutes' directly aligns with the business requirement and lets Snowflake orchestrate refreshes in a cost-aware way.
TARGET_LAG = DOWNSTREAM is appropriate in chained dynamic table pipelines when an upstream dynamic table should refresh only as needed to satisfy dependent downstream tables, not when there is a direct 10-minute freshness SLA. A standard view is not a substitute for a dynamic table when the goal is to precompute complex transformations for repeated reporting queries. Finally, dynamic tables natively support automated refreshes; tasks are a separate Snowflake orchestration feature and are not required just to keep a dynamic table current.
This aligns with Snowflake documentation and best practices for dynamic tables: use TARGET_LAG to define freshness expectations, allow Snowflake to manage refreshes automatically, and choose dynamic tables when you want materialized query results that stay up to date within a specified lag.
- A. Correct.
Correct. A dynamic table can be configured with a TARGET_LAG value such as 10 minutes to express the desired freshness relative to upstream data. Snowflake then manages refreshes automatically to keep the dynamic table within that target lag, which is the appropriate configuration when the business has a defined freshness SLA and the team wants Snowflake to optimize refresh timing.
- B. Incorrect.
Incorrect. TARGET_LAG = DOWNSTREAM is used when a dynamic table should be refreshed based on the needs of downstream dynamic tables rather than an explicit time-based freshness target. It does not mean the table refreshes immediately whenever any source table changes, and it is not the best choice when the requirement is specifically to keep data updated within 10 minutes.
- C. Incorrect.
Incorrect. A standard view does expose the latest data at query time, but it does not materialize results. For a complex join-and-aggregate workload used by dashboards, a view can shift compute cost and latency to query execution rather than precomputing results. This does not address the requirement to maintain near-real-time prepared data while minimizing dashboard query overhead.
- D. Incorrect.
Incorrect. Dynamic tables do support automated refresh behavior managed by Snowflake. Using a task to force periodic manual refresh is unnecessary for the core dynamic table refresh mechanism and reflects a misunderstanding of how dynamic tables are designed to operate.