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 + Tasks | Dynamic Tables |
|---|---|
| Procedural (How to update data) | Declarative (What the final data should look like) |
| You write INSERT/MERGE logic | You only write a SELECT statement |
| You schedule tasks | Snowflake refreshes automatically |
| You manage dependencies | Snowflake manages dependencies |
| More coding | Less 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 Component | Dynamic Table Equivalent |
|---|---|
| Stream | Built into Dynamic Table |
| Task | Automatic Refresh |
| Task Schedule | TARGET_LAG |
Task Dependency (AFTER) | Dynamic Table dependency graph |
| INSERT / MERGE | SELECT 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."