Snowflake Dynamic Tables — Comprehensive Reference Guide

Table of Contents

1. Overview
2. Declarative vs Imperative Paradigm
3. Architecture & DAG Chaining
4. Refresh Mechanism
5. Key Parameters
6. SQL Reference
7. Dynamic Tables vs Materialized Views
8. Dynamic Tables vs Streams & Tasks
9. Advantages
10. Use Cases
11. Limitations
12. Cost Considerations
13. dbt Integration
14. Monitoring & Observability
15. Best Practices
16. Decision Guide — When to Use (and When Not To)
17. Key Takeaways

1. Overview

Snowflake Dynamic Tables are a table type that materializes the result of a SQL query and is automatically kept up-to-date based on a configurable schedule called the Target Lag. Whenever the underlying source data changes, the dynamic table’s automated refresh process detects those changes and performs an incremental update — not a full data reload — ensuring the table always reflects the latest information.

Dynamic Tables simplify data engineering pipelines by removing the need for manually orchestrated Streams, Tasks, and CDC logic. You simply define what data you need through a SQL query, and Snowflake handles how and when to refresh it.

┌──────────────────────────────────────────────────────────────────┐
│                     DYNAMIC TABLE CONCEPT                        │
│                                                                  │
│   SQL Query Definition  ──►  Materialized Result ──► Auto Refresh│
│   (Declarative)              (Stored Table)          (Target Lag)│
│                                                                  │
└──────────────────────────────────────────────────────────────────┘

2. Declarative vs Imperative Paradigm

Dynamic Tables shift data engineering toward a declarative paradigm, where transformation logic is defined purely through SQL. This makes pipelines easier to understand, maintain, and evolve.

3. Architecture & DAG Chaining

Dynamic Tables can be chained together to form a Directed Acyclic Graph (DAG), where the output of one dynamic table feeds into another. This enables complex, multi-stage data transformations with built-in dependency management.

Key DAG Properties:

• Snapshot Isolation — All dynamic tables in a DAG are refreshed consistently from aligned snapshots.
• Dependency Management — Snowflake automatically resolves the refresh order based on the DAG topology.
• **DOWNSTREAM Target Lag** — Tables configured with DOWNSTREAM align their refresh schedule with their dependents.

4. Refresh Mechanism

The automated refresh process works in two key steps:

• Change Detection — Snowflake tracks changes in the underlying source data (leveraging internal stream-like mechanics).
• Merge Updates — Only the changed data is applied incrementally to the dynamic table.

Important: Refresh is automatic but not real-time. It operates on the configured Target Lag schedule. If you need sub-second freshness, Dynamic Tables are not the right solution.

Incremental Refresh Limitations

Incremental refresh cannot be used if:

Note: UNION ALL is supported for incremental refresh (with some edge cases).

5. Key Parameters

TARGET_LAG

Defines the freshness requirement for the dynamic table.

REFRESH_MODE

Note on AUTO mode: Snowflake attempts incremental refresh first and falls back to full refresh when needed. However, since AUTO behavior may change between Snowflake releases, it can cause unexpected performance variations in production pipelines. Consider using explicit modes for predictable behavior.

INITIALIZE

6. SQL Reference

Creating a Dynamic Table

CREATE OR REPLACE DYNAMIC TABLE <name>
    TARGET_LAG = { '<num> { seconds | minutes | hours | days }' | DOWNSTREAM }
    WAREHOUSE = <warehouse_name>
    INITIALIZE = ON_CREATE       -- or ON_SCHEDULE
    REFRESH_MODE = INCREMENTAL   -- or FULL or AUTO
AS
    <query>;
EXAMPLE :

CREATE DYNAMIC TABLE REPORT.QUARTERLY_CUSTOMER_AND_ORDER_BY_COUNTRY
    TARGET_LAG   = 'DOWNSTREAM'
    WAREHOUSE    = SF_US_DE_WH_01
    INITIALIZE   = ON_CREATE
    REFRESH_MODE = INCREMENTAL
AS
SELECT
    nat.N_NAME                        AS Country_Name,
    YEAR(O_ORDERDATE)                 AS Year,
    QUARTER(O_ORDERDATE)              AS Quarter,
    COUNT(DISTINCT C_NAME)            AS Number_Of_Unique_Customer,
    SUM(ord.O_TOTALPRICE)             AS Total_Order_Amount
FROM STN.STN_NATION nat
    INNER JOIN STN.STN_CUSTOMER cus
        ON cus.C_NATIONKEY = nat.N_NATIONKEY
    INNER JOIN STN.STN_ORDERS ord
        ON cus.C_CUSTKEY = ord.O_CUSTKEY
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3;

Show & Describe

-- List all dynamic tables
SHOW DYNAMIC TABLES;

-- Describe a specific dynamic table
DESCRIBE DYNAMIC TABLE <table_name>;
-- or
DESC DYNAMIC TABLE <table_name>;

Lifecycle Control

-- Suspend automatic refresh
ALTER DYNAMIC TABLE <table_name> SUSPEND;

-- Resume automatic refresh
ALTER DYNAMIC TABLE <table_name> RESUME;

-- Trigger a manual refresh
ALTER DYNAMIC TABLE <table_name> REFRESH;

-- Drop a dynamic table
DROP DYNAMIC TABLE <table_name>;

Quick Reference Table

7. Dynamic Tables vs Materialized Views

