Skip to main content

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.

When to use Dynamic Tables over Incremental?
  • Incremental: You control the refresh via dbt run on a schedule (e.g., every hour). You write custom is_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:

  1. On the first dbt run, dbt creates a DYNAMIC TABLE in Snowflake.
  2. Snowflake's background service monitors stg_orders and stg_customers.
  3. Within 5 minutes of any source change, Snowflake automatically refreshes the table.
  4. On subsequent dbt run calls, 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
What does 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​

FeatureMaterialized ViewDynamic TableIncremental
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?NoNoYes (Airflow/cron)
Cost modelContinuous background computeServerless compute on changeWarehouse compute during dbt run
Best forSimple single-table aggregatesComplex real-time pipelinesLarge batch pipelines