Dynamic Tables: Declarative Data Pipelines

Building data pipelines has always been a chore: write complex scripts, schedule cron jobs, handle failures, and manage incremental updates. Snowflake Dynamic Tables change the game by letting you declare what you want, not how to get it. With a simple SQL statement, you define a transformation, and Snowflake automatically keeps the result up-to-date as source data changes. This declarative approach reduces maintenance overhead, eliminates manual scheduling, and enables real-time analytics without the complexity of streaming frameworks. In this tutorial, you'll learn how to create, configure, and optimize Dynamic Tables for production workloads, with practical examples and debugging tips.

What Are Snowflake Dynamic Tables?

Dynamic Tables are a Snowflake feature that automatically refreshes the results of a SQL query as source data changes. They replace traditional ETL pipelines with a declarative model: you write a SELECT statement, and Snowflake handles incremental refreshes, scheduling, and failure recovery. Dynamic Tables support both batch and streaming sources, making them ideal for real-time analytics, data marts, and transformation layers. They are defined with a TARGET_LAG parameter that controls how stale the data can be, balancing freshness and cost.

CREATE DYNAMIC TABLE sales_summary
  TARGET_LAG = '5 minutes'
  WAREHOUSE = my_wh
AS
SELECT
  DATE_TRUNC('hour', order_time) AS hour,
  product_id,
  SUM(quantity) AS total_qty,
  SUM(amount) AS total_revenue
FROM orders
GROUP BY hour, product_id;

Creating Your First Dynamic Table

Creating a Dynamic Table is as simple as writing a CREATE DYNAMIC TABLE statement. You specify a target lag (how fresh the data should be) and a warehouse for compute. The query can include joins, aggregations, subqueries, and even CTEs. Dynamic Tables support incremental refresh automatically: Snowflake tracks changes in source tables and applies only the delta. This is far more efficient than full refreshes. Let's create a Dynamic Table that aggregates daily sales by product category.

CREATE DYNAMIC TABLE daily_category_sales
  TARGET_LAG = '1 hour'
  WAREHOUSE = analytics_wh
AS
SELECT
  o.order_date,
  p.category,
  SUM(o.quantity) AS units_sold,
  SUM(o.amount) AS revenue
FROM orders o
JOIN products p ON o.product_id = p.product_id
GROUP BY o.order_date, p.category;

Understanding Target Lag and Refresh Behavior

The TARGET_LAG parameter defines the maximum acceptable staleness of the Dynamic Table. Snowflake automatically schedules refreshes to meet this target. You can set it to a duration like '5 minutes' or '1 hour'. Shorter lags mean more frequent refreshes, which can increase cost. Snowflake uses a cost-based optimizer to decide whether to do incremental or full refresh. You can also manually trigger a refresh with ALTER DYNAMIC TABLE ... REFRESH. Monitoring refresh history helps you tune performance.

-- Check refresh history for a Dynamic Table
SELECT *
FROM INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY
WHERE TABLE_NAME = 'DAILY_CATEGORY_SALES'
ORDER BY REFRESH_START_TIME DESC
LIMIT 10;

Incremental Refresh and Change Tracking

Dynamic Tables leverage Snowflake's change tracking to perform incremental refreshes. When source tables are modified (INSERT, UPDATE, DELETE), Snowflake captures the changes and applies them to the Dynamic Table. This avoids full table scans and reduces compute costs. However, not all queries are incrementally refreshable. Snowflake supports incremental refresh for SELECT, JOIN, aggregation, and window functions under certain conditions. If a query is not incrementally refreshable, Snowflake falls back to full refresh. You can check the refresh type in the history.

SELECT TABLE_NAME, REFRESH_TYPE, STATUS
FROM INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY
WHERE TABLE_NAME = 'DAILY_CATEGORY_SALES'
ORDER BY REFRESH_START_TIME DESC
LIMIT 5;

Managing Dynamic Tables: Alter, Drop, and Clone

Dynamic Tables can be altered to change TARGET_LAG, warehouse, or even the query (with limitations). You can also clone a Dynamic Table for testing or backup. Dropping a Dynamic Table removes both the definition and the stored data. Use ALTER DYNAMIC TABLE to suspend or resume automatic refresh. Cloning is useful for creating development copies without affecting production.

-- Change target lag
ALTER DYNAMIC TABLE daily_category_sales SET TARGET_LAG = '30 minutes';

-- Suspend automatic refresh
ALTER DYNAMIC TABLE daily_category_sales SUSPEND;

-- Resume automatic refresh
ALTER DYNAMIC TABLE daily_category_sales RESUME;

-- Clone a Dynamic Table
CREATE DYNAMIC TABLE daily_category_sales_dev CLONE daily_category_sales;

Dynamic Tables vs. Materialized Views vs. Streams + Tasks

Dynamic Tables are often compared to materialized views and the traditional Streams + Tasks pattern. Materialized views are limited to single-table aggregations and don't support joins or complex transformations. Streams + Tasks give you full control but require manual coding for incremental logic. Dynamic Tables offer a middle ground: declarative SQL with automatic incremental refresh. They are easier to set up than Streams + Tasks and more powerful than materialized views. However, for very complex pipelines or custom error handling, Streams + Tasks may still be necessary.

-- Materialized View (single table, no joins)
CREATE MATERIALIZED VIEW daily_sales_mv AS
SELECT DATE_TRUNC('day', order_time) AS day, SUM(amount) AS total
FROM orders
GROUP BY day;

-- Dynamic Table (supports joins)
CREATE DYNAMIC TABLE daily_sales_dt
  TARGET_LAG = '1 hour'
  WAREHOUSE = my_wh
AS
SELECT o.order_date, c.region, SUM(o.amount) AS total
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY o.order_date, c.region;

Best Practices for Production Dynamic Tables

1. Start with a reasonable TARGET_LAG (e.g., 5-15 minutes) and adjust based on monitoring. 2. Use a dedicated warehouse to avoid contention. 3. Cluster Dynamic Tables on filter columns to speed up refreshes. 4. Monitor refresh history and cost using Snowflake's views. 5. Avoid non-deterministic functions in queries to enable incremental refresh. 6. Test with a clone before modifying production. 7. Set up alerts for failed refreshes using Snowflake's event notifications. 8. Consider using Dynamic Tables for data marts to reduce load on source systems.

-- Add clustering to a Dynamic Table
ALTER DYNAMIC TABLE daily_category_sales CLUSTER BY (order_date);