Dynamic Tables are one of Snowflake’s most exciting features for modern data engineering. They offer a declarative way to keep data automatically refreshed, eliminating the need for manual scheduling or orchestration.
What Are Dynamic Tables?
A Dynamic Table is a virtual table in Snowflake that automatically refreshes its data based on a defined SQL query and a target lag—the maximum acceptable delay between source data changes and the table’s refresh.
Think of it as a materialized view with built-in freshness guarantees.
CREATE OR REPLACE DYNAMIC TABLE sales_summary
TARGET_LAG = '5 minutes'
AS
SELECT region, SUM(sales) AS total_sales
FROM raw_sales
GROUP BY region;
This table will stay up-to-date within 5 minutes of changes to raw_sales.
Why Use Dynamic Tables?
Dynamic Tables are ideal when you want:
- Automated freshness without scheduling/orchestration tools
- Declarative logic for derived tables
- Real-time analytics with predictable latency
They’re especially useful for:
- Dashboards and BI tools
- Intermediate transformation layers
- Streaming or micro-batch use cases
Pros and Cons of Dynamic Tables
✅ Pros | ⚠️ Cons |
|---|---|
Auto-refresh based on | Limited control over exact refresh timing |
Declarative syntax simplifies pipeline logic | Still in preview—may lack full feature parity |
No external orchestration needed | May not suit complex dependency chains |
Optimized for freshness | Can incur compute costs if refreshes are frequent |
Integrated with Snowflake’s compute model | Debugging refresh issues can be opaque |
Best Practices
- Choose a sensible TARGET_LAG: Balance freshness with cost.
- Avoid heavy transformations: Keep queries efficient to reduce refresh latency.
- Monitor usage: Use
SNOWFLAKE.ACCOUNT_USAGE.DYNAMIC_TABLE_REFRESH_HISTORYto track performance. - Use for intermediate layers: Great for staging or aggregation before final reporting.