Implementing Snowflake Dynamic Tables for Incremental Refresh: Replacing dbt Scheduled Runs with Warehouse-Native Continuous Transformation

Introduction

Your Airflow DAG fires at 6 AM and kicks off a dbt run. By 6:47 AM, every model in your project has been rebuilt, your warehouse bill has ticked up, and your analysts — who arrive at 8 AM — are working with data that's already nearly two hours stale before they even open their laptops. Meanwhile, your sales team is asking why the pipeline dashboard is showing yesterday's numbers at 9 AM, and your data engineers are defending a schedule that made sense six months ago but hasn't kept pace with the business.

This is the latency trap of scheduled batch transformation. And it's not an Airflow problem or a dbt problem — it's a fundamental tension between the "run on a clock" mental model and the continuous, event-driven world that modern data sources actually live in. Snowflake Dynamic Tables are a first-class solution to this problem. They shift the responsibility of when to refresh away from an external scheduler and into the warehouse itself, letting you declare a target data freshness — say, one minute — and let Snowflake figure out the cheapest incremental update plan to honor it.

By the end of this lesson, you'll understand not just how Dynamic Tables work mechanically, but when to use them instead of dbt incremental models, how to structure a full multi-layer pipeline using them, and how to migrate an existing scheduled dbt workflow to warehouse-native continuous transformation. You'll also know the gotchas — and there are a few — before they bite you in production.

What you'll learn:

• How Snowflake Dynamic Tables work under the hood, including the incremental refresh engine and lag semantics
• When Dynamic Tables outperform dbt scheduled models, and when they don't
• How to build a multi-layer pipeline (staging → intermediate → mart) using Dynamic Tables with realistic DDL
• How to migrate an existing dbt project incrementally, without a full rewrite
• How to monitor, troubleshoot, and control costs for Dynamic Table workloads

Prerequisites

You should be comfortable with SQL and have hands-on experience with Snowflake — you've written queries, understand how virtual warehouses work, and have at least read about dbt fundamentals and how it transforms data in the warehouse. You don't need to be a dbt expert, but you should know what a materialized view is and roughly how incremental models work. If you haven't yet worked through the concepts behind incremental models and late-arriving data, skim that first — it'll make the tradeoffs here land harder.

---

What Are Snowflake Dynamic Tables, Really?

Before we write any DDL, let's establish a precise mental model, because "it's like a materialized view" is true but undersells the difference.

A standard materialized view in Snowflake is a precomputed result set that the warehouse refreshes automatically, but it has significant restrictions: no joins across multiple source tables in many configurations, no support for aggregations with certain functions, and no fine-grained control over refresh timing. Dynamic Tables remove all of those restrictions. They're defined by a SELECT query — as complex as you want, with joins, aggregations, window functions, CTEs — and Snowflake's refresh engine continuously monitors their upstream dependencies and re-materializes only the rows that have changed.

That last phrase is the important one: only the rows that have changed. Snowflake's Dynamic Table engine tracks changes at the micro-partition level using the same change-tracking infrastructure that powers Snowflake Streams. When a source table gets new rows, or rows are updated or deleted, the engine computes a minimal delta and applies it to the Dynamic Table rather than re-scanning the entire dataset.

The other concept to nail down is target lag. When you create a Dynamic Table, you specify a TARGET_LAG — a duration like '1 minute', '5 minutes', or '1 hour'. This is not a cron schedule. It's a freshness SLA: a declaration that the data in this table should never be more than N minutes behind its source. Snowflake's scheduler continuously checks whether the downstream table is within the lag window and triggers a refresh if it's about to breach it. If your source hasn't changed, no refresh happens at all — no compute spend, no busy work.

This is a fundamentally different contract than 0 6 * * *. A cron schedule says "run at this time regardless of whether anything changed." A target lag says "stay this fresh regardless of when changes arrive."

---

The Architecture: Dynamic Tables as a DAG

One of the most elegant properties of Dynamic Tables is that you can chain them. If staging_orders is a Dynamic Table built from raw raw.orders, and int_orders_enriched is a Dynamic Table built from staging_orders, Snowflake understands that dependency chain automatically. When raw.orders gets new data, the refresh cascades: staging_orders refreshes first, then int_orders_enriched refreshes from the updated staging layer.