Guidance: Use a Materialized View when all you need is aggregation or transformation of a single table. Use a Dynamic Table for more complex use cases involving joins and multi-source transformations.

8. Dynamic Tables vs Streams & Tasks

9. Advantages

10. Use Cases

Real-Time / Near Real-Time Analytics

Dynamic Tables automate the refresh of analytical datasets, replacing complex Streams + Tasks pipelines with a single SQL definition.

Change Data Capture (CDC)

Dynamic Tables provide built-in CDC tracking — they automatically capture and process source data changes without requiring manual Stream setup.

Data Vault Architecture

Dynamic Tables are well-suited for the information mart layer in a Data Vault architecture:

• Data Vault is an insert-only modeling pattern; updates and deletes to source data are not required.
• Dynamic Tables act like materialized views with automated refresh, making them ideal for serving the presentation/mart layer.

Note: Dynamic Tables do not support append-only processing; they reflect the current state of the query result. This makes them suited for the mart layer, not the raw/staging vault layers.

Dynamic Iceberg Tables

Snowflake also supports Dynamic Iceberg Tables, combining the benefits of dynamic table automation with the open table format of Apache Iceberg for interoperability.

11. Limitations

Unsupported SQL Features

Operational Constraints

Summary of What You Cannot Do

• DML operations (INSERT / UPDATE / DELETE / TRUNCATE)
• Temporary dynamic tables
• Dynamic SQL / session variables
• Sequences (e.g., my_sequence.NEXTVAL)
• Non-deterministic functions in queries
• External / shared tables as sources
• Materialized views as sources
• Stored procedures / complex branching logic
• SAMPLE / TABLESAMPLE
• Set DATA_RETENTION_TIME_IN_DAYS = 0 on source tables

12. Cost Considerations

Dynamic Tables have two primary cost components:

💡 Tip: Use transient dynamic tables for intermediate transformation layers where data durability is not critical. This avoids Time Travel and Fail-safe storage overhead.

13. dbt Integration

Creating a dynamic table in dbt is straightforward — add the following configuration block to your .sql model file:

{{ config(
    materialized = "dynamic_table",
    on_configuration_change = "apply",       -- Options: "apply" | "continue" | "fail"
    target_lag = "downstream",               -- Options: "downstream" | "<N> seconds | minutes | hours | days"
    snowflake_warehouse = "<warehouse-name>",
    refresh_mode = "AUTO",                   -- Options: "AUTO" | "FULL" | "INCREMENTAL"
    initialize = "ON_CREATE"                 -- Options: "ON_CREATE" | "ON_SCHEDULE"
) }}
SELECT
    ...
FROM {{ ref('source_model') }}

dbt Configuration Parameters

14. Monitoring & Observability

Refresh History

Query the refresh history to monitor pipeline health and identify failures:

SELECT *
FROM TABLE(
    INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(
        NAME_PREFIX => 'DEMO_DB.PUBLIC.',
        ERROR_ONLY  => TRUE
    )
)
ORDER BY name, data_timestamp;

DAG Graph History

Visualize the dependency graph and refresh topology:

SELECT *
FROM TABLE(
    INFORMATION_SCHEMA.DYNAMIC_TABLE_GRAPH_HISTORY()
);

Monitoring Checklist

• Review DYNAMIC_TABLE_REFRESH_HISTORY regularly for errors
• Monitor refresh duration trends for performance regression
• Check DYNAMIC_TABLE_GRAPH_HISTORY for DAG topology issues
• Validate that Target Lag requirements are being met
• Track warehouse credit consumption for refresh operations

15. Best Practices

1 Use INCREMENTAL mode for large datasets Minimizes compute cost and refresh time

2 Avoid non-deterministic functions Maintains incremental refresh eligibility

3 Keep transformations deterministic Ensures predictable, repeatable results

4 Monitor refresh history regularly Catches failures and performance degradation early

5 Use DAG chaining carefully Overly deep DAGs increase latency and complexity

6 Prefer explicit REFRESH_MODE over AUTO in production Avoids unexpected behavior changes across Snowflake releases

7 Use transient dynamic tables for intermediate layers Reduces storage cost

8 Enable Time Travel on all source tables Required for change tracking; DATA_RETENTION_TIME_IN_DAYS ≠ 0

9 Integrate with dbt for version control and CI/CD Enables automated testing, documentation, and lineage tracking 10 Stick to deterministic SQL Avoid RAND(), CURRENT_TIMESTAMP, stored procedures

16. Decision Guide

When to Use Dynamic Tables

• You want automated, declarative transformations
• You want to avoid the complexity of Streams & Tasks
• You need multi-table joins in your transformation
• You don’t require strict real-time latency
• You want built-in CDC without manual setup

When NOT to Use Dynamic Tables

• You need real-time (sub-second) updates
• You require complex procedural or branching logic
• You need fine-grained control over refresh schedules
• Your queries depend on unsupported SQL features (external functions, non-deterministic functions)
• You need DML operations on the target table

17. Key Takeaways

• Dynamic Tables = Materialized Views + Automation + CDC

• They REDUCE pipeline complexity significantly

• Best suited for ANALYTICS & TRANSFORMATION layers

• NOT a full replacement for ALL pipeline patterns

• Use DECLARATIVE SQL — avoid procedural logic

• Integrate with dbt for production-grade CI/CD

• Monitor with REFRESH_HISTORY & GRAPH_HISTORY

• Keep SQL DETERMINISTIC for incremental refresh support