CREATE DYNAMIC TABLE¶
Creates a dynamic table, based on a specified query.
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:
Privilege |
Object |
Notes |
---|---|---|
CREATE DYNAMIC TABLE |
Schema in which you plan to create the dynamic table. |
|
SELECT |
Tables, views, and dynamic tables that you plan to query for the new dynamic table. |
|
USAGE |
Warehouse that you plan to use to refresh the table. |
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;
In this example:
The dynamic table materializes the results of a query of the
product_id
andproduct_name
columns of thestaging_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.