Everything to Know About Snowflake Dynamic Tables

By Ido Arieli Noga
December 24, 2025 | 5 min read

Managing data pipelines in Snowflake? Then you’ve definitely hit that moment when you have to choose between juggling refresh schedules, dealing with stale data, and picking between materialized views and streams with tasks. That’s where dynamic tables come into play. 

Dynamic tables were built to solve all of your problems – and they might be one of the most underused features in your team’s Snowflake environment. 

Why are dynamic tables so useful? Well, they can help you: 

  • Get performance benefits similar to materialized views, with the flexibility to support complex transformations
  • All without having to write custom orchestration code
  • No need to use external schedulers, custom refresh logic, or dependency tracking

This guide covers everything engineering managers and data architects need to know about dynamic tables, from what exactly they are, when to use them, and how to avoid gotchas that can blow up your compute costs. 

What Are Snowflake Dynamic Tables? 

Dynamic tables are a fully managed Snowflake object that materializes query results and keeps them automatically updated based on changes in source data. In other words: dynamic tables are smart, self-maintaining tables that sit between traditional views and materialized views in terms of capability and complexity. 

For example, say you define a SQL query and specify how fresh you need the data to be. Snowflake then handles everything else, from tracking changes in upstream tables, to scheduling refreshes, to updating the table incrementally when needed. That means your team is left with query performance that rivals precomputed tables without the operational overhead of managing the refresh process. 

Dynamic tables persist their data physically. That means when your dashboard queries a dynamic table with complex joins and aggregations, it’s reading pre-calculated results instead of recomputing everything on the fly. This is especially valuable for expensive transformations that run repeatedly. 

This automation goes beyond just scheduling. With dynamic tables, Snowflake can automatically: 

  • Rely on Snowflake’s internal change tracking mechanisms for supported queries
  • Determine if a query is eligible for incremental refresh based on its structure
  • Manage dependencies between dynamic tables in a pipeline 

If you have three dynamic tables that feed into each other, Snowflake ensures they refresh in the correct order and maintain data consistency across the entire chain. 

Dynamic Tables vs. Materialized Views: Understanding the Critical Differences

People often compare dynamic tables and materialized views – and for good reason. Both serve similar purposes but have fundamentally different limitations that affect which you should choose.

Here’s a breakdown of how the two compare: 

FeatureDynamic TablesMaterialized Views
Query complexity Full SQL support: multi-table joins, unions, CTEs, nested transformationsSingle-table operations only; limited aggregations, filters, and column transformations
JoinsFully supported across multiple tablesNot supported; immediate deal-breaker for most use cases
Window functionsSupported (ROW_NUMBER, RANK, LAG, etc)Not supported
Refresh frequencyScheduled based on target lag you define (minimum 60 seconds)Near real-time automatic updates as base tables change
Refresh controlMust query the dynamic table directlyAlways up-to-date, no control over refresh timing
Query rewriteMust query the dynamic table directlyOptimizer may automatically rewrite base table queries to use the materialized view
Incremental refreshSupported for eligible queries, significantly reduces compute costsAlways incremental 
Cost predictabilityHigh – you control refresh frequency and warehouse sizeLower – continuous updates can accumulate unexpected costs
Best for Complex transformations, data pipelines, multi-source enrichmentSimple single-table aggregations requiring real-time freshness
Typical use casesCustomer 360 views, sales reporting pipelines, slowly changing dimensionsSimple metric rollups, single-table summaries

The biggest difference between dynamic tables and materialized views is query complexity. Materialized views can only handle single-table operations. If you need a join, window function or union, you’re better off with dynamic tables that support the full range of SQL operations. 

How to Create Snowflake Dynamic Tables

You’ll need three things to create a dynamic table: 

  1. The query that defines your transformation
  2. The target lag that controls refresh frequency 
  3. The warehouse that provides compute resources

Here’s a basic example that aggregates sales data: 

CREATE OR REPLACE DYNAMIC TABLE sales_summary
  TARGET_LAG = '30 minutes'
  WAREHOUSE = analytics_wh
  AS
    SELECT
      product_id,
      DATE_TRUNC('day', order_date) as sale_date,
      SUM(amount) as total_sales,
      COUNT(*) as order_count
    FROM raw_sales
    GROUP BY product_id, DATE_TRUNC('day', order_date);

Let’s take a closer look at different factors that affect how you build out your dynamic tables. 

Target Lag: The Key to Balancing Freshness and Cost

Target lag is the maximum time your dynamic table can lag behind the source tables. Setting TARGET_LAG = ’30 minutes’ tells Snowflake to keep the data no more than 30 minutes out of date. This won’t guarantee refresh timing. Snowflake will attempt to meet the target, but the actual freshness may vary depending on query complexity, data volume, and warehouse size. 

The minimum target lag is 60 seconds. If you need real-time updates more frequently than once per minute, you’ll have to use streams and tasks instead. Most analytic use cases set a target lag between 5 minutes and 1 hour, which walks the line between data freshness and compute efficiency. 

Pro tip: There’s a special DOWNSTREAM option for target lag. Setting TARGET_LAG = DOWNSTREAM tells Snowflake to only refresh this dynamic table when downstream dependent dynamic tables require fresh data.  

Refresh Mode: Choosing Between Incremental and Full

We haven’t touched on this yet, but the REFRESH_MODE parameter controls how Snowflake refreshes your dynamic table. It has massive implications for compute costs. You have three options here: 

  1. INCREMENTAL: This is the most commonly desired refresh mode. Snowflake tracks which rows are changed in the source tables and only processes those changes, merging them into the dynamic table. 
  2. FULL: Recomputes the entire table from scratch on every refresh. Use this when incremental refresh isn’t supported or when the volume of changes approaches the total table size. 
  3. AUTO: This allows Snowflake to decide between incremental and full refresh based on the query structure and cost estimates. It attempts to be incremental by default and falls back to full when needed. This sounds ideal, but Snowflake notes that AUTO behavior may change between releases, causing performance changes in production pipelines. 

Here’s an example with an explicit refresh mode: 

CREATE OR REPLACE DYNAMIC TABLE customer_metrics
  TARGET_LAG = '1 hour'
  WAREHOUSE = analytics_wh
  REFRESH_MODE = INCREMENTAL
  AS
    SELECT
      customer_id,
      COUNT(DISTINCT order_id) as lifetime_orders,
      SUM(order_total) as lifetime_value,
      MAX(order_date) as last_order_date
    FROM orders
    GROUP BY customer_id;

NOTE: Incremental refresh mode does come with a catch: not all queries are supported. Queries with non-deterministic functions like RANDOM() or CURRENT_TIMESTAMP() can’t be incrementally refreshed because their results change even when source data doesn’t. 

Initialization: When Your Table Gets Its First Data

The INITIALIZE parameter controls when the initial data load takes place. You have two options here: 

  1. ON_CREATE: This is the default, which causes Snowflake to populate the table synchronously when you run the CREATE statement. This command won’t return until the initial refresh completes, and if the refresh fails, table creation fails. 
  2. ON_SCHEDULE: This creates the table empty and populates it on the first scheduled refresh. Queries against the table will fail with an error about the table not being initialized until the first refresh runs. 

ON_CREATE is the safer option for most use cases because you’ll know immediately if something is wrong with your query or if dependencies are missing. ON_SCHEDULE on the other hand makes sense for very large tables where the initial load would take hours and you don’t want to wait for table creation to finish. 

Monitoring Dynamic Tables: Knowing What’s Actually Happening

You know now that creating dynamic tables is easy enough. The tricky party is knowing if they’re working correctly – and efficiently. Snowflake has a few monitoring options you can use: 

  1. Snowsight
  2. Using information schema
  3. Setting up alerts

Let’s take a closer look at each of these to see how exactly you can use them. 

Monitoring in Snowsight

This is your quick health check option. The Snowsight UI gives you the fastest way to check dynamic table health. For a specific table, you can see: 

  • Table details: This shows the scheduling state, last refresh status, and if incremental or full refreshes are being used. 
  • Refresh history: This displays when each refresh ran, how long it took, and if it succeeded or failed. 
  • Graph view: This shows the dependency relationships between this table and upstream/downstream tables. 
  • Data preview: This helps you preview the first 100 rows to verify the output looks correct. 

Using Information Schema for Deeper Analysis

For programmatic monitoring or deeper analysis, query the INFORMATION_SCHEMA table functions. 

The most useful for dynamic tables are: 

DYNAMIC_TABLE_REFRESH_HISTORY, which returns details about every refresh that’s run in the last seven days. 

