dynamic_tables

Creates, updates, deletes, gets or lists a dynamic_tables resource.

Overview

Name	`dynamic_tables`
Type	Resource
Id	`snowflake.tables.dynamic_tables`

Fields

The following fields are returned by SELECT queries:

get
list

successful

Name	Datatype	Description
`name`	`string`	Specifies the name for the dynamic table, must be unique for the schema in which the dynamic table is created
`database_name`	`string`	Database in which the dynamic table is stored
`schema_name`	`string`	Schema in which the dynamic table is stored
`automatic_clustering`	`boolean`	If Automatic Clustering is enabled for your account, specifies whether it is explicitly enabled or disabled for the dynamic table.
`budget`	`string`	Name of the budget if the object is monitored by a budget
`bytes`	`integer (int64)`	Number of bytes that will be scanned if the entire table is scanned in a query. Note that this number may be different than the number of actual physical bytes stored on-disk for the table
`cluster_by`	`array`	Specifies one or more columns or column expressions in the dynamic table as the clustering key
`columns`	`array`
`comment`	`string`	Specifies a comment for the dynamic table.
`created_on`	`string (date-time)`	Date and time when the dynamic table was created.
`data_retention_time_in_days`	`integer`	Specifies the retention period for the dynamic table so that Time Travel actions (SELECT, CLONE) can be performed on historical data in the dynamic table
`initialize`	`string`	Specifies the behavior of the initial refresh of the dynamic table (ON_CREATE, ON_SCHEDULE)
`kind`	`string`	Specifies the dynamic table type, permanent (default) or transient. (PERMANENT, TRANSIENT) (default: PERMANENT)
`max_data_extension_time_in_days`	`integer`	Specifies the retention period for the dynamic table so that Time Travel actions (SELECT, CLONE) can be performed on historical data in the dynamic table
`owner`	`string`	Role that owns the table
`owner_role_type`	`string`	The type of role that owns the object.
`query`	`string`	Specifies the query whose results the dynamic table should contain (example: SELECT * FROM foo)
`refresh_mode`	`string`	Specifies the refresh type for the dynamic table (AUTO, FULL, INCREMENTAL)
`rows`	`integer (int64)`	Number of rows in the dynamic table.
`scheduling_state`	`string`	Scheduling state (RUNNING or SUSPENDED) (RUNNING, SUSPENDED)
`target_lag`	`object`	Specifies the schedule for periodically refreshing the dynamic table.
`warehouse`	`string`	Specifies the name of the warehouse that provides the compute resources for refreshing the dynamic table (example: test_wh)

A Snowflake dynamic table object.

Name	Datatype	Description
`name`	`string`	Specifies the name for the dynamic table, must be unique for the schema in which the dynamic table is created
`database_name`	`string`	Database in which the dynamic table is stored
`schema_name`	`string`	Schema in which the dynamic table is stored
`automatic_clustering`	`boolean`	If Automatic Clustering is enabled for your account, specifies whether it is explicitly enabled or disabled for the dynamic table.
`budget`	`string`	Name of the budget if the object is monitored by a budget
`bytes`	`integer (int64)`	Number of bytes that will be scanned if the entire table is scanned in a query. Note that this number may be different than the number of actual physical bytes stored on-disk for the table
`cluster_by`	`array`	Specifies one or more columns or column expressions in the dynamic table as the clustering key
`columns`	`array`
`comment`	`string`	Specifies a comment for the dynamic table.
`created_on`	`string (date-time)`	Date and time when the dynamic table was created.
`data_retention_time_in_days`	`integer`	Specifies the retention period for the dynamic table so that Time Travel actions (SELECT, CLONE) can be performed on historical data in the dynamic table
`initialize`	`string`	Specifies the behavior of the initial refresh of the dynamic table (ON_CREATE, ON_SCHEDULE)
`kind`	`string`	Specifies the dynamic table type, permanent (default) or transient. (PERMANENT, TRANSIENT) (default: PERMANENT)
`max_data_extension_time_in_days`	`integer`	Specifies the retention period for the dynamic table so that Time Travel actions (SELECT, CLONE) can be performed on historical data in the dynamic table
`owner`	`string`	Role that owns the table
`owner_role_type`	`string`	The type of role that owns the object.
`query`	`string`	Specifies the query whose results the dynamic table should contain (example: SELECT * FROM foo)
`refresh_mode`	`string`	Specifies the refresh type for the dynamic table (AUTO, FULL, INCREMENTAL)
`rows`	`integer (int64)`	Number of rows in the dynamic table.
`scheduling_state`	`string`	Scheduling state (RUNNING or SUSPENDED) (RUNNING, SUSPENDED)
`target_lag`	`object`	Specifies the schedule for periodically refreshing the dynamic table.
`warehouse`	`string`	Specifies the name of the warehouse that provides the compute resources for refreshing the dynamic table (example: test_wh)