This is functionally equivalent to what you'd orchestrate manually with Airflow DAG dependencies, except the dependency graph is inferred from the SQL itself rather than declared in Python. No TaskGroup, no trigger_rule, no retry logic — the warehouse manages it.

Let me show you what a realistic three-layer pipeline looks like. We'll use an e-commerce scenario: raw order data arriving continuously from a Fivetran-managed Postgres source, with a staging layer, an intermediate enrichment layer, and a mart layer consumed by a BI tool.

Layer 1: Staging Dynamic Table

CREATE OR REPLACE DYNAMIC TABLE staging.stg_orders
  TARGET_LAG = '2 minutes'
  WAREHOUSE = compute_wh_xs
AS
SELECT
    o.order_id,
    o.customer_id,
    o.created_at::TIMESTAMP_NTZ                        AS order_created_at,
    o.updated_at::TIMESTAMP_NTZ                        AS order_updated_at,
    UPPER(TRIM(o.status))                              AS order_status,
    o.subtotal_cents / 100.0                           AS subtotal_usd,
    o.shipping_cents / 100.0                           AS shipping_usd,
    o.tax_cents / 100.0                                AS tax_usd,
    (o.subtotal_cents + o.shipping_cents + o.tax_cents)
        / 100.0                                        AS total_usd,
    o.currency_code,
    o._fivetran_synced                                 AS source_synced_at
FROM raw.orders o
WHERE o._fivetran_deleted = FALSE;

A few things to notice here. First, the WAREHOUSE clause — Dynamic Tables require a dedicated or shared virtual warehouse to run their refreshes. This isn't the same warehouse that your analysts query from; best practice is to use a small, dedicated compute warehouse (XS or S) for transformation workloads. Second, TARGET_LAG = '2 minutes' means staging data will be at most two minutes behind the Fivetran-landed raw data. Third, we filter _fivetran_deleted = FALSE right at the staging layer — the kind of cleanup logic that's genuinely simple and appropriate here.

Layer 2: Intermediate Enrichment

CREATE OR REPLACE DYNAMIC TABLE intermediate.int_orders_with_customer
  TARGET_LAG = '5 minutes'
  WAREHOUSE = compute_wh_xs
AS
SELECT
    o.order_id,
    o.customer_id,
    o.order_created_at,
    o.order_updated_at,
    o.order_status,
    o.subtotal_usd,
    o.shipping_usd,
    o.tax_usd,
    o.total_usd,
    o.currency_code,
    o.source_synced_at,
    c.email                                            AS customer_email,
    c.full_name                                        AS customer_name,
    c.signup_date                                      AS customer_signup_date,
    c.segment                                          AS customer_segment,
    DATEDIFF('day', c.signup_date, o.order_created_at) AS days_since_signup
FROM staging.stg_orders o
LEFT JOIN staging.stg_customers c
    ON o.customer_id = c.customer_id;

Notice that int_orders_with_customer references staging.stg_orders, which is itself a Dynamic Table. Snowflake sees this dependency and will not refresh the intermediate table until its upstream staging table has completed its own refresh. The TARGET_LAG of 5 minutes on the intermediate layer is intentionally looser — it allows Snowflake to batch intermediate refreshes efficiently rather than triggering a cascade for every micro-partition change.

Layer 3: Mart

CREATE OR REPLACE DYNAMIC TABLE marts.orders_daily_summary
  TARGET_LAG = '30 minutes'
  WAREHOUSE = compute_wh_xs
AS
SELECT
    DATE_TRUNC('day', order_created_at)                AS order_date,
    customer_segment,
    COUNT(DISTINCT order_id)                           AS total_orders,
    COUNT(DISTINCT customer_id)                        AS unique_customers,
    SUM(total_usd)                                     AS gross_revenue_usd,
    SUM(subtotal_usd)                                  AS product_revenue_usd,
    AVG(total_usd)                                     AS avg_order_value_usd,
    COUNT(DISTINCT CASE WHEN order_status = 'REFUNDED'
        THEN order_id END)                             AS refunded_orders,
    SUM(CASE WHEN order_status = 'REFUNDED'
        THEN total_usd ELSE 0 END)                     AS refunded_revenue_usd
FROM intermediate.int_orders_with_customer
GROUP BY 1, 2;