SELECT
  name,
  refresh_end_time,
  refresh_action,
  state,
  data_lag_seconds,
  query_id
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(
  table_name => 'SALES_SUMMARY'
))
ORDER BY refresh_end_time DESC;

DYNAMIC_TABLES provides metadata about each dynamic table including target lag, scheduling state, and current data freshness. 

SELECT
  name,
  target_lag,
  data_lag_seconds,
  scheduling_state,
  refresh_mode,
  warehouse_name
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLES())
WHERE database_name = 'ANALYTICS_DB';

Pro tip: The data_lag_seconds column shows how far behind your data currently is. If this consistently exceeds your target lag, you need a larger warehouse or need to optimize your query. 

Setting Up Alerts for Failed Refreshes

Monitoring dashboards are great for anyone who has the time to dig into the details, but your team needs proactive alerts when refreshes fail. Snowflake’s event tables combined with alerts mean you can address failures within minutes. 

To set this up, you’ll first want to enable event logging at the database or object level: 

-- Enable ERROR-level logging for all dynamic tables in the database
ALTER DATABASE analytics_db SET LOG_LEVEL = 'ERROR';

-- Or enable for a specific table
ALTER DYNAMIC TABLE sales_summary SET LOG_LEVEL = 'ERROR';

Then create an alert that triggers when refresh failures are logged: 

CREATE OR REPLACE ALERT dynamic_table_failure_alert
  WAREHOUSE = alerts_wh
  SCHEDULE = '5 minute'
  IF (EXISTS (
    SELECT 1
    FROM SNOWFLAKE.TELEMETRY.EVENTS
    WHERE resource_attributes:"snow.executable.type" = 'DYNAMIC_TABLE'
      AND resource_attributes:"snow.database.name" = 'ANALYTICS_DB'
      AND value:state = 'FAILED'
      AND timestamp >= DATEADD('minute', -5, CURRENT_TIMESTAMP())
  ))
  THEN CALL SYSTEM$SEND_EMAIL(
    '[email protected]',
    'Dynamic Table Refresh Failed',
    'Check Snowsight monitoring for details'
  );

This alert checks every five minutes for failed refreshes in the last five minutes and sends an email when those failures occur. 

How to Update Dynamic Tables Safely 

Updating dynamic tables is a careful practice of walking the line between which changes will and won’t trigger a full data reload. We’ll break down what changes preserve data and which trigger reinitialization below. 

Changes That Preserve Data

These alterations can be made without losing data or triggering a reinitialization: 

  • Changing target lag
  • Switching warehouses
  • Adding or modifying cluster keys

These are changes that take place immediately and don’t require rebuilding the table. However, changing target lag to a more aggressive setting will increase refresh frequency – and compute costs. 

Changes That Trigger Reinitialization 

Some modifications require Snowflake to reinitialize the table, which means recomputing all data from scratch. 

These changes include: 

  • Changing the query definition 
  • Changing refresh mode (you can’t alter refresh mode after creation, so to change from INCREMENTAL to FULL, you must drop and recreate the table)

Snowflake Dynamic Tables Best Practices

Running dynamic tables efficiently means your team has to understand not just how they work, but how to structure them to avoid performance and cost pitfalls. 

Break Complex Pipelines into Layers

One large dynamic table with a 500-line query is harder to debug and optimize than three connected tables with simpler logic. Try to break complex transformations into stages: 

- Stage 1: Clean and standardize raw data
CREATE DYNAMIC TABLE cleaned_orders
  TARGET_LAG = '30 minutes'
  WAREHOUSE = etl_wh
  REFRESH_MODE = INCREMENTAL
  AS
    SELECT
      order_id,
      customer_id,
      UPPER(TRIM(status)) as status,
      order_date,
      amount
    FROM raw_orders
    WHERE order_date >= DATEADD('day', -90, CURRENT_DATE());

-- Stage 2: Join with dimension data
CREATE DYNAMIC TABLE enriched_orders
  TARGET_LAG = '30 minutes'
  WAREHOUSE = etl_wh
  REFRESH_MODE = INCREMENTAL
  AS
    SELECT
      o.order_id,
      o.order_date,
      o.amount,
      c.customer_name,
      c.customer_tier
    FROM cleaned_orders o
    JOIN customers c ON o.customer_id = c.customer_id;

