ARA-C01 Question 347
Single answerDynamic tablesA data platform team is designing a near-real-time analytics pipeline in Snowflake using dynamic tables. Raw clickstream events land continuously in a staging table. The team plans to create DT_CLEANSED from the staging table and DT_SESSIONIZED from DT_CLEANSED. Business stakeholders require the final sessionized data to be no more than 10 minutes behind the source data, while minimizing warehouse cost and operational overhead. Which design approach best meets these requirements?
- A
Set TARGET_LAG = '10 minutes' on both dynamic tables and use DOWNSTREAM for DT_CLEANSED so refreshes are coordinated based on the final table's lag requirement.
- B
Set TARGET_LAG = DOWNSTREAM on DT_SESSIONIZED and schedule a task every 10 minutes to refresh both dynamic tables explicitly.
- C
Set TARGET_LAG = '5 minutes' on DT_CLEANSED and TARGET_LAG = '10 minutes' on DT_SESSIONIZED so the upstream table refreshes often enough to support the downstream SLA.
- D
Create both dynamic tables with TARGET_LAG = '1 minute' and rely on auto-suspend on the warehouse to minimize cost while guaranteeing the 10-minute requirement.
Show answer and explanation
Correct answer: C
Explanation
Dynamic tables use declarative refresh management based on TARGET_LAG, and architects should design lag values across the entire dependency chain, not in isolation. In a multi-hop pipeline, the downstream dynamic table's freshness depends on the upstream dynamic table being refreshed early enough to provide recent data. Therefore, assigning the same lag to each layer is often insufficient for an end-to-end SLA. A common best practice is to set upstream dynamic tables to a tighter lag than downstream ones, or use DOWNSTREAM on intermediate upstream nodes when appropriate to coordinate refreshes with downstream demand. Snowflake documentation on dynamic tables emphasizes understanding dependency graphs, automatic refresh behavior, and TARGET_LAG semantics when designing cost-efficient pipelines that meet freshness objectives.
- A. Incorrect.
Incorrect. In a dynamic table pipeline, setting an upstream dynamic table to DOWNSTREAM means it refreshes in response to the needs of downstream dynamic tables. However, if both DT_CLEANSED and DT_SESSIONIZED are given a fixed target lag of 10 minutes, the cumulative lag through the pipeline can exceed the desired freshness at the final layer. Snowflake recommends configuring upstream dynamic tables with a tighter target lag than downstream tables, or using DOWNSTREAM appropriately, to account for pipeline depth. This option does not best address end-to-end freshness for the final table.
- B. Incorrect.
Incorrect. Dynamic tables are refreshed automatically by Snowflake based on TARGET_LAG; they are not designed to be manually orchestrated by tasks for standard refresh behavior. Also, setting the final dynamic table to DOWNSTREAM is not valid for achieving a terminal SLA because DOWNSTREAM is intended for upstream tables that should refresh only when needed by downstream dependencies. This option adds unnecessary operational overhead and reflects a misunderstanding of how dynamic table refresh scheduling works.
- C. Correct.
Correct. In a chain of dynamic tables, the architect must design target lag with end-to-end freshness in mind. By setting DT_CLEANSED to a tighter lag, such as 5 minutes, and DT_SESSIONIZED to 10 minutes, the upstream table is kept sufficiently fresh so the downstream table can usually meet the final 10-minute freshness requirement. This aligns with Snowflake guidance that target lag should be planned across the dependency graph rather than assigned identically at every layer. It also keeps the system fully managed by Snowflake without introducing extra orchestration.
- D. Incorrect.
Incorrect. A 1-minute target lag on both layers may improve freshness, but it likely increases compute cost unnecessarily and does not represent the best design for a stated 10-minute business requirement. Dynamic tables refresh automatically to try to meet the specified lag, so over-tightening target lag can cause more frequent refresh activity than needed. Auto-suspend helps reduce idle warehouse cost, but it does not make this the most cost-efficient architecture.