Categories:

Information Schema , Table functions

DYNAMIC_TABLE_GRAPH_HISTORY¶

This table function returns information on all dynamic tables in the current account. This information includes the dependencies between dynamic tables and on base tables. A common use is to identify all dynamic tables that are part of a pipeline.

In the output of this function, each row represents a dynamic table. The VALID_FROM and VALID_TO columns specify the range of time over which the description of a dynamic table was valid (i.e., accurately described the dynamic table).

Changes to a dynamic table such as altering the TARGET_LAG result in the creation of new entries.

This table function provides only descriptions with a VALID_TO value within 7 days of the current time.

Syntax¶

DYNAMIC_TABLE_GRAPH_HISTORY(
  [ AS_OF => <constant_expr> ]
  [ , HISTORY_START => <constant_expr> [ , HISTORY_END => <constant_expr> ] ]
)
Copy

Arguments¶

All arguments are optional. If no arguments are provided, only the most recent description of existing dynamic tables are returned. Specify constant_expr in TIMESTAMP_LTZ format.

AS_OF => constant_expr

Time at which to return the state of the graph. You can specify a time that corresponds to a value in the REFRESH_VERSION column in the output of the DYNAMIC_TABLE_REFRESH_HISTORY function.

HISTORY_START => constant_expr , . HISTORY_END => constant_expr

Date/time range of the dynamic table refresh history. HISTORY_START specifies the earliest date/time, inclusive, to return data. HISTORY_END, which must be specified with HISTORY_START, specifies the end date/time for returning data.

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 as it appears in the graph of dynamic tables. You can use this to join the output with the output of the DYNAMIC_TABLE_REFRESH_HISTORY function.

INPUTS

ARRAY of OBJECTs

Each OBJECT represents a table, view, or dynamic table that serves as the input to this dynamic table, and consists of:

  • name (TEXT): fully qualified name.

  • kind (TEXT): type of input (TABLE,VIEW, or DYNAMIC TABLE).

TARGET_LAG_TYPE

TEXT

One of:

  • USER_DEFINED - Determined by the TARGET_LAG parameter specified for the dynamic table.

  • DOWNSTREAM - Indicates a dynamic table with a DOWNSTREAM TARGET_LAG. Refer to Understanding dynamic table refresh for more information.

TARGET_LAG_SEC

NUMBER

The target lag time in seconds of this dynamic table. This is the value that was specified in the TARGET_LAG parameter of the dynamic table.

QUERY_TEXT

TEXT

The SELECT statement for this dynamic table.

VALID_FROM

TIMESTAMP_LTZ

The description of the dynamic table is valid after this time.

VALID_TO

TIMESTAMP_LTZ

If present, the description of the dynamic table is valid up to this time. If null, the description is still accurate.

SCHEDULING_STATE

OBJECT

OBJECT consisting of:

  • state (TEXT): Scheduling state (ACTIVE or SUSPENDED).

  • reason_code (TEXT): Optional reason for the reason if the state is not ACTIVE.

  • reason_message (TEXT): Text description of the reason the dynamic table is not active. Only applies if the state is not active.

  • suspended_on (TIMESTAMP_LTZ): Optional timestamp when the dynamic table was suspended.

  • resumed_on (TIMESTAMP_LTZ): Optional timestamp when it was last resumed if dynamic table is ACTIVE.

ALTER_TRIGGER

ARRAY

Describes why a new entry is created in the DYNAMIC_TABLE_GRAPH_HISTORY function. Can be one of the following:

  • NONE (backwards-compatible)

  • CREATE_DYNAMIC_TABLE

  • ALTER_TARGET_LAG

  • SUSPEND

  • RESUME

  • REPLICATION_REFRESH

  • ALTER_WAREHOUSE

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 information, see Snowflake Information Schema.

Examples¶

Retrieve the graph history of each dynamic table in the account, its properties, and its dependencies on other tables and dynamic tables:

SELECT
  name,
  inputs,
  target_lag_type,
  target_lag_sec,
  scheduling_state,
  alter_trigger
FROM
  TABLE (
    INFORMATION_SCHEMA.DYNAMIC_TABLE_GRAPH_HISTORY ()
  )
ORDER BY
  name;
Copy
+--------------------+---------------------------------------------------+-----------------+----------------+---------------------------------------------+------------------+
| NAME               |[] INPUTS                                          | TARGET_LAG_TYPE | TARGET_LAG_SEC | [] SCHEDULING_STATE                         | [] ALTER_TRIGGER |
|--------------------+---------------------------------------------------+-----------------+----------------+---------------------------------------------|------------------+
| MY_DYNAMIC_TABLE_1 | [                                                 | USER_DEFINED    | 300            | {                                           | [                |
|                    |  {                                                |                 |                |   "resumed_on": "2024-03-01 10:29:02.066 Z",|   "RESUME"       |
|                    |    "kind": "DYNAMIC_TABLE",                       |                 |                |   "state": "ACTIVE"                         | ]                |
|                    |    "name": "MY_QUALIFIED_NAME.MY_DYNAMIC_TABLE_2" |                 |                | }                                           |                  |
|                    |  }                                                |                 |                |                                             |                  |
|                    | ]                                                 |                 |                |                                             |                  |
+--------------------+---------------------------------------------------+-----------------+----------------+---------------------------------------------+------------------+
Language: English