Dynamic Tables in dbt + Snowflake
Part 1: Dynamic Tables
What is a Dynamic Table?
A Dynamic Table is Snowflake's modern, powerful alternative to Materialized Views. It is a physical table that Snowflake automatically refreshes based on a TARGET_LAG you define — no need to schedule dbt run for it.
Unlike Materialized Views (which cannot use JOINs, subqueries, or window functions), Dynamic Tables support full SQL — making them the go-to choice for real-time or near-real-time pipelines in Snowflake.
- Incremental: You control the refresh via
dbt runon a schedule (e.g., every hour). You write customis_incremental()logic. - Dynamic Table: Snowflake itself handles the refresh automatically. You just set a
TARGET_LAG(e.g., "refresh within 5 minutes of source change"). Zero scheduling needed.
Configuring a Dynamic Table in dbt
Example 1: Simple Aggregation (Auto-refreshed every 5 minutes)
models/realtime/rt_revenue_by_city.sql
{{ config(
materialized='dynamic_table',
snowflake_warehouse='COMPUTE_WH',
target_lag='5 minutes'
) }}
SELECT
c.city,
COUNT(o.order_id) AS total_orders,
SUM(o.amount) AS total_revenue,
MAX(o.order_date) AS latest_order_date,
AVG(o.amount) AS avg_order_value
FROM {{ ref('stg_orders') }} o
JOIN {{ ref('stg_customers') }} c
ON o.customer_id = c.customer_id
GROUP BY c.city
What happens:
- On the first
dbt run, dbt creates aDYNAMIC TABLEin Snowflake. - Snowflake's background service monitors
stg_ordersandstg_customers. - Within 5 minutes of any source change, Snowflake automatically refreshes the table.
- On subsequent
dbt runcalls, dbt does nothing — Snowflake is handling everything.
The SQL dbt compiles:
CREATE OR REPLACE DYNAMIC TABLE RT_REVENUE_BY_CITY
TARGET_LAG = '5 minutes'
WAREHOUSE = COMPUTE_WH
AS
SELECT
c.city,
COUNT(o.order_id) AS total_orders,
...
Example 2: Real-Time Customer 360 Dashboard
models/realtime/rt_customer_360.sql
{{ config(
materialized='dynamic_table',
snowflake_warehouse='COMPUTE_WH',
target_lag='10 minutes'
) }}
SELECT
c.customer_id,
c.first_name || ' ' || c.last_name AS full_name,
c.email,
COUNT(o.order_id) AS lifetime_orders,
COALESCE(SUM(o.amount), 0) AS lifetime_spend
FROM {{ ref('stg_customers') }} c
LEFT JOIN {{ ref('stg_orders') }} o
ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name, c.email
This gives your Tableau or Power BI dashboard a real-time customer 360 view that is always within 10 minutes of the live data — without any dbt scheduler, Airflow DAG, or cron job.
Example 3: Chaining Dynamic Tables (Dynamic Table → Dynamic Table)
The real power of Dynamic Tables is chaining them. The downstream Dynamic Table automatically refreshes when its upstream Dynamic Table refreshes.
models/realtime/rt_high_value_customers.sql
{{ config(
materialized='dynamic_table',
snowflake_warehouse='COMPUTE_WH',
target_lag='downstream'
) }}
SELECT
customer_id,
full_name,
email,
lifetime_orders,
lifetime_spend
FROM {{ ref('rt_customer_360') }}
WHERE lifetime_spend > 500
target_lag='downstream' mean?It tells Snowflake: "Don't refresh on your own schedule. Instead, refresh whenever the downstream consumer needs fresh data." This creates a chain reaction: when stg_orders changes → rt_customer_360 refreshes → rt_high_value_customers refreshes automatically.
Dynamic Table vs Materialized View vs Incremental
| Feature | Materialized View | Dynamic Table | Incremental |
|---|---|---|---|
| JOINs supported? | ❌ No | ✅ Yes | ✅ Yes |
| Window functions? | ❌ No | ✅ Yes | ✅ Yes |
| CTEs / Subqueries? | ❌ No | ✅ Yes | ✅ Yes |
| Auto-refresh? | ✅ By Snowflake | ✅ By Snowflake | ❌ Requires dbt run |
| Scheduling needed? | No | No | Yes (Airflow/cron) |
| Cost model | Continuous background compute | Serverless compute on change | Warehouse compute during dbt run |
| Best for | Simple single-table aggregates | Complex real-time pipelines | Large batch pipelines |