Skip to main content

❄️ 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​

ConceptDescription
Incremental RefreshAutomatically processes only new or changed data instead of reloading the full dataset, optimizing performance and compute cost.
Data DependenciesAutomatically tracks and maintains refresh order across dependent tables (DAG), ensuring consistent data snapshots in multi-hop pipelines.
OrchestrationAutomatically 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
FeatureDescription
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 AwarenessTables are refreshed in the correct order based on dependencies.
Complex Query SupportSupports joins, aggregations, window functions, and CTEs β€” much more powerful than MVs.
Incremental ComputationOnly changed rows are processed, reducing compute cost.
Warehouse-Aware ExecutionSpecify the warehouse for table refresh computation.
DAG-Based OrchestrationAutomatically 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
FeatureDynamic Table (DT)Streams + TasksMaterialized View (MV)
Auto-refresh
  • βœ… Fully automated refresh managed by Snowflake based on TARGET_LAG (freshness SLA).

❌ No built-in auto-refresh; requires explicit task scheduling or chaining.

  • βœ… Automatically refreshes when base tables change, but limited to simple dependencies.
Complex SQL (joins, aggregates)
  • βœ… Supports complex joins, aggregations, unions, and transformations across multiple schemas/databases.
βœ… Fully flexible β€” any SQL logic supported.

❌ Restricted to a single base table and limited expressions.

Handles dependencies
  • βœ… Automatically tracks lineage and refreshes in dependency order (even across schemas).

❌ 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 (TARGET_LAG).

βœ… Must manually define schedules or AFTER dependencies.

❌ Handled internally, but refresh timing cannot be customized.

Incremental computation
  • βœ… Automatic incremental refresh β€” only changed data recomputed.
  • βœ… Possible via Streams & MERGE patterns, defined manually.
  • βœ… Maintains incrementally but only for simple projections/filters.
Warehouse control
  • βœ… You define which warehouse is used for refresh (WAREHOUSE =).
βœ… Full control β€” each task runs on a specific warehouse.

❌ Uses Snowflake-managed compute β€” no visibility or control over cost.

Monitoring / lineage visibility
  • βœ… Built-in lineage view and ACCOUNT_USAGE.DYNAMIC_TABLE_REFRESH_HISTORY.
  • βœ… Check task history via INFORMATION_SCHEMA.TASK_HISTORY.
⚠️ 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
  1. Define clear layers (bronze β†’ silver β†’ gold) for pipeline clarity
  2. Start with higher TARGET_LAG (e.g., 30 minutes) and tune based on SLA
  3. Minimize unnecessary dependencies to reduce DAG complexity
  4. Leverage incremental refresh over full reloads
  5. Use warehouses optimized for ETL workloads separate from user queries
  6. 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.