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:
| Feature | Dynamic Tables | Materialized Views |
| Query complexity | Full SQL support: multi-table joins, unions, CTEs, nested transformations | Single-table operations only; limited aggregations, filters, and column transformations |
| Joins | Fully supported across multiple tables | Not supported; immediate deal-breaker for most use cases |
| Window functions | Supported (ROW_NUMBER, RANK, LAG, etc) | Not supported |
| Refresh frequency | Scheduled based on target lag you define (minimum 60 seconds) | Near real-time automatic updates as base tables change |
| Refresh control | Must query the dynamic table directly | Always up-to-date, no control over refresh timing |
| Query rewrite | Must query the dynamic table directly | Optimizer may automatically rewrite base table queries to use the materialized view |
| Incremental refresh | Supported for eligible queries, significantly reduces compute costs | Always incremental |
| Cost predictability | High – you control refresh frequency and warehouse size | Lower – continuous updates can accumulate unexpected costs |
| Best for | Complex transformations, data pipelines, multi-source enrichment | Simple single-table aggregations requiring real-time freshness |
| Typical use cases | Customer 360 views, sales reporting pipelines, slowly changing dimensions | Simple 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:
- The query that defines your transformation
- The target lag that controls refresh frequency
- 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:
- 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.
- 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.
- 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:
- 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.
- 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:
- Snowsight
- Using information schema
- 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.