Methods

The following methods are available for this resource:

Name	Accessible by	Required Params	Optional Params	Description
`get`	`select`	`database_name`, `schema_name`, `name`, `endpoint`		Fetch a Dynamic Table.
`list`	`select`	`database_name`, `schema_name`, `endpoint`	`like`, `startsWith`, `showLimit`, `fromName`, `deep`	Lists the dynamic tables under the database and schema.
`create`	`insert`	`database_name`, `schema_name`, `endpoint`, `name`, `target_lag`, `warehouse`, `query`	`createMode`	Create a dynamic table, with standard create modifiers as query parameters. See the Dynamic Table component definition for what is required to be provided in the request body.
`delete`	`delete`	`database_name`, `schema_name`, `name`, `endpoint`	`ifExists`	Delete a dynamic table with the given name. If ifExists is used, the operation will succeed even if the object does not exist. Otherwise, there will be a failure if the drop is unsuccessful.
`clone`	`exec`	`database_name`, `schema_name`, `name`, `endpoint`, `name`	`createMode`, `copyGrants`, `targetDatabase`, `targetSchema`	Create a new dynamic table by cloning from the specified resource
`undrop`	`exec`	`database_name`, `schema_name`, `name`, `endpoint`		Undrop specified dynamic table
`suspend`	`exec`	`database_name`, `schema_name`, `name`, `endpoint`	`ifExists`	Suspend refreshes on the dynamic table
`resume`	`exec`	`database_name`, `schema_name`, `name`, `endpoint`	`ifExists`	Resume refreshes on the dynamic table
`refresh`	`exec`	`database_name`, `schema_name`, `name`, `endpoint`	`ifExists`	Specifies that the dynamic table should be manually refreshed
`suspend_recluster`	`exec`	`database_name`, `schema_name`, `name`, `endpoint`	`ifExists`	Suspend recluster of a dynamic table
`resume_recluster`	`exec`	`database_name`, `schema_name`, `name`, `endpoint`	`ifExists`	Resume recluster of a dynamic table
`swap_with`	`exec`	`database_name`, `schema_name`, `name`, `targetName`, `endpoint`	`ifExists`, `targetDatabase`, `targetSchema`	Swap with another dynamic table

Parameters

Parameters can be passed in the WHERE clause of a query. Check the Methods section to see which parameters are required or optional for each operation.

Name	Datatype	Description
`database_name`	`string`	Identifier (i.e. name) for the database to which the resource belongs. You can use the `/api/v2/databases` GET request to get a list of available databases.
`endpoint`	`string`	Organization and account identifier (orgname-accountname) (default: orgname-accountname)
`name`	`string`	Identifier (i.e. name) for the resource.
`schema_name`	`string`	Identifier (i.e. name) for the schema to which the resource belongs. You can use the `/api/v2/databases/{database}/schemas` GET request to get a list of available schemas for the specified database.
`targetName`	`string`	The name of the target dynamic table to be swapped with.
`copyGrants`	`boolean`	Parameter to enable copy grants when creating the object.
`createMode`	`string`	Parameter allowing support for different modes of resource creation. Possible values include: - `errorIfExists`: Throws an error if you try to create a resource that already exists. - `orReplace`: Automatically replaces the existing resource with the current one. - `ifNotExists`: Creates a new resource when an alter is requested for a non-existent resource.
`deep`	`boolean`	Optionally includes dependency information of the dynamic table.
`fromName`	`string`	Parameter to enable fetching rows only following the first row whose object name matches the specified string. Case-sensitive and does not have to be the full name.
`ifExists`	`boolean`	Parameter that specifies how to handle the request for a resource that does not exist: - `true`: The endpoint does not throw an error if the resource does not exist. It returns a 200 success response, but does not take any action on the resource. - `false`: The endpoint throws an error if the resource doesn't exist.
`like`	`string`	Parameter to filter the command output by resource name. Uses case-insensitive pattern matching, with support for SQL wildcard characters.
`showLimit`	`integer`	Parameter to limit the maximum number of rows returned by a command.
`startsWith`	`string`	Parameter to filter the command output based on the string of characters that appear at the beginning of the object name. Uses case-sensitive pattern matching.
`targetDatabase`	`string`	Database of the target dynamic table. Defaults to the source table's database.
`targetSchema`	`string`	Schema of the target dynamic table. Defaults to the source table's schema.

`SELECT` examples

get
list

Fetch a Dynamic Table.

