Dynamic tables: Return value changes and new columns added to DYNAMIC_TABLE_GRAPH_HISTORY, DYNAMIC_TABLE_REFRESH_HISTORY, and SHOW DYNAMIC TABLES

Attention

This behavior change is in the 2024_02 bundle.

For the current status of the bundle, refer to Bundle History.

Return value behavior

The behavior of the DYNAMIC_TABLE_GRAPH_HISTORY, DYNAMIC_TABLE_REFRESH_HISTORY, and SHOW DYNAMIC TABLES functions has changed. Return values for these functions are displayed as follows.

Return value behavior for the DYNAMIC_TABLE_GRAPH_HISTORY function

When this behavior change bundle is enabled, the output of the DYNAMIC_TABLE_GRAPH_HISTORY function includes the following return value changes:

Before the change:

  • The SCHEDULING_STATE column returns "state": "RUNNING" or "state": "SUSPENDED" to describe the state of the dynamic table.

  • The SCHEDULING_STATE column returns RUNNING if an upstream table was suspended and you lacked the MONITOR privilege on that upstream table.

After the change:

  • The SCHEDULING_STATE column returns "state": "ACTIVE" or "state": "SUSPENDED" to describe the state of the dynamic table.

  • The SCHEDULING_STATE column returns SUSPENDED, even if you don’t have the MONITOR privilege on upstream tables.

Return value behavior for the DYNAMIC_TABLE_REFRESH_HISTORY function

When this behavior change bundle is enabled, the output of the DYNAMIC_TABLE_REFRESH_HISTORY function includes the following return value changes:

Before the change:

  • The LAST_COMPLETED_DEPENDENCY column might incorrectly return NULL values.

  • The STATE column returned SKIPPED for refresh jobs that were skipped due to an upstream failure.

  • Refresh histories were displayed for all dynamic table states.

After the change:

  • The LAST_COMPLETED_DEPENDENCY column now returns accurate values.

  • The STATE column now returns UPSTREAM_FAILED for refresh jobs that are skipped due to an upstream failure.

  • Refresh histories are no longer displayed if the STATE column returns QUEUED or SKIPPED. (If your dynamic table refresh was skipped due to upstream failure, the STATE column now returns UPSTREAM_FAILED instead.)

Return value behavior for the SHOW DYNAMIC TABLE function

When this behavior change bundle is enabled, the output of the SHOW DYNAMIC TABLES command includes the following return value changes:

Before the change:

  • The SCHEDULING_STATE column returns RUNNING or SUSPENDED to describe the state of the dynamic table.

After the change:

  • The SCHEDULING_STATE column returns ACTIVE or SUSPENDED to describe the state of the dynamic table.

Column changes

When enabled, the following additional columns are added to the DYNAMIC_TABLE_GRAPH_HISTORY and DYNAMIC_TABLE_REFRESH_HISTORY functions.

DYNAMIC_TABLE_GRAPH_HISTORY function: New column in output

When this behavior change bundle is enabled, the output of the DYNAMIC_TABLE_GRAPH_HISTORY function includes the following new column(s):

Column name

Data type

Description

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

DYNAMIC_TABLE_REFRESH_HISTORY function: New columns in output

When this behavior change bundle is enabled, the output of the DYNAMIC_TABLE_REFRESH_HISTORY function includes the following new column(s):

Column name

Data type

Description

TARGET_LAG

TEXT

This column describes the TARGET_LAG value for the dynamic table at the time the refresh occurred.

GRAPH_HISTORY_VALID_FROM

TIMESTAMP_NTZ

Encodes the VALID_FROM timestamp of the DYNAMIC_TABLE_GRAPH_HISTORY table function when the refresh occurred.

Ref: 1543

Language: English