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 the arguments are optional. If no arguments are provided, then only the most recent description of currently existing dynamic tables will be returned.

AS_OF => constant_expr: Time (in TIMESTAMP_LTZ format) 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 details, see Snowflake Information Schema.