COF-C03 Question 108
Single answerDynamicA data engineering team uses a dynamic table to maintain a curated SALES_DAILY_SUMMARY table from a large transactional SALES table. Business users require the summary to stay within 15 minutes of the source data. The team also wants Snowflake to automatically choose whether to refresh incrementally or perform a full refresh when needed, instead of forcing one mode. Which configuration best meets these requirements?
- A
Create the dynamic table with TARGET_LAG = '15 minutes' and REFRESH_MODE = AUTO
- B
Create the dynamic table with TARGET_LAG = DOWNSTREAM and REFRESH_MODE = FULL
- C
Create the dynamic table with TARGET_LAG = '15 minutes' and REFRESH_MODE = INCREMENTAL
- D
Create the dynamic table with TARGET_LAG = '15 minutes' and enable automatic clustering on the source table
Show answer and explanation
Correct answer: A
Explanation
Dynamic tables use TARGET_LAG to define how fresh the materialized results should be compared with the underlying data. When a business requirement states that data must be within a specific window, such as 15 minutes, TARGET_LAG should be set accordingly. Snowflake also supports different refresh modes for dynamic tables, including AUTO, which lets Snowflake determine the best supported refresh behavior based on the dynamic table definition. This is appropriate when the team does not want to hard-code FULL or INCREMENTAL behavior. In contrast, DOWNSTREAM is used for dependency-driven refresh orchestration and does not directly express a 15-minute SLA for the dynamic table itself. Best practice is to choose a TARGET_LAG that reflects the business freshness requirement and use AUTO when you want Snowflake to manage the refresh mode selection.
- A. Correct.
Correct. TARGET_LAG specifies the acceptable freshness of the dynamic table relative to its base objects. Setting TARGET_LAG = '15 minutes' aligns with the business requirement that results be no more than 15 minutes behind source data. REFRESH_MODE = AUTO allows Snowflake to determine whether the dynamic table can be maintained incrementally or must use full refresh semantics based on the query definition and other factors. This is the best fit when the team wants Snowflake to choose the refresh strategy automatically.
- B. Incorrect.
Incorrect. TARGET_LAG = DOWNSTREAM means the dynamic table is refreshed according to the needs of downstream dynamic tables rather than a direct freshness target. That does not satisfy a stated requirement that this table itself remain within 15 minutes of the source data. REFRESH_MODE = FULL also contradicts the requirement to let Snowflake decide between incremental and full refresh.
- C. Incorrect.
Incorrect. TARGET_LAG = '15 minutes' does satisfy the freshness requirement, but REFRESH_MODE = INCREMENTAL forces incremental refresh behavior rather than allowing Snowflake to choose automatically. A candidate might select this because incremental refresh sounds more efficient, but the scenario explicitly asks for automatic selection of the refresh approach.
- D. Incorrect.
Incorrect. TARGET_LAG = '15 minutes' addresses freshness, but automatic clustering on the source table is unrelated to how the dynamic table chooses between incremental and full refresh modes. Clustering can help query performance in some cases, but it does not configure dynamic table refresh strategy. The missing required setting here is REFRESH_MODE = AUTO.