SELECT
name,
database_name,
schema_name,
automatic_clustering,
budget,
bytes,
cluster_by,
columns,
comment,
created_on,
data_retention_time_in_days,
initialize,
kind,
max_data_extension_time_in_days,
owner,
owner_role_type,
query,
refresh_mode,
rows,
scheduling_state,
target_lag,
warehouse
FROM snowflake.tables.dynamic_tables
WHERE database_name = '{{ database_name }}' -- required
AND schema_name = '{{ schema_name }}' -- required
AND name = '{{ name }}' -- required
AND endpoint = '{{ endpoint }}' -- required
;

Lists the dynamic tables under the database and schema.

SELECT
name,
database_name,
schema_name,
automatic_clustering,
budget,
bytes,
cluster_by,
columns,
comment,
created_on,
data_retention_time_in_days,
initialize,
kind,
max_data_extension_time_in_days,
owner,
owner_role_type,
query,
refresh_mode,
rows,
scheduling_state,
target_lag,
warehouse
FROM snowflake.tables.dynamic_tables
WHERE database_name = '{{ database_name }}' -- required
AND schema_name = '{{ schema_name }}' -- required
AND endpoint = '{{ endpoint }}' -- required
AND "like" = '{{ like }}'
AND startsWith = '{{ startsWith }}'
AND showLimit = '{{ showLimit }}'
AND fromName = '{{ fromName }}'
AND deep = '{{ deep }}'
;

`INSERT` examples

create
Manifest

Create a dynamic table, with standard create modifiers as query parameters. See the Dynamic Table component definition for what is required to be provided in the request body.

INSERT INTO snowflake.tables.dynamic_tables (
name,
kind,
columns,
target_lag,
refresh_mode,
initialize,
warehouse,
cluster_by,
query,
data_retention_time_in_days,
max_data_extension_time_in_days,
comment,
database_name,
schema_name,
endpoint,
createMode
)
SELECT 
'{{ name }}' /* required */,
'{{ kind }}',
'{{ columns }}',
'{{ target_lag }}' /* required */,
'{{ refresh_mode }}',
'{{ initialize }}',
'{{ warehouse }}' /* required */,
'{{ cluster_by }}',
'{{ query }}' /* required */,
{{ data_retention_time_in_days }},
{{ max_data_extension_time_in_days }},
'{{ comment }}',
'{{ database_name }}',
'{{ schema_name }}',
'{{ endpoint }}',
'{{ createMode }}'
RETURNING
status
;

# Description fields are for documentation purposes
- name: dynamic_tables
props:
  - name: database_name
    value: "{{ database_name }}"
    description: Required parameter for the dynamic_tables resource.
  - name: schema_name
    value: "{{ schema_name }}"
    description: Required parameter for the dynamic_tables resource.
  - name: endpoint
    value: "{{ endpoint }}"
    description: Required parameter for the dynamic_tables resource.
  - name: name
    value: "{{ name }}"
    description: |
      Specifies the name for the dynamic table, must be unique for the schema in which the dynamic table is created
  - name: kind
    value: "{{ kind }}"
    description: |
      Specifies the dynamic table type, permanent (default) or transient.
    valid_values: ['PERMANENT', 'TRANSIENT']
    default: PERMANENT
  - name: columns
    value:
      - name: "{{ name }}"
        datatype: "{{ datatype }}"
        comment: "{{ comment }}"
  - name: target_lag
    description: |
      Specifies the schedule for periodically refreshing the dynamic table.
    value:
      type: "{{ type }}"
  - name: refresh_mode
    value: "{{ refresh_mode }}"
    description: |
      Specifies the refresh type for the dynamic table
    valid_values: ['AUTO', 'FULL', 'INCREMENTAL']
  - name: initialize
    value: "{{ initialize }}"
    description: |
      Specifies the behavior of the initial refresh of the dynamic table
    valid_values: ['ON_CREATE', 'ON_SCHEDULE']
  - name: warehouse
    value: "{{ warehouse }}"
    description: |
      Specifies the name of the warehouse that provides the compute resources for refreshing the dynamic table
  - name: cluster_by
    value:
      - "{{ cluster_by }}"
    description: |
      Specifies one or more columns or column expressions in the dynamic table as the clustering key
  - name: query
    value: "{{ query }}"
    description: |
      Specifies the query whose results the dynamic table should contain
  - name: data_retention_time_in_days
    value: {{ data_retention_time_in_days }}
    description: |
      Specifies the retention period for the dynamic table so that Time Travel actions (SELECT, CLONE) can be performed on historical data in the dynamic table
  - name: max_data_extension_time_in_days
    value: {{ max_data_extension_time_in_days }}
    description: |
      Specifies the retention period for the dynamic table so that Time Travel actions (SELECT, CLONE) can be performed on historical data in the dynamic table
  - name: comment
    value: "{{ comment }}"
    description: |
      Specifies a comment for the dynamic table.
  - name: createMode
    value: "{{ createMode }}"
    description: Parameter allowing support for different modes of resource creation. Possible values include: - `errorIfExists`: Throws an error if you try to create a resource that already exists. - `orReplace`: Automatically replaces the existing resource with the current one. - `ifNotExists`: Creates a new resource when an alter is requested for a non-existent resource.
    description: Parameter allowing support for different modes of resource creation. Possible values include: - `errorIfExists`: Throws an error if you try to create a resource that already exists. - `orReplace`: Automatically replaces the existing resource with the current one. - `ifNotExists`: Creates a new resource when an alter is requested for a non-existent resource.

