βοΈ Dynamic Tables in Snowflake
-
Dynamic Tables in Snowflake are next-generation, self-refreshing tables that automatically refresh based on a defined query and target freshness.
-
They simplify data pipelines by combining the capabilities of Materialized Views and Stream + Task pipelines, without the need for external schedulers.
-
Pls Note: Dynamic Tables are a special type of managed object in Snowflake β they are not permanent, transient, or temporary tables.
-
Instead, they are "their own category, with some unique lifecycle and metadata characteristics.
π Overview & Why Dynamic Tables
Dynamic Tables in Snowflake are self-refreshing, dependency-aware result sets that stay updated automatically based on a defined freshness target (TARGET_LAG).
They simplify pipeline management by combining the best of Materialized Views and Streams + Tasks, without any manual orchestration.
βοΈ What Dynamic Tables Automatically Manageβ
| Concept | Description |
|---|---|
| Incremental Refresh | Automatically processes only new or changed data instead of reloading the full dataset, optimizing performance and compute cost. |
| Data Dependencies | Automatically tracks and maintains refresh order across dependent tables (DAG), ensuring consistent data snapshots in multi-hop pipelines. |
| Orchestration | Automatically coordinates refreshes across all dependencies to meet freshness SLAs β no cron jobs, tasks, or external schedulers needed. |
π§ In short: You define what your data should look like and how fresh it should be β Snowflake handles how and when to refresh it.
π§© Why Dynamic Tables?β
Before Dynamic Tables, engineers relied on:
- Materialized Views (MVs) β auto-refreshing but limited SQL capabilities
- Streams + Tasks β highly flexible but manually orchestrated
Dynamic Tables unify both worlds by offering:
- Declarative pipeline definition β specify outcome, not schedule
- Automated incremental refresh and dependency handling
- Freshness SLAβdriven execution, managed entirely by Snowflake
π§± Under the Hoodβ
- Internally, Snowflake stores Dynamic Tables as managed tables with system metadata tracking refresh states and dependencies.
- They are not classified as permanent, transient, or temporary tables β the system manages them as part of the Dynamic Table framework.
- Each refresh is warehouse-driven and optimized automatically by Snowflakeβs internal engine.
π‘ Think of Dynamic Tables as declarative, self-healing data pipelines β always fresh, dependency-aware, and cost-optimized, with zero manual orchestration.
βοΈ Key Features
| Feature | Description |
|---|---|
| Declarative Pipelines | - Define final dataset; - Snowflake handles the refresh logic automatically. - A declarative pipeline means you declare what the final data should look like, and the system (in this case, Snowflake) automatically figures out how and when to make it happen. |
Target Freshness (TARGET_LAG) | Ensures your data is updated according to the freshness interval you specify. |
| Dependency Awareness | Tables are refreshed in the correct order based on dependencies. |
| Complex Query Support | Supports joins, aggregations, window functions, and CTEs β much more powerful than MVs. |
| Incremental Computation | Only changed rows are processed, reducing compute cost. |
| Warehouse-Aware Execution | Specify the warehouse for table refresh computation. |
| DAG-Based Orchestration | Automatically manages multi-layer dependencies in pipelines. |
π Typical Use Cases
Dynamic Tables are ideal in scenarios where you want:
- To materialize query results without writing custom orchestration code
- To avoid manually tracking data dependencies and refresh schedules
- To chain multiple tables for data transformations
- To focus on pipeline outcomes declaratively, while Snowflake handles scheduling and execution
Common use cases include:
- Incremental ETL pipelines β automate bronze β silver β gold transformations
- Materialized data marts β maintain pre-aggregated summaries for analytics
- Chained transformations β multi-hop pipeline orchestration
- Real-time dashboards β near-real-time updates without manual refreshes
- Data quality & audit metrics β continuously compute validation KPIs
- ML feature tables β keep model-ready data always up-to-date
π§± Architecture Concept
Dynamic Tables follow a multi-hop architecture similar to medallion layering:
RAW β BRONZE β SILVER β GOLD
β β β
Hop 1 Hop 2 Hop 3
- Each layer depends on the previous layer
- Snowflake tracks dependencies and refreshes in correct order
- Ensures consistent snapshots across the pipeline
π Comparison with Other Approaches
| Feature | Dynamic Table (DT) | Streams + Tasks | Materialized View (MV) |
|---|---|---|---|
| Auto-refresh |
| β No built-in auto-refresh; requires explicit task scheduling or chaining. |
|
| Complex SQL (joins, aggregates) |
| β Fully flexible β any SQL logic supported. | β Restricted to a single base table and limited expressions. |
| Handles dependencies |
| β Task dependencies limited to same schema (no cross-schema chaining). | β No dependency awareness β must refresh manually if multi-level dependencies exist. |
| Scheduler required | β Managed internally by Snowflake; you only define freshness ( | β Must manually define schedules or AFTER dependencies. | β Handled internally, but refresh timing cannot be customized. |
| Incremental computation |
|
|
|
| Warehouse control |
| β Full control β each task runs on a specific warehouse. | β Uses Snowflake-managed compute β no visibility or control over cost. |
| Monitoring / lineage visibility |
|
| β οΈ Limited metadata visibility. |
| Cross-schema / cross-db support | β Fully supported β dependencies can span DBs & schemas. | β AFTER chaining limited to same schema. | β Must be in same schema as base table. |
| Use case fit | π§ Ideal for data pipelines, data marts, and multi-layer ETL chains (Bronze β Silver β Gold). | π§ Best for procedural ETL workflows, file ingestion, or external triggers. | βοΈ Great for simple aggregates, summary tables, or report caching. |
| Cost behavior | πΈ Uses assigned warehouse only when lag exceeds threshold (efficient). | πΈ Pay per run; depends on task frequency and warehouse size. | πΈ Cost built into Snowflake compute β not warehouse-visible. |
| Maintenance overhead | πͺΆ Low β declarative setup, no orchestration once defined. | βοΈ MediumβHigh β manual SQL, scheduling, and dependency management. | πͺΆ Low β simple automatic refresh but limited flexibility. |
β‘ Advanced Use Cases
- Slowly Changing Dimensions (SCDs): Implement Type 1 and Type 2 SCDs by reading from change streams and applying window functions over record keys ordered by change timestamps. Handles insertions, deletions, and out-of-order updates.
- Joins and Aggregations: Incrementally precompute expensive joins and aggregations to enable fast query response times.
- Batch to Streaming Transitions: Seamlessly switch from batch to streaming pipelines using
ALTER DYNAMIC TABLE. Adjust refresh frequency to balance cost and freshness.
π‘ Best Practices
- Define clear layers (bronze β silver β gold) for pipeline clarity
- Start with higher
TARGET_LAG(e.g., 30 minutes) and tune based on SLA - Minimize unnecessary dependencies to reduce DAG complexity
- Leverage incremental refresh over full reloads
- Use warehouses optimized for ETL workloads separate from user queries
- Apply role-based access control for secure data sharing
π§© Summary
Dynamic Tables are smart, self-refreshing, dependency-aware materialized pipelines in Snowflake.
They remove the need for:
- Manual orchestration
- Custom refresh logic
- Scheduling management
Ideal for continuous ETL, dashboards, analytics marts, and ML pipelines with minimal operational overhead.