9.3 Declarative Data Pipelines with Dynamic Tables

9.3 Declarative Data Pipelines with Dynamic Tables

Historically, constructing continuous data pipelines in Snowflake required stitching together Streams, Tasks, and Stored Procedures using imperative procedural SQL. While powerful, this approach demands significant operational overhead: engineers must manually define stream offsets, write complex MERGE statements, maintain DAG dependencies, and handle task suspension ordering.

To modernize continuous transformation, Snowflake introduced Dynamic Tables. A Dynamic Table is a first-class declarative object defined by a SQL query and a target freshness objective. Instead of prescribing how and when data moves, architects declare what the materialized state should be and how fresh it needs to remain. Snowflake's autonomous continuous engine handles change data tracking, refresh scheduling, compute allocation, and pipeline orchestration.

Dynamic Tables Architecture

A Dynamic Table materializes the result of an arbitrary SQL query over one or more base tables, views, or other dynamic tables. Unlike standard views (which compute results at query time) or materialized views (limited to a single table with no joins and restricted aggregations), Dynamic Tables support multi-table joins, aggregations, window functions, and unions.

-- Create a Dynamic Table aggregating customer orders
CREATE OR REPLACE DYNAMIC TABLE analytics_db.marts.dt_customer_spending
  TARGET_LAG = '10 MINUTES'
  WAREHOUSE = transform_wh
AS
  SELECT 
    c.customer_id,
    c.customer_name,
    c.tier,
    COUNT(o.order_id) AS total_orders,
    SUM(o.order_amount) AS total_spend,
    MAX(o.order_date) AS latest_order_date
  FROM raw_db.staging.customers c
  INNER JOIN raw_db.staging.orders o 
    ON c.customer_id = o.customer_id
  GROUP BY c.customer_id, c.customer_name, c.tier;

The Core Properties of Dynamic Tables

1. Declarative SQL Definition: The transformation logic is expressed entirely via a standard SELECT query. There are no procedural loops, cursor fetches, or manual MERGE statements.
2. Materialized Storage: Like a standard table, data is physically materialized into columnar micro-partitions in Snowflake storage. Queries against dynamic tables read pre-computed results directly with zero view re-computation latency.
3. Automated Continuous Refresh: Snowflake's continuous processing engine monitors upstream source objects and automatically initiates background refreshes using the assigned virtual warehouse to satisfy the specified freshness target.

Target Lag (TARGET_LAG) & Autonomous Scheduling

The TARGET_LAG parameter is the cornerstone of Dynamic Table architecture. It specifies the maximum allowable duration that the dynamic table's materialized data may lag behind the latest committed changes in its upstream base objects.

Specifying Target Lag

Target lag can be specified using explicit time durations or the specialized DOWNSTREAM keyword:

• Time Duration: E.g., TARGET_LAG = '1 MINUTE', '15 MINUTES', '1 HOUR', '1 DAY'. This defines a concrete service level agreement (SLA) for data freshness.
• DOWNSTREAM: E.g., TARGET_LAG = 'DOWNSTREAM'. Specifies that this dynamic table should not refresh on an independent schedule. Instead, it refreshes only when downstream dynamic tables that depend on it require refreshed inputs.

-- Staging Dynamic Table configured for Downstream refresh
CREATE OR REPLACE DYNAMIC TABLE analytics_db.staging.dt_cleansed_orders
  TARGET_LAG = 'DOWNSTREAM'
  WAREHOUSE = transform_wh
AS
  SELECT order_id, customer_id, order_amount, order_date
  FROM raw_db.staging.raw_orders
  WHERE status != 'CANCELLED';

-- Mart Dynamic Table configured with explicit 15-minute lag
CREATE OR REPLACE DYNAMIC TABLE analytics_db.marts.dt_hourly_revenue
  TARGET_LAG = '15 MINUTES'
  WAREHOUSE = transform_wh
AS
  SELECT DATE_TRUNC('hour', order_date) AS order_hour, SUM(order_amount) AS hourly_revenue
  FROM analytics_db.staging.dt_cleansed_orders
  GROUP BY 1;

How the Autonomous Scheduler Works

When you chain dynamic tables together into a multi-tier pipeline (e.g., Bronze $\rightarrow$ Silver $\rightarrow$ Gold), Snowflake automatically constructs a global dependency graph:

1. Dependency Analysis: The scheduler inspects the lineage between all dynamic tables and their upstream sources.
2. Lag Propagation: In the example above, dt_cleansed_orders has TARGET_LAG = 'DOWNSTREAM'. Because dt_hourly_revenue depends on it with a 15-minute lag, Snowflake automatically schedules dt_cleansed_orders to refresh just before dt_hourly_revenue refreshes.
3. Compute Efficiency: Using DOWNSTREAM eliminates redundant refreshes. If intermediate staging tables had their own 5-minute schedules, they would wake warehouses unnecessarily even when downstream reporting consumers only require 15-minute or hourly updates.

