Dynamic table reference¶
This page collects links to the SQL commands, monitoring views and functions, and developer APIs for dynamic tables.
SQL commands¶
Use these commands to create, modify, inspect, and remove dynamic tables.
| Command | Description |
|---|---|
| CREATE DYNAMIC TABLE | Create a dynamic table with a definition, target lag, and warehouse assignment. |
| ALTER DYNAMIC TABLE | Change properties of a dynamic table, including target lag, warehouse, suspend, and resume. |
| DROP DYNAMIC TABLE | Remove a dynamic table from the system. |
| UNDROP DYNAMIC TABLE | Restore a dynamic table that was dropped within the Time Travel retention period. |
| SHOW DYNAMIC TABLES | List dynamic tables and their properties, including resolved refresh mode and scheduling state. |
| DESCRIBE DYNAMIC TABLE | Describe the columns and data types of a dynamic table. |
Information Schema functions¶
Call these table functions from the INFORMATION_SCHEMA schema. DYNAMIC_TABLE_REFRESH_HISTORY and DYNAMIC_TABLES are scoped to the current database. DYNAMIC_TABLE_GRAPH_HISTORY returns account-level data regardless of database context. They retain 7 days of history.
| Function | Description |
|---|---|
| DYNAMIC_TABLES | Return metadata for dynamic tables, including scheduling state and current lag. |
| DYNAMIC_TABLE_REFRESH_HISTORY | Return refresh history for dynamic tables, including refresh status, duration, and data timestamp. |
| DYNAMIC_TABLE_GRAPH_HISTORY | Return the dependency graph and scheduling state history for dynamic tables in a pipeline. |
Account Usage views¶
Account Usage views are available in the SNOWFLAKE shared database and retain 365 days of history, compared to 7 days for the Information Schema functions.
| View | Description |
|---|---|
| DYNAMIC_TABLE_REFRESH_HISTORY view | Return refresh history across all dynamic tables in the account, with 365-day retention. |
System functions¶
| Function | Description |
|---|---|
| SYSTEM$SHOW_DYNAMIC_TABLES_CREATED_FOR_RESHARING | List hidden dynamic tables created for cross-region resharing of listings. |
Developer APIs¶
Use these APIs to manage dynamic tables programmatically.
| API | Description |
|---|---|
| Manage dynamic tables | Manage dynamic tables through the Snowflake REST API. |
| Managing Snowflake dynamic tables with Python | Create, alter, and drop dynamic tables with the Snowflake Python API. |
| Snowflake Python APIs: Managing Snowflake objects with Python | Overview of the Snowflake Python API for managing Snowflake objects programmatically. |