Skip to content

Grokem · https://www.grokem.com/data/data-platforms/data-warehouses/snowflake-dynamic-tables-vs-streams-and-tasks-when-to-use-each-pipeline-style

Data WarehousesExplainerIntermediate level

Snowflake Dynamic Tables vs Streams and Tasks: When to Use Each Pipeline Style

A dynamic table replaces a stream, a task and a MERGE with one SELECT and a target lag. This explainer shows how each approach works, where each fits, and how to choose between them.

Pankaj Nigam

Technical Writer

Published
  • 7 min read
  • 0 sec

Your progress
  1. Snowflake Dynamic Tables vs Streams and Tasks: When to Use Each Pipeline Style7 min(finished)
    0%

What you will learn

4
  • Explain what a dynamic table is and how it differs from a traditional table
  • Describe the work a stream, a task and a MERGE do together, and which part a dynamic table takes over
  • Choose between dynamic tables and streams and tasks for a given pipeline step
  • Avoid confusing dynamic tables with interactive tables and materialized views

Snowflake gives you two ways to keep a derived table up to date. You can wire the pieces yourself with a stream, a task and a MERGE. Or you can declare the result you want as one SELECT with a target lag, and let a dynamic table do the rest.

This piece explains both from first principles, then gives you a way to decide. It states no version numbers, because none apply to the concepts here. Check the official Snowflake documentation for the exact syntax and behaviour in your account.

STREAMS AND TASKSSource tableStreamTaskMERGETarget tableDYNAMIC TABLESource tableDynamic tableSELECT + target lag
The same job built with four moving parts or with one declared query.

What is a dynamic table?

A dynamic table is a table whose contents are defined by a query. Snowflake stores the result set of that query, and it keeps that result up to date for you.

You describe what the table should contain. You do not write the steps that move rows into it.

This is the key idea: the definition is declarative. The query is the single source of truth for the table's contents. Everything else, such as when and how rows are brought up to date, is handled by Snowflake.

The freshness you want is expressed as a target lag. It tells Snowflake how far behind its sources the table is allowed to be.

How is a dynamic table different from a traditional table?

A traditional table holds whatever you last put into it. Something has to write to it: an INSERT, a MERGE, a load job, or a scheduled task. If that writer stops, the table goes stale and nothing tells you.

A dynamic table has no external writer. It refreshes automatically, based on a schedule or when the underlying data changes. Snowflake handles the refresh.

That changes who owns the freshness. With a traditional table you own it, through code. With a dynamic table you state a target and Snowflake owns it.

The trade is control. You give up row-by-row decisions about how data lands in the table, and you get a much smaller pipeline to maintain.

What do streams, tasks and MERGE do?

The imperative approach splits one job into three parts that you build and connect.

  • A stream records what changed in a source table, so you can work on changes rather than the whole table.
  • A task runs your SQL on a schedule, so the work happens without anyone starting it.
  • A MERGE applies those changes to the target table, inserting, updating or deleting rows as your logic requires.

You are responsible for how they fit together. You decide the schedule, you write the MERGE, and you make sure the stream is consumed correctly. If a stream is read by something other than your task, you can lose track of changes, so you have to think about who consumes it.

This is flexible, because every step is code you can change. It is also more to build, test and monitor.

What replaces all of that in a dynamic table?

One SELECT with a target lag.

The SELECT is the transformation you would otherwise have buried inside a MERGE. The target lag replaces the task schedule. Snowflake takes over the change tracking and the refresh that the stream and task did by hand.

Compare what you maintain in each case:

Concern Streams, tasks and MERGE Dynamic table
Transformation logic Inside the MERGE In the SELECT
Change tracking A stream you manage Handled by Snowflake
Run schedule A task you configure A target lag you declare
Apply changes to target Your MERGE logic Handled by Snowflake

The exact CREATE DYNAMIC TABLE statement and its required parameters are not reproduced here. Read the official Snowflake documentation page for that statement before you write one, so you use the current syntax for your account.

Is a dynamic table the same as a materialized view?

They are similar, not identical. Both store the result set of a query, which helps query performance because readers do not recompute the result each time.

The practical difference is in how you think about them. A dynamic table lets you build a derived table from a query and keep it fresh as a pipeline step, so you can chain transformations. If you have wanted a materialized-view-like object that fits into a multi-step pipeline, a dynamic table is the closest match.

Before you rely on a dynamic table as a drop-in materialized view, check the official documentation for the limits and behaviour that apply to each. The similarity is in the idea, not in every detail.

When should you choose a dynamic table?

Choose a dynamic table when the table you need is fully described by a query over other tables.

Good fits:

  • A transformation step in a pipeline. The step reads sources and produces a result, and you only care that the result is fresh enough.
  • A pipeline you want to shrink. One SELECT is easier to read, review and hand over than a stream, a task and a MERGE.
  • A freshness goal you can state as a lag. If "within a few minutes" or "within an hour" is the real requirement, the target lag expresses it directly.
  • Teams that prefer SQL over orchestration. You write the query and skip the scheduling code.

The common thread is that the output is a function of the inputs. If you can write it as a SELECT, a dynamic table is the first thing to try.

When should you stay with streams and tasks?

Stay with streams and tasks when you need control that a single SELECT cannot express.

Good fits:

  • Custom change handling. Your MERGE applies different logic to inserts, updates and deletes, or writes to more than one target.
  • Side effects. The step does more than produce a table, for example it calls other statements as part of the same run.
  • Exact scheduling. You need the work to run at a specific time or in a specific order, rather than meeting a lag goal.
  • Existing pipelines that work. A stable stream-and-task pipeline has a real cost to replace, and a rewrite is not automatically an improvement.

