ARA-C01 Question 346
Single answerDynamic tablesA retail analytics team uses a pipeline of Snowflake dynamic tables to build near-real-time reporting. The base table SALES_RAW receives continuous inserts throughout the day. A dynamic table DT_SALES_CLEAN standardizes and filters the raw data, and another dynamic table DT_DAILY_REGION aggregates revenue by region and business date for dashboards. The architect must reduce compute cost while keeping the dashboard data no more than 15 minutes behind the source. Which design choice best meets this requirement?
- A
Set DT_DAILY_REGION to TARGET_LAG = '15 minutes' and DT_SALES_CLEAN to DOWNSTREAM so the upstream table refreshes only when needed by the aggregate table.
- B
Set both dynamic tables to TARGET_LAG = '1 minute' so Snowflake can optimize refresh scheduling automatically and minimize total cost.
- C
Replace DT_DAILY_REGION with a materialized view because materialized views guarantee lower cost than dynamic tables for aggregate reporting workloads.
- D
Schedule a task every 15 minutes to ALTER both dynamic tables and force a refresh, because dynamic tables do not support dependency-aware refresh behavior.
Show answer and explanation
Correct answer: A
Explanation
The key architectural requirement is to meet a 15-minute freshness SLA at the lowest reasonable cost. In Snowflake, dynamic tables support TARGET_LAG to express freshness requirements declaratively. In a chain of dynamic tables, using DOWNSTREAM on upstream tables is a cost-conscious design because it allows refreshes to occur in response to downstream needs instead of refreshing independently on a stricter schedule. This is especially effective when only the final reporting table has a business-facing SLA. By contrast, over-constraining lag to 1 minute increases compute, and replacing dynamic tables with materialized views is not inherently better because materialized views have different limitations and are not designed as a general substitute for multi-step transformation pipelines. Snowflake documentation and best practices emphasize aligning TARGET_LAG to actual business needs and using dependency-aware refresh semantics in dynamic table pipelines.
- A. Correct.
Correct. Setting the final serving dynamic table to a 15-minute target lag aligns directly to the business SLA. Configuring the upstream dynamic table with DOWNSTREAM lets Snowflake refresh it only when a dependent dynamic table needs fresh data, which can reduce unnecessary refreshes and therefore lower compute consumption. This is a practical design pattern for chained dynamic tables where only the downstream result has a strict freshness requirement.
- B. Incorrect.
Incorrect. A 1-minute target lag is much stricter than the stated 15-minute requirement and would typically increase refresh frequency and compute usage rather than minimize it. Although Snowflake does manage refresh scheduling for dynamic tables, setting unnecessarily aggressive lag targets is a common cost optimization mistake.
- C. Incorrect.
Incorrect. Materialized views are not a universal lower-cost replacement for dynamic tables. The best choice depends on query pattern, transformation complexity, and pipeline design. Dynamic tables are specifically intended for declarative pipeline orchestration and can handle multi-step transformations; this scenario already uses dependent transformations and a freshness SLA that is well suited to dynamic tables.
- D. Incorrect.
Incorrect. Dynamic tables already support dependency-aware refresh behavior, including coordination across upstream and downstream dynamic tables. Using a task to issue ALTER statements to force refresh is unnecessary for this requirement and adds operational complexity. This option reflects a misconception that dynamic tables must be externally orchestrated like tasks.