- Categories:
DYNAMIC_TABLES¶
This table function returns metadata about dynamic tables, including aggregate lag metrics and the status of the most recent refreshes, within 7 days of the current time.
Syntax¶
DYNAMIC_TABLES (
[ NAME => '<string>' ]
[ , REFRESH_DATA_TIMESTAMP_START => <constant_expr> ]
[ , RESULT_LIMIT => <integer> ]
[ , INCLUDE_CONNECTED => { TRUE | FALSE } ]
)
Arguments¶
All the arguments are optional. If no arguments are provided, 100 refreshes from all dynamic tables in the account will be returned.
NAME => 'string'
The name of a dynamic table.
Names must be single-quoted and are case insensitive.
You can specify the unqualified name (
dynamic_table_name
), the partially qualified name (schema_name.dynamic_table_name
), or the fully qualified name (database_name.schema_name.dynamic_table_name
).For more information on object name resolution, refer to Object name resolution.
The function returns the metadata for this table.
REFRESH_DATA_TIMESTAMP_START => constant_expr
Time (in TIMESTAMP_LTZ format) for computing metrics related to dynamic table target lag. Includes all refreshes with LATEST_DATA_TIMESTAMP greater than or equal to REFRESH_DATA_TIMESTAMP_START.
Default: All refreshes in refresh history are retained for 7 days.
RESULT_LIMIT => integer
A number specifying the maximum number of rows returned by the function.
If the number of matching rows is greater than this limit, the refreshes that finished most recently (and those that are still running) are returned, up to the specified limit.
Range:
1
to10000
Default:
100
.INCLUDE_CONNECTED => { TRUE | FALSE }
When set to TRUE, the function returns metadata for all dynamic tables connected to the dynamic table specified by the NAME argument.
You must specify the NAME argument, you must not specify the RESULT_LIMIT argument.
Default:
FALSE
Output¶
The function returns the following columns:
Column Name |
Data Type |
Description |
---|---|---|
NAME |
TEXT |
Name of the dynamic table. |
SCHEMA_NAME |
TEXT |
Name of the schema that contains the dynamic table. |
DATABASE_NAME |
TEXT |
Name of the database that contains the dynamic table. |
QUALIFIED_NAME |
TEXT |
Fully qualified name of the dynamic table. |
TARGET_LAG_SEC |
NUMBER |
Target lag time in seconds of the dynamic table. This is the value that was specified in the TARGET_LAG parameter of the dynamic table. |
TARGET_LAG_TYPE |
TEXT |
The type of target lag. Can be one of the following:
|
SCHEDULING_STATE |
OBJECT |
OBJECT consisting of:
|
MEAN_LAG_SEC |
NUMBER |
The mean lag time (in seconds) of refreshes for this dynamic table. |
MAXIMUM_LAG_SEC |
NUMBER |
The maximum lag time in seconds of refreshes for this dynamic table. |
TIME_ABOVE_TARGET_LAG_SEC |
NUMBER |
The time in seconds in the retention period or since the last configuration change, when the actual lag was more than the defined target lag. |
TIME_WITHIN_TARGET_LAG_RATIO |
NUMBER |
The ratio of time in the retention period or since the last configuration change, when actual lag is within the target lag. |
LATEST_DATA_TIMESTAMP |
TIMESTAMP_LTZ |
Data timestamp of the last successful refresh. |
LAST_COMPLETED_REFRESH_STATE |
TEXT |
Status of the last terminated refresh for the dynamic table. Can be one of the following:
|
LAST_COMPLETED_REFRESH_STATE_CODE |
TEXT |
Code representing the current state of the refresh. If the LAST_COMPLETED_REFRESH_STATE is FAILED, this column shows the error code associated with the failure. |
LAST_COMPLETED_REFRESH_STATE_MESSAGE |
TEXT |
Description of the current state of the refresh. If the LAST_COMPLETED_REFRESH_STATE is FAILED, this column shows the error message associated with the failure. |
EXECUTING_REFRESH_QUERY |
TEXT |
If present, this represents the query ID of the refresh job. If null, there is no refresh job in progress. |
Usage notes¶
When calling an Information Schema table function, the session must have an INFORMATION_SCHEMA schema in use or the function name must be fully-qualified. For more details, see Snowflake Information Schema.
Examples¶
Retrieve the names, lag information, and data timestamp of the last successful refresh for all dynamic tables connected with the specified dynamic table.
SELECT
name,
target_lag_sec,
mean_lag_sec,
latest_data_timestamp
FROM
TABLE (
INFORMATION_SCHEMA.DYNAMIC_TABLES (
NAME => 'mydb.myschema.mydt',
INCLUDE_CONNECTED => TRUE
)
)
ORDER BY
target_lag_sec