Dynamic tables compared to streams and tasks and to materialized views¶

Like streams and tasks, dynamic tables provide a way to transform data in your pipeline.

Dynamic tables compared to streams and tasks¶

Although dynamic tables serve a similar purpose compared to tasks, there are important differences.

Comparison between streams/tasks and dynamic tables

Streams and Tasks	Dynamic Tables
Tasks use an imperative approach: You write procedural code to transform data from base tables.	Dynamic tables use a declarative approach: You write a query that specifies the result you want, and data is retrieved and transformed from the base tables used in the query.
You define a schedule for executing the code that transforms the data.	An automated refresh process determines the schedule for performing refreshes. The process schedules these refreshes to meet the specified target level of freshness (lag).
The procedural code can contain calls to non-deterministic code, stored procedures, and other tasks. The procedural code can contain calls to UDFs and external functions.	Although the SELECT statement for a dynamic table can contain joins, aggregations, window functions, and other SQL functions and constructions, the statement cannot contain calls to stored procedures and tasks. Currently, the SELECT statement also cannot contain calls to external functions. This limitation is due to the way in which dynamic tables are refreshed. To refresh the data, an automated process analyzes the SELECT statement for the dynamic table in order to determine the best approach to refresh the data. The automated process cannot determine this for certain types of queries. For the complete list of restrictions on the SELECT statement, see Types of queries that support incremental refreshes and Query constructs not currently supported in dynamic tables.
Tasks can use streams to refresh data in target tables incrementally. You can schedule these tasks to run on a regular basis.	An automated refresh process performs incremental refreshes of dynamic tables on a regular basis. The process determines the schedule based on the target “freshness” of the data that you specify.

Dynamic tables compared to materialized views¶

Dynamic tables have some similarities to materialized views in that both materialize the results of a query. However, there are important differences:

Materialized Views	Dynamic Tables
Materialized views are designed to improve query performance transparently. For example, if you query the base table, the query optimizer in Snowflake can rewrite the query automatically to query the materialized view instead.	Dynamic tables are designed to build multi-level data pipelines. Although dynamic tables can improve query performance, the query optimizer in Snowflake does not automatically rewrite queries to use dynamic tables. A dynamic table is used in a query only if you specify the dynamic table in the query.
A materialized view can only use a single base table. A materialized view cannot be based on a complex query (that is, a query with joins or nested views).	A dynamic table can be based on a complex query, including one with joins and unions.
Data accessed through materialized views is always current. If a DML operation changes the data in the base table, Snowflake either updates the materialized view or uses the updated data from the base table.	The data is current up to the target lag time for the dynamic table. Materialized view maintenance is automatically managed by a separate compute service, including refresh logic, along with the compute for any updates, typically at additional cost.

Materialized Views

Dynamic Tables

Materialized views are designed to improve query performance transparently.

For example, if you query the base table, the query optimizer in Snowflake can rewrite the query automatically to query the materialized view instead.

Dynamic tables are designed to build multi-level data pipelines.

Although dynamic tables can improve query performance, the query optimizer in Snowflake does not automatically rewrite queries to use dynamic tables. A dynamic table is used in a query only if you specify the dynamic table in the query.

A materialized view can only use a single base table. A materialized view cannot be based on a complex query (that is, a query with joins or nested views).

A dynamic table can be based on a complex query, including one with joins and unions.

Data accessed through materialized views is always current. If a DML operation changes the data in the base table, Snowflake either updates the materialized view or uses the updated data from the base table.

The data is current up to the target lag time for the dynamic table.

Materialized view maintenance is automatically managed by a separate compute service, including refresh logic, along with the compute for any updates, typically at additional cost.