Skip to main content

Dynamic Tables vs Streams + Tasks – When to Use What

Dynamic Tables are not a 100% replacement for Streams + Tasks.

They replace them for many SQL-based transformation pipelines, but Streams + Tasks are still required for several advanced use cases.

Streams + TasksDynamic Tables
Procedural (How to update data)Declarative (What the final data should look like)
You write INSERT/MERGE logicYou only write a SELECT statement
You schedule tasksSnowflake refreshes automatically
You manage dependenciesSnowflake manages dependencies
More codingLess coding

🔹 1️⃣ Traditional Streams + Tasks Approach​

Suppose you have a raw sales table RAW_SALES. The traditional approach requires multiple objects:

-- Step 1: Create a Stream to track changes
CREATE OR REPLACE STREAM STREAM_RAW_SALES ON TABLE RAW_SALES;

-- Step 2: Create a Task to process those changes
CREATE OR REPLACE TASK LOAD_SALES
WAREHOUSE = COMPUTE_WH
SCHEDULE = '5 MINUTE'
WHEN SYSTEM$STREAM_HAS_DATA('STREAM_RAW_SALES')
AS
MERGE INTO SALES AS target
USING STREAM_RAW_SALES AS source
ON target.SALE_ID = source.SALE_ID
WHEN MATCHED THEN UPDATE SET target.AMOUNT = source.AMOUNT
WHEN NOT MATCHED THEN INSERT (SALE_ID, CUSTOMER_ID, AMOUNT)
VALUES (source.SALE_ID, source.CUSTOMER_ID, source.AMOUNT);

You are responsible for:

  • Creating Stream
  • Creating Task
  • Scheduling
  • MERGE logic
  • Error handling
  • Task dependencies

🔹 2️⃣ The Dynamic Table Approach​

Instead of all of that, you write a single CREATE DYNAMIC TABLE statement:

CREATE OR REPLACE DYNAMIC TABLE SALES_DT
TARGET_LAG = '5 MINUTES'
WAREHOUSE = COMPUTE_WH
AS
SELECT
CUSTOMER_ID,
SUM(AMOUNT) AS TOTAL_AMOUNT
FROM RAW_SALES
GROUP BY CUSTOMER_ID;

That's it. No Stream. No Task. No MERGE. No Schedule.

Snowflake figures out:

  • What changed
  • What needs refreshing
  • Dependency order
  • Incremental refresh when possible

🔹 3️⃣ Why is it Considered a Replacement?​

Because internally, Dynamic Tables already perform the work that Streams + Tasks were doing manually.

Earlier (Streams + Tasks):

Change in Source
↓
Stream (tracks change)
↓
Task (runs on schedule)
↓
MERGE into target

Now (Dynamic Tables):

Change in Source
↓
Dynamic Table Engine
↓
Updated Result

Snowflake automatically tracks changes and refreshes based on the specified TARGET_LAG. You no longer need to create a separate stream for most SQL transformation pipelines.


🔹 4️⃣ Side-by-Side Example​

Old Method (~40–50 lines of SQL)​

-- Create Stream
CREATE STREAM CUSTOMER_STREAM ON TABLE ORDERS;

-- Create Task
CREATE TASK UPDATE_CUSTOMER_SUMMARY
WAREHOUSE = COMPUTE_WH
SCHEDULE = '5 MINUTE'
WHEN SYSTEM$STREAM_HAS_DATA('CUSTOMER_STREAM')
AS
MERGE INTO CUSTOMER_SUMMARY AS target
USING (
SELECT CUSTOMER_ID, MAX(ORDER_DATE) AS LAST_ORDER
FROM CUSTOMER_STREAM
GROUP BY CUSTOMER_ID
) AS source
ON target.CUSTOMER_ID = source.CUSTOMER_ID
WHEN MATCHED THEN UPDATE SET target.LAST_ORDER = source.LAST_ORDER
WHEN NOT MATCHED THEN INSERT VALUES (source.CUSTOMER_ID, source.LAST_ORDER);

Dynamic Table (~5 lines of SQL)​

CREATE OR REPLACE DYNAMIC TABLE CUSTOMER_DT
TARGET_LAG = '5 MINUTES'
WAREHOUSE = COMPUTE_WH
AS
SELECT CUSTOMER_ID, MAX(ORDER_DATE) AS LAST_ORDER
FROM ORDERS
GROUP BY CUSTOMER_ID;

Only one object. Same result.


🔹 5️⃣ What Replaced What?​

Streams + Tasks ComponentDynamic Table Equivalent
StreamBuilt into Dynamic Table
TaskAutomatic Refresh
Task ScheduleTARGET_LAG
Task Dependency (AFTER)Dynamic Table dependency graph
INSERT / MERGESELECT statement defining the desired result

🔹 6️⃣ Can Dynamic Tables Replace Everything?​

No.

Use Streams + Tasks when you need:

  • Stored Procedure calls
  • Python / Java procedures
  • External API calls
  • Complex MERGE logic
  • Multiple target tables from one stream
  • IF...ELSE logic
  • Loops
  • Email notifications
  • File movement
  • Conditional execution
  • External orchestration

Example — Dynamic Tables CANNOT do this:

IF RECORD_COUNT > 1000
THEN
CALL SEND_EMAIL();
ELSE
CALL LOAD_TABLE();
END IF;

🔹 7️⃣ When Should You Use Each?​

✅ Use Dynamic Tables when:​

  • Bronze → Silver → Gold transformations
  • Data cleansing
  • Aggregations
  • Joins
  • Slowly changing SQL transformations
  • ELT pipelines

Example — Medallion Architecture with Dynamic Tables:

Landing Table
↓
Bronze DT
↓
Silver DT
↓
Gold DT

✅ Use Streams + Tasks when:​

  • CDC processing with custom logic
  • Calling Stored Procedures
  • Complex MERGE logic (e.g., SCD Type 2 with manual valid-from/valid-to)
  • Triggering external APIs
  • Loading multiple target tables from one source
  • Workflow orchestration with conditional branching

🔹 8️⃣ The Hybrid Pattern (Best Practice)​

The most robust architecture often combines both tools:

  • Ingestion: Use Snowpipe or Snowpipe Streaming to land data into a Bronze table.
  • Data Quality & Side Effects: Use a Stream + Task to scan the Bronze table. If bad data is found, quarantine it and send a Slack alert (side effect). Insert the clean data into a Silver table.
  • Transformation & Serving: Point a chain of Dynamic Tables at the Silver table to perform all complex joins and aggregations for the Gold layer.

💡 Interview Answer​

Question: Are Dynamic Tables a replacement for Streams + Tasks?

"Dynamic Tables replace Streams + Tasks for most SQL-based transformation pipelines. Instead of manually creating a Stream to track changes, a Task to schedule execution, and writing MERGE logic, you write a single SELECT statement and Snowflake handles change tracking, scheduling via TARGET_LAG, and dependency management automatically. However, Streams + Tasks are still required for procedural logic like calling stored procedures, triggering external APIs, conditional branching with IF/ELSE, and complex MERGE operations. In practice, the best architecture is a hybrid — using Streams + Tasks for ingestion validation and side effects, and Dynamic Tables for the transformation layers."