The documentation excerpts for this topic do not list every limit on what a dynamic table query may contain. Check the official documentation for the restrictions on query shape before you plan a migration.

What does a dynamic table cost?

A dynamic table is not free to keep fresh. Refreshing it uses compute, and a tighter target lag means Snowflake has to refresh more often to meet it.

This is the main lever you control. Ask for the loosest lag your consumers can accept, because a lag tighter than you need is a cost you pay without benefit.

Streams and tasks have a cost too: the compute your task runs, and the time your team spends maintaining the pipeline. When you compare, count both. The honest comparison is compute plus engineering effort, not compute alone.

The official documentation describes the billing details for dynamic tables. Read it and measure on your own data, because the cost depends on the query and the data volume.

Are dynamic tables the same as interactive tables?

No. The two names are often used as if they meant the same thing, but they are different features.

A dynamic table is about keeping a derived result fresh from a query. An interactive table is a separate table type. Do not use the two terms interchangeably in design notes or tickets, because the wrong choice leads you to the wrong documentation.

If you see a search result or a colleague's message that mixes them up, confirm which feature is meant before you act. The official Snowflake documentation has a page for each.

How do you decide in practice?

Work through these questions in order for each pipeline step.

  1. Can the output be written as one SELECT over other tables? If not, use streams and tasks.
  2. Can you state the freshness you need as a lag? If you need a fixed run time instead, use a task.
  3. Do you need custom logic for how changes land? If yes, use a stream and a MERGE.
  4. Is the pipeline already working and stable? If yes, leave it unless the maintenance cost is the problem.
  5. Otherwise, try a dynamic table. Start with a loose lag, measure the cost, then tighten only if consumers need it.

You do not have to pick one style for the whole account. A dynamic table can sit next to a stream-and-task pipeline, each doing the job it suits.


Glossary

Dynamic table
A Snowflake table whose contents are defined by a query and refreshed automatically by Snowflake.
Target lag
The freshness goal you declare for a dynamic table: how far behind its sources it may be.
Stream
A Snowflake object that records changes to a source table so they can be processed.
Task
A Snowflake object that runs SQL on a schedule.
MERGE
A SQL statement that inserts, updates or deletes rows in a target table based on a source.
Interactive table
A separate Snowflake table type, not the same thing as a dynamic table.
Materialized view
A stored query result, similar in idea to a dynamic table.

Frequently asked questions

Do dynamic tables replace streams and tasks?

Not entirely. A dynamic table replaces the stream, task and MERGE combination when the target is fully described by one SELECT. Streams and tasks remain the better fit for custom change handling, side effects and fixed scheduling.

What is target lag?

It is the freshness goal you declare for a dynamic table. It replaces the schedule you would otherwise set on a task.

Is a dynamic table the same as a materialized view?

They are similar because both store a query's result set. They are not identical, so check the official documentation for the behaviour and limits of each.

Are dynamic tables and interactive tables the same?

No. They are different features that are often confused. Confirm which one is meant before you read documentation or design a pipeline.

Does a tighter target lag cost more?

A tighter lag means more frequent refreshes to meet it, and refreshing uses compute. Ask for the loosest lag your consumers can accept and measure the cost on your own data.

Next steps

  • Read the official Snowflake documentation page for dynamic tables, including the limits on query shape.
  • Read the official page for the CREATE DYNAMIC TABLE statement before you write your first one.
  • Pick one low-risk stream-and-task pipeline and sketch it as a single SELECT with a target lag to test the fit.
  • Read the official pages on streams and tasks to confirm how your current pipeline consumes changes.

From Grokem: https://www.grokem.com/data/data-platforms/data-warehouses/snowflake-dynamic-tables-vs-streams-and-tasks-when-to-use-each-pipeline-style

Finished this article?

How did this strike you?

Be the first to react – it is anonymous.

Community

Questions about this?

Ask in Questions and help and get help from other readers.

Ask a questionBrowse Questions and help

Comments

No comments yet. Share what worked for you, or ask a question.

Keep learning

Picked for this article

Related reading, discussions and learning paths on the same subject.

All in Data Warehouses

Learning paths

Learning pathIntermediate

SQL for data work

From removing duplicates and window functions to modelling for reporting, cohort analysis, indexes and query plans: the SQL a working analyst or engineer uses every week, each lesson with queries that were run.

8 lessonsabout 90 minStart

Learning pathIntermediate

Data pipelines you can trust

How data gets from source to report without surprises: the ETL and ELT choice, a first pipeline, cleaning, quality checks, safe re-runs and scheduling.

7 lessonsabout 75 minStart

Choose what we may use. You can change this at any time with “Privacy choices” at the foot of every page. Details are in the Cookie policy and the Privacy policy.

Strictly necessary

Remembers your theme, the lessons you marked as done and this choice. It stays on your device, tracks nothing and cannot be switched off.

Always on
What exactly is collected, and why

Google Analytics

Collects
The pages you view, your device and browser type, your language, how you reached the site and your approximate location (country and city, worked out from your IP address).
Why
To understand which pages and lessons are used, so we can improve them.
Stored
Two cookies, _ga and _ga_<id>, for up to 2 years. Google acts for us; the data is processed by Google, including outside your country.

Grokem’s own counts

Collects
The pages you open and how long you stay, how far you scroll, where you came from, your device type, browser, screen size, language and time zone. Your IP address is turned into a code that changes every day; it is never stored as it is.
Why
To see how long lessons are read and where readers leave. Not shared with anyone.
Stored
A random visitor ID, a visit ID and the time of your last page view in your browser’s storage (gr_vid, gr_sid, gr_last). No name or email is attached.