This mart aggregates daily revenue by customer segment — exactly the kind of table a BI tool like Tableau or Looker queries for a dashboard. With a 30-minute target lag, the dashboard data is never more than 30 minutes stale. With a traditional hourly dbt run plus pipeline wait time, it was often 2-3 hours stale. And crucially, the mart only recomputes when the intermediate layer actually changes, not on a fixed schedule.

---

Dynamic Tables vs. dbt Incremental Models: Choosing the Right Tool

This is where practitioners usually need to slow down, because the answer isn't "Dynamic Tables are always better." Let's be honest about the tradeoffs.

Dynamic Tables win when:

• Your source data arrives continuously (via CDC, Kafka, or frequent Fivetran/Airbyte syncs)
• You need sub-hourly freshness and your batch schedule can't keep up
• Your transformation logic can be expressed as a pure SELECT (no procedural logic)
• You want to eliminate the operational burden of scheduling and dependency management

dbt incremental models win when:

• You need fine-grained control over merge strategy (e.g., custom unique key logic, delete+insert)
• Your pipeline involves slowly changing dimensions where you're explicitly writing history rows
• You need to run Python models or macro-generated SQL that Dynamic Tables can't express
• You're heavily invested in dbt's testing, documentation, and lineage ecosystem and want to keep everything in one place

The honest answer for most teams is a hybrid approach: Dynamic Tables for the high-frequency, latency-sensitive layers (staging and intermediate), and dbt models for complex mart-layer logic that needs merge semantics or business-rule enforcement. The two aren't mutually exclusive — a dbt model can query a Dynamic Table as a source, and a Dynamic Table can sit downstream of a dbt-managed staging layer.

If you're currently using Airflow to orchestrate dbt runs with complex dependencies, Dynamic Tables won't necessarily replace that entire stack — but they can remove the most latency-sensitive models from the scheduled DAG, letting Airflow focus on the parts that genuinely need orchestration (like triggering reports after a specific event, or managing dependencies across systems).

---

Migrating an Existing dbt Project: A Practical Approach

Let's say you have a dbt project with 40 models, and your most latency-sensitive pipeline is a chain: stg_events → int_session_events → fct_user_sessions. Currently this runs hourly via Airflow + dbt. Here's how to migrate it without blowing up your existing project.

Step 1: Enable Change Tracking on Source Tables

Dynamic Tables require change tracking on their upstream base tables. If your Fivetran-landed tables live in raw, run:

ALTER TABLE raw.events SET CHANGE_TRACKING = TRUE;
ALTER TABLE raw.users SET CHANGE_TRACKING = TRUE;

Step 2: Identify Models That Can Move

Look at your dbt models and ask three questions for each one:

1. Is this model a pure SELECT with no procedural logic or macro-generated DDL tricks?
2. Does this model use any SQL constructs that Dynamic Tables don't support?
3. Would this model benefit from sub-hourly freshness, or is hourly fine?

For our example chain, stg_events and int_session_events are pure selects that would genuinely benefit from low latency. fct_user_sessions involves a complex session boundary calculation using window functions — Dynamic Tables support window functions fine, but the merge logic requires careful thought.

Step 3: Create Parallel Dynamic Table Versions

Don't delete your dbt models yet. Create the Dynamic Tables in a parallel schema:

-- Create a new schema for Dynamic Table versions
CREATE SCHEMA IF NOT EXISTS dynamic_staging;
CREATE SCHEMA IF NOT EXISTS dynamic_intermediate;

-- Mirror of stg_events dbt model, now as a Dynamic Table
CREATE OR REPLACE DYNAMIC TABLE dynamic_staging.stg_events
  TARGET_LAG = '1 minute'
  WAREHOUSE = transform_wh_xs
AS
SELECT
    event_id,
    user_id,
    session_id,
    event_type,
    event_timestamp::TIMESTAMP_NTZ  AS event_at,
    page_url,
    referrer_url,
    device_type,
    PARSE_JSON(event_properties)    AS properties,
    _fivetran_synced                AS source_synced_at
FROM raw.events
WHERE _fivetran_deleted = FALSE
  AND event_timestamp >= DATEADD('day', -90, CURRENT_TIMESTAMP());

