Choose between dynamic tables, streams and tasks, and materialized views¶

Like streams and tasks, dynamic tables provide a way to transform data in your pipeline. This topic compares these approaches to help you choose the right one.

Dynamic tables compared to streams and tasks¶

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

Comparison between streams and tasks and dynamic tables¶

Example: Comparison of data transformation between streams and tasks and dynamic tables¶

The example in Transform loaded JSON data on a schedule uses streams and tasks to transform and insert new data into a target table (names) as the data is streamed into a landing table (raw).

The following examples demonstrate how to perform the same transformation using dynamic tables. When creating a dynamic table, you specify the query for the results that you want to see. For the incremental refresh of the data, you don’t need to create a stream to track changes and write a task to examine those changes and apply the changes to the target table. The automated refresh process does this for you based on the query that you specify.

-- Create a landing table to store
-- raw JSON data.
CREATE OR REPLACE TABLE raw
  (var VARIANT);

-- Create a stream to capture inserts
-- to the landing table.
CREATE OR REPLACE STREAM rawstream1
  ON TABLE raw;

-- Create a table that stores the
-- names of office visitors from the
-- raw data.
CREATE OR REPLACE TABLE names
  (id INT,
   first_name STRING,
   last_name STRING);

-- Create a task that inserts new name
-- records from the rawstream1 stream
-- into the names table.
-- Execute the task every minute when
-- the stream contains records.
CREATE OR REPLACE TASK raw_to_names
  WAREHOUSE = mywh
  SCHEDULE = '1 minute'
  WHEN
    SYSTEM$STREAM_HAS_DATA('rawstream1')
  AS
    MERGE INTO names n
      USING (
        SELECT var:id id, var:fname fname, var:lname lname,
                metadata$action, metadata$isupdate
                  FROM rawstream1
      ) r1 ON n.id = TO_NUMBER(r1.id)
      WHEN MATCHED AND metadata$action = 'DELETE'
            AND NOT metadata$isupdate THEN
          DELETE
      WHEN MATCHED AND metadata$action = 'INSERT' THEN
        UPDATE SET n.first_name = r1.fname, n.last_name = r1.lname
      WHEN NOT MATCHED AND metadata$action = 'INSERT' THEN
        INSERT (id, first_name, last_name)
          VALUES (r1.id, r1.fname, r1.lname);

-- Start the task
ALTER TASK raw_to_names RESUME;

-- Create a landing table to store
-- raw JSON data.
CREATE OR REPLACE TABLE raw
  (var VARIANT);

-- Create a dynamic table containing the
-- names of office visitors from
-- the raw data.
-- Try to keep the data up to date within
-- 1 minute of real time.
CREATE OR REPLACE DYNAMIC TABLE names
  TARGET_LAG = '1 minute'
  WAREHOUSE = mywh
  AS
    SELECT var:id::int id, var:fname::string first_name,
    var:lname::string last_name FROM raw;

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:

When not to use dynamic tables¶

This topic describes general and cross-feature limitations on dynamic tables.

General limitations¶

The following general limitations apply to using dynamic tables:

• A single account can hold a maximum of 50,000 dynamic tables.
• You can’t truncate data from a dynamic table.
• You can’t create a temporary dynamic table.
• When you use a dynamic table to ingest shared data, the query can’t select from a shared dynamic table or a shared secure view that references an upstream dynamic table.
• You can’t use secondary roles with dynamic tables because dynamic table refreshes act as their owner role. For more information, see Authorization through primary role and secondary roles.
• You can’t use dynamic SQL (for example, session variables or unbound variables of anonymous blocks) in the dynamic table’s definition.
• In a dynamic table definition, SELECT blocks that read from user-defined table functions (UDTF) must explicitly specify columns and can’t use *.
• Dynamic tables can become stale if they are not refreshed within the MAX_DATA_EXTENSION_TIME_IN_DAYS period of the input tables. Once stale, they must be recreated to resume refreshes.
• When creating a dynamic table that uses a warehouse named DEFAULT, you must use double quotes around the name, following the double-quoted identifier requirements. For example, CREATE DYNAMIC TABLE ... WAREHOUSE = "DEFAULT". For more information on creating dynamic tables, see Create dynamic tables.
• Dynamic tables don’t support sources that include directory tables, external tables, streams, and materialized views.
• You can’t create dynamic tables that read from views that query other dynamic tables, unless the view is wrapped in DYNAMIC_TABLE_REFRESH_BOUNDARY().
• You can’t clone dynamic Iceberg tables. Additionally, cloning a database or schema containing a dynamic Iceberg table does not clone the table to the new location.
• You can’t set the DATA_RETENTION_TIME_IN_DAYS object parameter to zero if your base table is a shared table.

Immutability constraints¶

Support for cross-feature interactions¶

The following cross-feature interactions are not supported:

• Using the query acceleration service (QAS) for dynamic table refreshes.
• Masking policies with database roles on shared tables.
• Aggregation and projection policies cannot be applied to the base tables of dynamic tables. If a base table has aggregation or projection policies associated with it, the dynamic table will fail to create.