-- Stage 3: Aggregate for reporting
CREATE DYNAMIC TABLE daily_sales_metrics
  TARGET_LAG = '30 minutes'
  WAREHOUSE = etl_wh
  REFRESH_MODE = INCREMENTAL
  AS
    SELECT
      DATE_TRUNC('day', order_date) as sale_date,
      customer_tier,
      COUNT(*) as order_count,
      SUM(amount) as total_revenue
    FROM enriched_orders
    GROUP BY DATE_TRUNC('day', order_date), customer_tier;

This more layered approach provides several benefits: 

  • Each stage is simpler to understand and modify
  • If one stage fails, you can pinpoint the issue quickly 
  • You can reuse intermediate layers for multiple downstream tables
  • Target lag can be tuned independently at each stage

Choose Target Lag Based on Business Requirements, Not Technology

Many are tempted to set an aggressive target lag (one minute, five minutes) because you can. Resist this (unless your business requires that level of freshness). Every reduction in target lag increases refresh frequency and compute costs. 

Think about what your organization actually needs: 

  • Executive dashboards refreshed once overnight? Set target lag to 12-24 hours. 
  • Operational metrics for customer support teams? 15-30 minutes is usually more than enough. 
  • Real-time fraud detection? You might need 1-2 minutes, but think about if streams and tasks or even real-time Snowpipe would be better. 

Remember that target lag propagates through pipelines. If your final table needs hourly updates, all upstream tables can also use hourly target lag, cutting back on overall compute spend. 

Size Warehouses Appropriately for Refresh Patterns

Running all dynamic table refreshes on a single X-Large warehouse wastes money on simple aggregations and throttles complex transformations. Try to segment workloads by complexity:

-- Simple cleaning and filtering: Small warehouse
CREATE DYNAMIC TABLE cleaned_events
  TARGET_LAG = '30 minutes'
  WAREHOUSE = small_etl_wh
  AS SELECT ...;

-- Complex multi-table joins: Large warehouse
CREATE DYNAMIC TABLE enriched_customer_360
  TARGET_LAG = '1 hour'
  WAREHOUSE = large_etl_wh
  AS SELECT ...;

Pro tip: Try to monitor actual refresh times and warehouse usage. If a refresh consistently completes in 30 seconds on a Medium warehouse with 20% utilization, drop to a Small. 

Stop Managing Refreshes Manually 

Dynamic tables eliminate a lot of operational complexity, but your team still has to monitor target lag, troubleshooting failed refreshes, and optimizing warehouse sizes to keep costs under control. For teams running dozens – or hundreds – of dynamic tables across multiple environments, that operational burden adds up fast. 

This is exactly what Yuki has been built to solve, helping your team to: 

  • Identify tables that are consistently falling behind their target lag and recommended warehouse sizing adjustments
  • Catch dynamic tables that should be using incremental refreshes but are running full refreshes due to query structure issues
  • Spot patterns where target lag settings are more aggressive than business requirements actually need, helping you dial back refresh frequency to reduce compute spend. 

The result? Instead of your team having to manually tune warehouses or guess how refresh workload should be routed, teams can use automated approaches like Yuki’s dynamic routing to keep Snowflake performance predictable as usage scales. Yuki can automatically optimize your Snowflake environment for query and warehouse usage, right-sizing compute resources and providing proactive cost governance so your team can focus on building transformations that drive business value. 
Request your free demo to see how Yuki reduces manual effort and turns Snowflake costs from an uncalculatable nightmare to a predictable cost.

By Ido Arieli Noga
Ido Arieli Noga is the CEO and Co-Founder of Yuki, where he helps businesses cut Snowflake spend through smart warehouse scaling and DevOps-driven optimization. He brings over 12 years of experience across data storage, BI, and FinOps, including nearly four years as Head of Data at Lightico and five years managing large-scale virtual environments in the government sector. Ido holds a degree in Computer Science and is passionate about building scalable, cost-efficient data infrastructures. Since founding Yuki in 2023, he’s focused on helping teams reduce costs without changing queries or code. Find more of his insights on Medium or LinkedIn.

Table of Contents

Free cost analysis

Take 5 minutes to learn how much money you can save on your Snowflake account.

By clicking Submit you’re confirming that you agree with our Terms and Conditions.

Follow us on LinkedIn

Related posts

Free cost analysis

Take 5 minutes to learn how much money you can save on your Snowflake account.

By clicking Submit you’re confirming that you agree with our Terms and Conditions.

Skip to content