The 90-day filter deserves explanation. Dynamic Tables maintain a full refresh on initial creation, then switch to incremental. For a table with years of event history, you almost never need the full history in a session analysis mart — scope your initial query appropriately to control the initial load cost.

Step 4: Validate Parity

Before switching BI tooling or downstream consumers, validate that the Dynamic Table output matches your dbt model output. Run a reconciliation query:

-- Count parity check
SELECT
    'dbt_model'    AS source,
    COUNT(*)       AS row_count,
    COUNT(DISTINCT user_id) AS unique_users,
    MIN(event_at)  AS earliest_event,
    MAX(event_at)  AS latest_event
FROM analytics.stg_events  -- dbt-managed table

UNION ALL

SELECT
    'dynamic_table' AS source,
    COUNT(*)        AS row_count,
    COUNT(DISTINCT user_id) AS unique_users,
    MIN(event_at)   AS earliest_event,
    MAX(event_at)   AS latest_event
FROM dynamic_staging.stg_events;  -- Dynamic Table

Run this after several refresh cycles. If counts diverge, dig into whether your dbt model has custom filter logic or incremental conditions that the Dynamic Table doesn't replicate.

Step 5: Cut Over and Deprecate

Once parity is confirmed, update downstream consumers (the intermediate models, BI tool connections, or any reverse ETL tools that query staging) to point at the Dynamic Tables. Then deprecate the dbt models — either remove them from the project or disable them with a config(enabled=false) block.

---

Monitoring and Observability

Dynamic Tables are easier to operate than scheduled pipelines in many ways — but you're trading explicit scheduler logs for a new set of observability surfaces. Here's what to monitor.

Checking Refresh Status

Snowflake provides built-in views for Dynamic Table observability:

-- Check the refresh history for a specific Dynamic Table
SELECT
    name,
    schema_name,
    state,
    state_code,
    state_message,
    query_id,
    data_timestamp,
    refresh_start_time,
    refresh_end_time,
    DATEDIFF('second', refresh_start_time, refresh_end_time) AS refresh_duration_sec,
    rows_inserted,
    rows_deleted,
    bytes_written
FROM TABLE(
    INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(
        NAME => 'DYNAMIC_STAGING.STG_EVENTS'
    )
)
ORDER BY refresh_start_time DESC
LIMIT 50;

This query gives you a row per refresh attempt with status, duration, and row counts. Build this into a monitoring dashboard — if state is consistently FAILED or refresh durations are growing, you need to know before your SLAs breach.

Measuring Actual Lag

Target lag is a promise, not a guarantee. Measure the actual lag:

-- Measure current actual lag across all Dynamic Tables in a schema
SELECT
    table_name,
    target_lag,
    scheduling_state,
    DATEDIFF('second',
        data_timestamp,
        CURRENT_TIMESTAMP())       AS actual_lag_seconds,
    data_timestamp                 AS data_as_of
FROM information_schema.dynamic_tables
WHERE table_schema = 'DYNAMIC_STAGING'
ORDER BY actual_lag_seconds DESC;

If actual_lag_seconds is consistently much higher than your TARGET_LAG, you have a resource contention problem — your warehouse is too small for the refresh frequency, or concurrent queries are blocking the refresh jobs.

Setting Up Alerts

Snowflake doesn't have native alerting on Dynamic Table lag (as of this writing), but you can build a lightweight alerting pattern using Snowflake Tasks + a monitoring table:

-- A task that checks lag and inserts alerts
CREATE OR REPLACE TASK monitoring.check_dynamic_table_lag
  WAREHOUSE = monitoring_wh_xs
  SCHEDULE = '5 minutes'
AS
INSERT INTO monitoring.dt_lag_alerts (table_name, actual_lag_seconds, alert_time)
SELECT
    table_name,
    DATEDIFF('second', data_timestamp, CURRENT_TIMESTAMP()) AS actual_lag_seconds,
    CURRENT_TIMESTAMP()
FROM information_schema.dynamic_tables
WHERE table_schema IN ('DYNAMIC_STAGING', 'DYNAMIC_INTERMEDIATE', 'MARTS')
  AND DATEDIFF('second', data_timestamp, CURRENT_TIMESTAMP())
      > CASE
            WHEN target_lag = '1 minute'  THEN 180    -- 3x target
            WHEN target_lag = '5 minutes' THEN 900
            WHEN target_lag = '30 minutes' THEN 3600
            ELSE 7200
        END;

