Dynamic Tables

Snowflake · Advanced Features

Theory

A dynamic table is a table defined by a query, which Snowflake keeps up to date for you. You write CREATE DYNAMIC TABLE clean_orders TARGET_LAG = '1 hour' WAREHOUSE = analytics_wh AS SELECT ..., and Snowflake schedules refreshes so the data is never more than about the target lag behind its sources. Where the query allows it, refreshes are incremental, processing only changed source rows; otherwise Snowflake does a full refresh.

Dynamic tables can read from other dynamic tables, forming a pipeline. A downstream table can use TARGET_LAG = DOWNSTREAM to refresh only when the tables that depend on it need fresh data. You cannot run INSERT or UPDATE on a dynamic table; its contents always come from its query.

Why we use it

Hand-building streams, tasks and MERGE statements for each transformation is a lot of code to maintain. Dynamic tables let you declare the result you want and how fresh it must be, and Snowflake handles the scheduling and incremental logic.

When to use it

Use dynamic tables for transformation pipelines where freshness of minutes or hours is fine and the logic is a SELECT. Keep streams and tasks for procedural logic, calls to external systems, or precise control over when and how changes are applied.

How Refresh Works

You set a TARGET_LAG (e.g. '5 minutes') and Snowflake decides how and when to refresh the table to stay within that lag, incrementally where possible instead of always recomputing from scratch.

Dynamic Tables vs Streams + Tasks

The same incremental pipeline that used to require a Stream, a Task, and a MERGE statement can often be replaced by a single CREATE DYNAMIC TABLE statement with much less code to maintain.

When to Still Use Streams + Tasks

Dynamic tables cover common transform-and-refresh patterns, but a Stream + Task pipeline still gives more fine-grained control when the logic is genuinely complex or conditional.

How it works

  1. Define the dynamic table query and TARGET_LAG
  2. Snowflake tracks changes in the source tables
  3. Would the data fall behind the target lag? Yes: Schedule a refresh on the warehouse. No: Wait.
  4. Can this query refresh incrementally? Yes: Process only the changed rows. No: Recompute the full result.
  5. Downstream dynamic tables refresh in order

Example

CREATE OR REPLACE DYNAMIC TABLE clean_orders
  TARGET_LAG = '1 hour' WAREHOUSE = analytics_wh
AS SELECT order_id, UPPER(TRIM(product)) AS product, amount, order_date
   FROM orders WHERE amount > 0;

CREATE OR REPLACE DYNAMIC TABLE daily_metrics
  TARGET_LAG = '2 hours' WAREHOUSE = analytics_wh
AS SELECT order_date, COUNT(*) AS order_count, SUM(amount) AS revenue
   FROM clean_orders GROUP BY order_date;

SELECT * FROM daily_metrics ORDER BY order_date DESC;

Key points

Snowflake topics

Snowflake Fundamentals

Warehouses & Performance

Staging & SnowSQL

Tables & Data Handling

Cloud Integrations

Storage & Optimization

Security & Governance

Views, Pipelines & Automation

Data Types & SQL Fundamentals

Semi-Structured Data Deep Dive

Advanced Features

Data Sharing & Collaboration

Cost Management & Monitoring

Databases, Schemas & Objects

Performance Deep Dive

Advanced Security & Governance

Stored Procedures & UDFs

Ecosystem & Integrations

SnowPro Certification Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Mastering Snowflake

The modern data cloud, from architecture to SnowPro certification. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice Snowflake SQL Sharpen the SQL you use in Snowflake: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Snowflake  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Unlike the SQL and Python courses, Snowflake needs a real account — there's no in-browser engine for it. Click Copy above, then paste it into a free Snowflake trial (30 days, no card required) and run it in Snowsight.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic