Dynamic Tables vs Streams/Tasks: When to Use Which

When building data pipelines in Snowflake, choosing between Dynamic Tables and Streams + Tasks is a critical architectural decision. The two follow completely different paradigms and suit different use cases. This article compares them across 7 axes and gives you a framework for making the call.

Axis 1: Declarative vs Imperative

Dynamic Tables are declarative. You simply declare "keep the result of this SELECT statement up to date," and Snowflake handles change detection, incremental processing, and refresh scheduling for you.

Streams + Tasks are imperative. You explicitly code the steps: use a Stream to capture CDC (change data capture) and a Task to run MERGE/INSERT statements. Transformation logic, execution order, and error handling are all under your control.

-- 宣言型: Dynamic Table
CREATE DYNAMIC TABLE gold_revenue
  TARGET_LAG = '10 minutes'
  WAREHOUSE = etl_wh
AS
  SELECT region, SUM(amount) AS total_revenue
  FROM silver_orders
  GROUP BY region;

-- 手続型: Stream + Task
CREATE STREAM orders_stream ON TABLE silver_orders;

CREATE TASK merge_revenue
  WAREHOUSE = etl_wh
  SCHEDULE = 'USING CRON */10 * * * * UTC'
  WHEN SYSTEM$STREAM_HAS_DATA('orders_stream')
AS
  MERGE INTO gold_revenue t
  USING (
    SELECT region, SUM(amount) AS total_revenue
    FROM orders_stream
    GROUP BY region
  ) s ON t.region = s.region
  WHEN MATCHED THEN UPDATE SET t.total_revenue = t.total_revenue + s.total_revenue
  WHEN NOT MATCHED THEN INSERT (region, total_revenue) VALUES (s.region, s.total_revenue);

Axis 2: SLA Control

Axis 3: Development and Operational Complexity

Axis 4: JOIN Handling

Dynamic Tables let you write any JOIN inside the SELECT, and Snowflake tracks every upstream table for you. With Streams + Tasks, a Stream is created on a single table (or view), so to JOIN changes from multiple tables you either create multiple Streams and combine them inside the Task, or design around the timing skew of change detection across sources.

-- Dynamic Tables: JOINを含むSELECTをそのまま宣言
CREATE DYNAMIC TABLE enriched_orders
  TARGET_LAG = '5 minutes'
  WAREHOUSE = etl_wh
AS
  SELECT
    o.order_id,
    o.order_date,
    p.product_name,
    p.category,
    o.quantity * p.unit_price AS line_total
  FROM raw_orders o
  JOIN raw_products p ON o.product_id = p.product_id;

-- Streams/Tasks: 複数Streamの管理が必要
CREATE STREAM orders_cdc ON TABLE raw_orders;
CREATE STREAM products_cdc ON TABLE raw_products;
-- → Task内で両Streamを参照し、MERGEロジックを実装
-- → 片方のStreamだけにデータがあるケースの処理が必要

Axis 5: Incremental Logic

Dynamic Tables' incremental refresh is detected and executed automatically by Snowflake. You write the SELECT without worrying about refresh mode. Keep in mind, though, that queries containing WINDOW functions or SELECT DISTINCT fall back to Full Refresh.

With Streams + Tasks, the Stream exposes INSERT/UPDATE/DELETE rows through columns like METADATA$ACTION, so you can write a MERGE statement that handles incremental processing with fine-grained precision. Tracking DELETE operations or comparing before/after values on UPDATEs are all possible.

Axis 6: Cost Structure

Axis 7: Monitoring and Observability

-- Dynamic Tablesのリフレッシュ監視
SELECT name, refresh_action, state, data_timestamp
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY())
WHERE state = 'FAILED'
ORDER BY refresh_start_time DESC
LIMIT 10;

-- Streams/Tasksの実行監視
SELECT name, state, error_message, scheduled_time
FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY(
  SCHEDULED_TIME_RANGE_START => DATEADD(HOUR, -24, CURRENT_TIMESTAMP())
))
WHERE state = 'FAILED'
ORDER BY scheduled_time DESC;

Design Decision Flowchart

パイプラインの要件は？
│
├─ SELECT文で表現可能な変換か？
│   ├─ YES → JOINや集計が中心か？
│   │         ├─ YES → Dynamic Tables推奨
│   │         └─ NO（条件分岐・外部連携あり）→ Streams/Tasks
│   └─ NO（ストアドプロシージャが必要）→ Streams/Tasks
│
├─ 厳密なCRONスケジュールが必要か？
│   ├─ YES（毎時0分に実行等）→ Streams/Tasks
│   └─ NO（最大遅延指定でOK）→ Dynamic Tables
│
├─ DELETE/UPDATEの変更前後の値を追跡する必要があるか？
│   ├─ YES → Streams/Tasks（METADATA$ACTION / $ISUPDATE）
│   └─ NO → Dynamic Tables
│
└─ 運用負荷を最小化したいか？
    ├─ YES → Dynamic Tables
    └─ 細かい制御が必要 → Streams/Tasks

7-Axis Summary Comparison

Check Your Understanding

Frequently Asked Questions