DYNAMIC_TABLES¶

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.

By default, the function returns 100 rows and the results are sorted by the dynamic table’s last completed refresh state in the following order, unless specified otherwise using the RESULT_LIMIT argument.

1. FAILED

2. UPSTREAM_FAILED

3. SKIPPED

4. SUCCEEDED

5. CANCELED

To sort by a different order, you must provide a large enough RESULT_LIMIT value (for example, the maximum value of a signed integer). As long as RESULT_LIMIT exceeds the total number of dynamic tables in the account, the results can be sorted using an ORDER BY clause.

To apply a filter on the results, also specify a large enough RESULT_LIMIT value for the filter to be applied on all dynamic tables.

Examples:

The following example sorts by a different order of name and returns 100 rows:

SELECT * FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLES(result_limit => <max_value>)) ORDER BY name ASC LIMIT 100 ;

Copy

The following example sorts by a different order of name and returns all rows:

SELECT * FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLES(result_limit => <max_value>)) ORDER BY name ASC ;

Copy

The following example filters for all dynamic tables with 1-minute target lag, uses the default sort, and returns all rows:

SELECT * FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLES(result_limit => <max_value>)) WHERE TARGET_LAG_SEC = 60 ;

Copy

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.

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