Categories:: Information Schema , Table functions

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 } ]
)

Copy

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 to 10000

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.

To view these columns, you must use a role with the MONITOR privilege. Otherwise, the function only returns a value for NAME, SCHEMA_NAME, DATABASE_NAME, and QUALIFIED_NAME. For more information about dynamic table privileges, see Privileges to view a dynamic table’s metadata.

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: USER_DEFINED: Determined by the TARGET_LAG parameter specified for the dynamic table. DOWNSTREAM: Includes a dynamic table with a DOWNSTREAM target lag.
SCHEDULING_STATE	OBJECT	OBJECT consisting of: STATE (TEXT): Scheduling state (RUNNING or SUSPENDED). REASON_CODE (TEXT): Specifies the code for the reason why the dynamic table is not running. REASON_MESSAGE (TEXT): Text description of the reason the dynamic table is not running. Only applies if the dynamic table is not in the RUNNING state. SUSPENDED_ON (TIMESTAMP_LTZ): Timestamp when the dynamic table was suspended. Only applies if the dynamic table is in the SUSPENDED state. RESUMED_ON (TIMESTAMP_LTZ): Timestamp when the dynamic table was last resumed. Only applies if dynamic table is in the RUNNING state.
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: SUCCEEDED: Refresh completed successfully. FAILED: Refresh failed during execution. UPSTREAM_FAILED: Fefresh not performed due to an upstream failed refresh. CANCELLED: Refresh was canceled before execution.
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

Copy