CREATE DYNAMIC TABLE¶

Syntax¶

CREATE [ OR REPLACE ] DYNAMIC TABLE <name>
  TARGET_LAG = { '<num> { seconds | minutes | hours | days }' | DOWNSTREAM }
  WAREHOUSE = <warehouse_name>
  AS <query>
  [ COMMENT = '<string_literal>' ]

Required Parameters¶

name

Specifies the identifier (i.e. name) for the dynamic table; must be unique for the schema in which the dynamic table is created.

In addition, the identifier must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (e.g. "My object"). Identifiers enclosed in double quotes are also case-sensitive.

For more details, see Identifier requirements.

TARGET_LAG = { num { seconds | minutes | hours | days } | DOWNSTREAM }

Specifies the lag for the dynamic table:

'num seconds | minutes | hours | days'

Specifies the maximum amount of time that the dynamic table’s content should lag behind updates to the base tables.

For example:

• If the data in the dynamic table should lag by no more than 5 minutes, specify 5 minutes.

• If the data in the dynamic table should lag by no more than 5 hours, specify 5 hours.

The minimum value is 1 minute. If the dynamic table A depends on another dynamic table B, the minimum lag for A must be greater than or equal to the lag for B.

DOWNSTREAM

Specifies that the dynamic table should be refreshed only when dynamic tables which depend on it are refreshed.

WAREHOUSE = warehouse_name

Specifies the name of the warehouse that provides the compute resources for refreshing the dynamic table.

You must have the USAGE privilege on this warehouse in order to create the dynamic table.

AS query

Specifies the query whose results the dynamic table should contain.

Optional Parameters¶

COMMENT = 'string_literal'

Specifies a comment for the dynamic table.

Default: No value.

Access Control Requirements¶

A role used to execute this SQL command must have the following privileges at a minimum:

Note that operating on any object in a schema also requires the USAGE privilege on the parent database and schema.

For instructions on creating a custom role with a specified set of privileges, see Creating Custom Roles.

For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.

Usage Notes¶

When you execute the CREATE DYNAMIC TABLE command, the current role in use becomes the owner of the dynamic table. This role is used to perform refreshes of the dynamic table in the background.

Dynamic tables are updated as underlying database objects change. Change tracking must be enabled on all underlying objects used by a dynamic table. See Dynamic tables and change tracking.

Customers should ensure that no personal data (other than for a User object), sensitive data, export-controlled data, or other regulated data is entered as metadata when using the Snowflake service. For more information, see Metadata Fields in Snowflake.

Examples¶

Create a dynamic table named product:

CREATE OR REPLACE DYNAMIC TABLE product
 TARGET_LAG = '20 minutes'
  WAREHOUSE = mywh
  AS
    SELECT product_id, product_name FROM staging_table;

Copy

In this example:

• The dynamic table materializes the results of a query of the product_id and product_name columns of the staging_table table.

• The target lag time is 20 minutes, which means that the data in the dynamic table should ideally be no more than 20 minutes older than the data in staging_table.

• The automated refresh process uses the compute resources in warehouse mywh to refresh the data in the dynamic table.