`DELETE` examples

delete

Delete a dynamic table with the given name. If ifExists is used, the operation will succeed even if the object does not exist. Otherwise, there will be a failure if the drop is unsuccessful.

DELETE FROM snowflake.tables.dynamic_tables
WHERE database_name = '{{ database_name }}' --required
AND schema_name = '{{ schema_name }}' --required
AND name = '{{ name }}' --required
AND endpoint = '{{ endpoint }}' --required
AND ifExists = '{{ ifExists }}'
;

Lifecycle Methods

Create a new dynamic table by cloning from the specified resource

EXEC snowflake.tables.dynamic_tables.clone 
@database_name='{{ database_name }}' --required, 
@schema_name='{{ schema_name }}' --required, 
@name='{{ name }}' --required, 
@endpoint='{{ endpoint }}' --required, 
@createMode='{{ createMode }}', 
@copyGrants={{ copyGrants }}, 
@targetDatabase='{{ targetDatabase }}', 
@targetSchema='{{ targetSchema }}' 
@@json=
'{
"name": "{{ name }}", 
"target_lag": "{{ target_lag }}", 
"warehouse": "{{ warehouse }}", 
"point_of_time": "{{ point_of_time }}"
}'
;

Undrop specified dynamic table

EXEC snowflake.tables.dynamic_tables.undrop 
@database_name='{{ database_name }}' --required, 
@schema_name='{{ schema_name }}' --required, 
@name='{{ name }}' --required, 
@endpoint='{{ endpoint }}' --required
;

Suspend refreshes on the dynamic table

EXEC snowflake.tables.dynamic_tables.suspend 
@database_name='{{ database_name }}' --required, 
@schema_name='{{ schema_name }}' --required, 
@name='{{ name }}' --required, 
@endpoint='{{ endpoint }}' --required, 
@ifExists={{ ifExists }}
;

Resume refreshes on the dynamic table

EXEC snowflake.tables.dynamic_tables.resume 
@database_name='{{ database_name }}' --required, 
@schema_name='{{ schema_name }}' --required, 
@name='{{ name }}' --required, 
@endpoint='{{ endpoint }}' --required, 
@ifExists={{ ifExists }}
;

Specifies that the dynamic table should be manually refreshed

EXEC snowflake.tables.dynamic_tables.refresh 
@database_name='{{ database_name }}' --required, 
@schema_name='{{ schema_name }}' --required, 
@name='{{ name }}' --required, 
@endpoint='{{ endpoint }}' --required, 
@ifExists={{ ifExists }}
;

Suspend recluster of a dynamic table

EXEC snowflake.tables.dynamic_tables.suspend_recluster 
@database_name='{{ database_name }}' --required, 
@schema_name='{{ schema_name }}' --required, 
@name='{{ name }}' --required, 
@endpoint='{{ endpoint }}' --required, 
@ifExists={{ ifExists }}
;

Resume recluster of a dynamic table

EXEC snowflake.tables.dynamic_tables.resume_recluster 
@database_name='{{ database_name }}' --required, 
@schema_name='{{ schema_name }}' --required, 
@name='{{ name }}' --required, 
@endpoint='{{ endpoint }}' --required, 
@ifExists={{ ifExists }}
;

Swap with another dynamic table

EXEC snowflake.tables.dynamic_tables.swap_with 
@database_name='{{ database_name }}' --required, 
@schema_name='{{ schema_name }}' --required, 
@name='{{ name }}' --required, 
@targetName='{{ targetName }}' --required, 
@endpoint='{{ endpoint }}' --required, 
@ifExists={{ ifExists }}, 
@targetDatabase='{{ targetDatabase }}', 
@targetSchema='{{ targetSchema }}'
;

Overview​

Fields​

Methods​

Parameters​

SELECT examples​

INSERT examples​

DELETE examples​

Lifecycle Methods​