ALTER TASK monitoring.check_dynamic_table_lag RESUME;

This pattern — a Snowflake Task querying information_schema and writing alerts — is warehouse-native monitoring without external tooling. You can then route those alerts through whatever notification system you use (Slack, PagerDuty, email via your data quality framework).

For more sophisticated freshness monitoring across your entire stack, see Automating Data Freshness SLAs for patterns that work across Dynamic Tables and dbt models simultaneously.

---

Cost Management for Dynamic Table Workloads

The incremental refresh model is inherently more cost-efficient than full rebuilds, but it introduces a new cost profile: many small compute bursts instead of one large scheduled run. This changes how you need to think about warehouse sizing.

The key cost levers are:

Warehouse size: Dynamic Table refreshes for simple staging transformations rarely need more than XS or S. The incremental scan is small by definition — only changed micro-partitions. Use XS for staging and intermediate; consider S for mart aggregations that touch more data.

Target lag tuning: The looser the lag, the less frequent the refresh, the lower the compute cost. If your analysts only look at the dashboard once an hour, a 30-minute lag delivers the same user experience as a 1-minute lag at 1/30th the refresh frequency.

Warehouse auto-suspend: Set AUTO_SUSPEND = 60 on your transformation warehouse (60 seconds). Refresh jobs claim the warehouse, run their delta computation, and release it. With auto-suspend, you only pay for the seconds of actual compute, not idle time between refreshes.

-- Dedicated transformation warehouse with aggressive auto-suspend
CREATE OR REPLACE WAREHOUSE transform_dt_wh
  WAREHOUSE_SIZE = XSMALL
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE
  COMMENT = 'Dedicated warehouse for Dynamic Table refresh workloads';

For a deeper dive into warehouse cost optimization patterns, Cost Management in Cloud Data Platforms covers resource monitors and budget alerts that work well alongside Dynamic Table workloads.

---

Hands-On Exercise

Let's build a complete, working pipeline from scratch. You'll need a Snowflake account with ACCOUNTADMIN or SYSADMIN access. We'll simulate a streaming e-commerce event source using Snowflake's own data generation features.

Setup

-- Create the schema hierarchy
CREATE DATABASE IF NOT EXISTS dt_workshop;
CREATE SCHEMA IF NOT EXISTS dt_workshop.raw;
CREATE SCHEMA IF NOT EXISTS dt_workshop.staging;
CREATE SCHEMA IF NOT EXISTS dt_workshop.intermediate;
CREATE SCHEMA IF NOT EXISTS dt_workshop.marts;

-- Create a dedicated warehouse
CREATE WAREHOUSE IF NOT EXISTS dt_workshop_wh
  WAREHOUSE_SIZE = XSMALL
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

Create a Simulated Source Table

-- Raw orders table with change tracking enabled from the start
CREATE OR REPLACE TABLE dt_workshop.raw.orders (
    order_id        INTEGER,
    customer_id     INTEGER,
    product_sku     VARCHAR(20),
    quantity        INTEGER,
    unit_price_usd  DECIMAL(10,2),
    status          VARCHAR(20),
    created_at      TIMESTAMP_NTZ,
    updated_at      TIMESTAMP_NTZ,
    _loaded_at      TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
)
CHANGE_TRACKING = TRUE;

-- Seed with 100 initial orders
INSERT INTO dt_workshop.raw.orders
    (order_id, customer_id, product_sku, quantity, unit_price_usd, status, created_at, updated_at)
SELECT
    SEQ4() + 1                                                        AS order_id,
    UNIFORM(1, 50, RANDOM())                                          AS customer_id,
    'SKU-' || UNIFORM(100, 999, RANDOM())                             AS product_sku,
    UNIFORM(1, 5, RANDOM())                                           AS quantity,
    ROUND(UNIFORM(10, 500, RANDOM()) + UNIFORM(0, 99, RANDOM())/100, 2) AS unit_price_usd,
    CASE UNIFORM(1, 4, RANDOM())
        WHEN 1 THEN 'pending'
        WHEN 2 THEN 'confirmed'
        WHEN 3 THEN 'shipped'
        ELSE 'delivered'
    END                                                               AS status,
    DATEADD('hour', -UNIFORM(1, 720, RANDOM()), CURRENT_TIMESTAMP())  AS created_at,
    CURRENT_TIMESTAMP()                                               AS updated_at