Exam Trap: If a Dynamic Table is configured with TARGET_LAG = 'DOWNSTREAM' and no downstream dynamic tables reference it, the table will NEVER refresh automatically! It will remain frozen at its initial creation state until a downstream consumer is created or a manual refresh is executed (ALTER DYNAMIC TABLE ... REFRESH).

SLA Reality: Target Lag vs. Actual Lag

TARGET_LAG represents a target objective for the scheduler, not a guaranteed hard real-time SLA:

• If an upstream data batch is exceptionally large, or if the assigned virtual warehouse is undersized and experiences severe CPU queuing, the execution duration of the refresh query may exceed the target lag window.
• Snowflake continues the refresh until completion, but the dynamic table's actual lag metric will temporarily exceed TARGET_LAG until catch-up completes.

Refresh Modes: Incremental Refresh vs. Full Refresh

A dynamic table's REFRESH_MODE is AUTO (default), INCREMENTAL, or FULL. With AUTO, Snowflake decides at creation time whether the query can be refreshed incrementally; the choice is then fixed, and SHOW DYNAMIC TABLES reports the refresh_mode_reason if Snowflake chose full refresh. Setting REFRESH_MODE = INCREMENTAL explicitly makes creation fail if the query cannot be refreshed incrementally.

1. Incremental Refresh (Preferred)

In an Incremental Refresh, Snowflake's engine analyzes change tracking metadata from upstream objects and processes only the micro-partitions that changed since the previous refresh. Delta rows are merged into the target table's micro-partitions without scanning the entire source dataset.

• Compute Consumption: Scales proportionally to the volume of modified/inserted rows, not the total size of the base table.
• Performance: Usually much faster than rebuilding the whole table when only a small share of the data changed.

2. Full Refresh (Fallback)

In a Full Refresh, Snowflake re-evaluates the dynamic table's entire query definition from scratch, scanning all base tables and rebuilding the target table's micro-partitions.

• Initial Materialization: The very first refresh following CREATE DYNAMIC TABLE is always a Full Refresh to establish the baseline dataset.
• Compute Consumption: Scales with the total volume of all base data. Can be very expensive for terabyte- or petabyte-scale tables.

SQL Operators: Incremental vs. Full Refresh Compatibility

A critical topic tested on the ARA-C01 exam is understanding which SQL constructs allow Incremental Refresh versus those that force a Full Refresh:

Architectural Comparison: Dynamic Tables vs. Streams & Tasks

When designing enterprise transformation pipelines, architects must choose between declarative Dynamic Tables and imperative Streams & Tasks. The SnowPro Advanced: Architect exam expects you to evaluate this architectural trade-off systematically.

Architectural Decision Matrix

When to Choose Dynamic Tables

1. SQL-Centric Transformation Pipelines: Your pipeline consists of standard data warehouse modeling patterns (dimensional stars, snowflakes, or normalized layers) expressible in SQL.
2. Multi-Hop Medallion Architectures: Transforming raw ingested data through Bronze (raw), Silver (cleansed/joined), and Gold (aggregated business marts).
3. Reduced Maintenance Overhead: You want to eliminate the operational burden of managing stream offsets, monitoring stream staleness, configuring task DAGs, and writing repetitive MERGE statements.
4. Autonomous SLA Management: You require declarative data freshness targets (TARGET_LAG) where the platform coordinates dependency ordering.

When to Choose Streams & Tasks

1. Imperative Procedural Workflows: The transformation requires conditional branching, looping, dynamic SQL generation, or calling Stored Procedures.
2. Non-SQL and Snowpark Processing: Workloads requiring Python, Java, or Scala runtimes, machine learning feature engineering, or Snowpark DataFrames.
3. External System Integration: Pipelines that invoke External Functions (e.g., calling an AWS Lambda function for address verification or credit scoring) or send alerts via notification integrations.
4. Sub-Minute Micro-Batching: Workloads requiring tight procedural triggers on specific transactional events rather than periodic lag-based evaluation.

Observability & Pipeline Management

Architects monitor Dynamic Table health, refresh performance, and lineage graphs using dedicated Snowflake Information Schema table functions:

-- Inspect refresh history and execution modes for a dynamic table
SELECT 
  name,
  state,
  refresh_action,
  refresh_start_time,
  refresh_end_time,
  TIMESTAMPDIFF('second', refresh_start_time, refresh_end_time) AS duration_seconds,
  data_timestamp,
  error_message
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(
  DYNAMIC_TABLE_NAME => 'ANALYTICS_DB.MARTS.DT_HOURLY_REVENUE'
))
ORDER BY refresh_start_time DESC;

Key Observability Columns

• refresh_action: Displays what a given refresh did, for example NO_DATA (no upstream changes), INCREMENTAL, FULL, or REINITIALIZE.
• refresh_mode_reason (from SHOW DYNAMIC TABLES): explains why a table resolved to full refresh.
• data_timestamp: The point-in-time snapshot to which the dynamic table's data was synchronized.

To trigger an immediate ad-hoc refresh outside the regular schedule:

-- Manually force an immediate refresh
ALTER DYNAMIC TABLE analytics_db.marts.dt_hourly_revenue REFRESH;