Dynamic Tables: Declarative Pipelines

For years, building data pipelines in Snowflake meant one thing: Streams and Tasks.

You created a Stream to capture changes, wrote a MERGE statement, wrapped it in a Task, and scheduled it with Cron. It worked, but it was imperative. You had to tell Snowflake how to update the data.

Dynamic Tables (DTs) change the paradigm to Declarative. You tell Snowflake what the result should look like, and it figures out the orchestration.

What is a Dynamic Table?#

A Dynamic Table matches the result of a query. That’s it. It’s like a Materialized View, but much more powerful (supports joins, aggregations, UDFs) and with a defined data lag target.

CREATE OR REPLACE DYNAMIC TABLE target_dt
  TARGET_LAG = '5 minutes'
  WAREHOUSE = my_wh
AS
SELECT
    c.customer_id,
    c.name,
    SUM(o.amount) as total_spend
FROM customer_raw c
JOIN orders_raw o ON c.customer_id = o.customer_id
GROUP BY 1, 2;

Snowflake monitors the underlying tables (customer_raw, orders_raw). When they change, it incrementally updates target_dt to ensure the data is never more than 5 minutes old.

When to use DTs vs. Streams/Tasks#

Use Dynamic Tables when:

• You want simplicity (SQL-only, no DAG management).
• You are building standard “Silver/Gold” layers in a medallion architecture.
• You need continuous data flow without complex dependency management.

Use Streams & Tasks when:

• You need fine-grained control over the “Trigger” (e.g., execute only if > 1000 rows changed).
• You perform non-SQL actions (sending emails, calling external APIs).
• You need extremely low latency (sub-minute) where DT overhead might be too high (though DTs are getting faster).

Monitoring & Cost#

Cost is the biggest concern people have with DTs. Because they auto-refresh, they consume compute credits.

• Snowsight Graph: The new Pipeline UI in Snowsight allows you to visualize the dependency chain of your DTs and see refresh history.
• Downstream Lag: Be careful chaining too many DTs (A -> B -> C -> D). If A has a 5-min lag and D has a 5-min lag, the total latency could be cumulative.
• Best Practice: Set TARGET_LAG = 'DOWNSTREAM' for intermediate tables. This tells Snowflake “only refresh this table when the final table needs it,” optimizing for cost.

Conclusion#

Dynamic Tables are the biggest quality-of-life improvement for Snowflake Data Engineers in years. They drastically reduce code volume (CREATE DYNAMIC TABLE vs STREAM + TASK + MERGE) and make pipelines easier to reason about.