FROM TABLE(GENERATOR(ROWCOUNT => 100));

Build the Dynamic Table Pipeline

-- Layer 1: Staging
CREATE OR REPLACE DYNAMIC TABLE dt_workshop.staging.stg_orders
  TARGET_LAG = '1 minute'
  WAREHOUSE = dt_workshop_wh
AS
SELECT
    order_id,
    customer_id,
    product_sku,
    quantity,
    unit_price_usd,
    UPPER(status)                                AS order_status,
    quantity * unit_price_usd                    AS line_total_usd,
    created_at                                   AS order_created_at,
    updated_at                                   AS order_updated_at,
    _loaded_at                                   AS source_loaded_at
FROM dt_workshop.raw.orders;

-- Layer 2: Intermediate aggregation per customer
CREATE OR REPLACE DYNAMIC TABLE dt_workshop.intermediate.int_customer_order_summary
  TARGET_LAG = '3 minutes'
  WAREHOUSE = dt_workshop_wh
AS
SELECT
    customer_id,
    COUNT(DISTINCT order_id)                     AS total_orders,
    SUM(line_total_usd)                          AS lifetime_value_usd,
    AVG(line_total_usd)                          AS avg_order_value_usd,
    MIN(order_created_at)                        AS first_order_at,
    MAX(order_created_at)                        AS latest_order_at,
    COUNT(DISTINCT CASE WHEN order_status = 'DELIVERED'
        THEN order_id END)                       AS delivered_orders,
    SUM(CASE WHEN order_status IN ('PENDING','CONFIRMED','SHIPPED')
        THEN line_total_usd ELSE 0 END)          AS open_order_value_usd
FROM dt_workshop.staging.stg_orders
GROUP BY customer_id;

-- Layer 3: Mart - customer segments by LTV
CREATE OR REPLACE DYNAMIC TABLE dt_workshop.marts.customer_segments
  TARGET_LAG = '10 minutes'
  WAREHOUSE = dt_workshop_wh
AS
SELECT
    customer_id,
    lifetime_value_usd,
    avg_order_value_usd,
    total_orders,
    delivered_orders,
    open_order_value_usd,
    first_order_at,
    latest_order_at,
    CASE
        WHEN lifetime_value_usd >= 2000 THEN 'VIP'
        WHEN lifetime_value_usd >= 500  THEN 'loyal'
        WHEN total_orders >= 2          THEN 'repeat'
        ELSE 'new'
    END                                          AS customer_segment,
    CURRENT_TIMESTAMP()                          AS mart_refreshed_at
FROM dt_workshop.intermediate.int_customer_order_summary;

Test the Incremental Refresh

Now simulate new orders arriving and watch the pipeline update:

-- Insert 10 new orders (simulating a new Fivetran sync)
INSERT INTO dt_workshop.raw.orders
    (order_id, customer_id, product_sku, quantity, unit_price_usd, status, created_at, updated_at)
SELECT
    101 + SEQ4()                                                      AS order_id,
    UNIFORM(1, 50, RANDOM())                                          AS customer_id,
    'SKU-' || UNIFORM(100, 999, RANDOM())                             AS product_sku,
    UNIFORM(1, 5, RANDOM())                                           AS quantity,
    ROUND(UNIFORM(10, 500, RANDOM()) + UNIFORM(0, 99, RANDOM())/100, 2) AS unit_price_usd,
    'pending'                                                         AS status,
    CURRENT_TIMESTAMP()                                               AS created_at,
    CURRENT_TIMESTAMP()                                               AS updated_at
FROM TABLE(GENERATOR(ROWCOUNT => 10));

-- Wait 90 seconds, then check the mart
-- (The cascade: raw → staging (1 min) → intermediate (3 min) → mart (10 min))

SELECT customer_segment, COUNT(*) AS customer_count, SUM(lifetime_value_usd) AS segment_ltv
FROM dt_workshop.marts.customer_segments
GROUP BY customer_segment
ORDER BY segment_ltv DESC;

After about 90 seconds, your staging table will have refreshed to include the new orders. After 3 minutes, the intermediate aggregations will update. After 10 minutes, the mart will reflect the new customer segments. Watch the refresh history to verify:

SELECT state, refresh_start_time, refresh_end_time, rows_inserted, rows_deleted
FROM TABLE(
    INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(
        NAME => 'DT_WORKSHOP.STAGING.STG_ORDERS'
    )
)
ORDER BY refresh_start_time DESC
LIMIT 10;

---

Common Mistakes & Troubleshooting

Mistake 1: Forgetting change tracking on source tables

Symptom: CREATE DYNAMIC TABLE fails with an error about change tracking not being enabled.

Fix: ALTER TABLE raw.my_table SET CHANGE_TRACKING = TRUE; — and remember to do this before creating the Dynamic Table, not after.

Mistake 2: Setting aggressive lag on all layers

Symptom: Your XS warehouse never auto-suspends; compute costs spike dramatically.

Fix: Stagger lag windows across layers as described above. Audit actual business requirements — most analyst dashboards don't need sub-5-minute freshness.

Mistake 3: Using non-deterministic functions

Symptom: Dynamic Table creation fails or produces unexpected results on incremental refresh.

Fix: Avoid CURRENT_TIMESTAMP(), RANDOM(), UUID_STRING() in the core transformation logic of your Dynamic Tables. These are only safe in tracking columns (like the mart_refreshed_at column in the exercise above) and even then, test carefully. For timestamp-based logic, use the source column's timestamp, not CURRENT_TIMESTAMP().

Mistake 4: Unbounded historical scans on initial creation

Symptom: Initial Dynamic Table creation takes 45 minutes and costs more than expected.

Fix: Add a date filter in your staging Dynamic Table to limit the initial full scan. Something like WHERE created_at >= DATEADD('year', -2, CURRENT_TIMESTAMP()) is usually appropriate. Incremental refreshes after the initial load only process changes, so this only affects the first build.

Mistake 5: Querying Dynamic Tables with stale cache assumptions

Symptom: Analysts complain that the data is stale even though the Dynamic Table says it's fresh.

Fix: Check whether your BI tool is caching query results. Tableau, Looker, and Power BI all have result caching layers that can serve stale data even when the underlying warehouse table has been refreshed. This is not a Dynamic Table problem — it's a BI tool configuration issue.

Mistake 6: Overlooking downstream lineage

Snowflake doesn't yet natively surface Dynamic Table lineage in all third-party data catalog tools. If your team relies on data lineage tracking across the stack, test your lineage tooling against Dynamic Tables before full migration. Some tools parse SQL definitions to infer lineage; others rely on query history events, which Dynamic Table refreshes do emit.

---

Summary & Next Steps

Snowflake Dynamic Tables represent a genuine architectural shift — not just a convenience feature. By moving transformation scheduling into the warehouse itself and grounding freshness in a lag-based contract rather than a clock-based schedule, they eliminate an entire category of operational complexity: the cron schedule, the dependency orchestration, the "did it run?" monitoring, the late-arriving data patches. For latency-sensitive pipelines where data changes continuously, they're often the right tool.

That said, they're not a universal replacement for dbt. Complex merge logic, slowly changing dimensions, Python models, and dbt's testing and documentation ecosystem all remain compelling reasons to keep dbt in your stack. The winning architecture for most teams is a hybrid: Dynamic Tables handling the high-frequency, low-complexity transformation layers, with dbt managing the business-rule-heavy mart layer and providing the testing and governance layer on top.

Where to go from here:

• Explore the full end-to-end real-time pipeline pattern — combining Kafka ingestion with Dynamic Tables and sub-minute analytics — in Designing and Implementing a Real-Time Ingestion Pipeline with Kafka, dbt, and Snowflake Dynamic Tables
• If you're maintaining a dbt project alongside Dynamic Tables, Profiling and Optimizing Slow dbt Models will help you identify which models are candidates for migration based on actual query plan data
• For teams building a medallion architecture — bronze/silver/gold layering — Dynamic Tables map naturally to the bronze-to-silver transition and are worth evaluating as a replacement for scheduled silver-layer dbt models

The best way to build intuition for Dynamic Tables is to run the hands-on exercise above against real data in your own warehouse — watch the refresh history, instrument the lag metrics, and compare the compute costs against your existing scheduled run. The numbers usually make the case on their own.