COF-C03 Question 237
Single answerDynamic tablesA data engineering team uses a dynamic table to maintain a near-real-time aggregated sales dataset for dashboards. The source table receives continuous inserts throughout the day. The team created the dynamic table with TARGET_LAG = '5 minutes', but business users report that the data is often 20-30 minutes behind. Investigation shows the dynamic table definition is valid and the source data is arriving on time. The team wants the dynamic table to refresh closer to the requested lag. Which action is the MOST appropriate to address this issue?
- A
Increase the size of the warehouse used for refreshing the dynamic table so refreshes can complete faster
- B
Convert the dynamic table to a temporary table so Snowflake can refresh it more frequently
- C
Change the dynamic table to use a materialized view because materialized views guarantee refresh exactly at the TARGET_LAG interval
- D
Set TARGET_LAG to DOWNSTREAM so the dynamic table refreshes every 5 minutes regardless of warehouse performance
Show answer and explanation
Correct answer: A
Explanation
Dynamic tables use automated refreshes to keep query results up to date, but TARGET_LAG represents the desired maximum data freshness, not an absolute SLA that Snowflake guarantees under all conditions. In practice, if refresh queries are expensive or the assigned warehouse is undersized, actual lag can exceed the target. In this scenario, the definition is valid and source data is timely, so the most practical corrective action is to improve the compute resources used for refreshes. Snowflake documentation and best practices for dynamic tables emphasize understanding that refresh timing depends on workload complexity, refresh duration, and available compute. Candidates should distinguish dynamic tables from temporary tables and materialized views, and should also recognize that DOWNSTREAM affects dependency-based refresh behavior rather than forcing a fixed refresh interval.
- A. Correct.
Correct. TARGET_LAG is a freshness target, not a strict guarantee. If refresh processing takes too long or available compute is insufficient, the actual lag can exceed the target. Increasing the warehouse size used for dynamic table refreshes can help refresh jobs finish sooner and bring observed lag closer to the requested freshness target.
- B. Incorrect.
Incorrect. Temporary tables do not provide automatic refresh behavior like dynamic tables. Converting to a temporary table would remove the automated incremental maintenance capability rather than improve refresh performance.
- C. Incorrect.
Incorrect. Materialized views and dynamic tables serve different use cases. Materialized views do not provide a guaranteed exact refresh schedule based on a user-defined TARGET_LAG value. This option confuses dynamic table freshness targets with materialized view maintenance behavior.
- D. Incorrect.
Incorrect. TARGET_LAG = DOWNSTREAM means the dynamic table refreshes relative to downstream dependencies, not that it will refresh every 5 minutes regardless of compute capacity. It does not override warehouse sizing or performance constraints.