Schema:

ACCOUNT_USAGE

DYNAMIC_TABLE_REFRESH_HISTORY view¶

This Account Usage view displays information for dynamic table refresh history.

See also:

DYNAMIC_TABLE_REFRESH_HISTORY (Information Schema)

Columns¶

Column Name

Data Type

Description

NAME

VARCHAR

Name of the dynamic table.

SCHEMA_NAME

VARCHAR

Name of the schema that contains the dynamic table.

DATABASE_NAME

VARCHAR

Name of the database that contains the dynamic table.

ID

NUMBER

Internal, Snowflake-generated identifier for the dynamic table.

SCHEMA_ID

NUMBER

Internal, Snowflake-generated identifier of the schema that contains the dynamic table.

DATABASE_ID

NUMBER

Internal, Snowflake-generated identifier of the database that contains the dynamic table.

STATE

VARCHAR

Status of the refresh for the dynamic table. This can be one of the following: . - EXECUTING: refresh in progress. . - SUCCEEDED: refresh completed successfully. . - FAILED: refresh failed during execution. . - CANCELLED: refresh was canceled before execution. . - UPSTREAM_FAILED: refresh not performed due to an upstream failed refresh.

STATE_CODE

VARCHAR

Code representing the current state of the refresh.

STATE_MESSAGE

VARCHAR

Description of the current state of the refresh.

QUERY_ID

VARCHAR

ID of the SQL statement that produced the results for the dynamic table.

DATA_TIMESTAMP

TIMESTAMP_LTZ

Transactional timestamp when the refresh was evaluated. (This might be slightly before the actual time of the refresh.) All data, in base objects, that arrived before this timestamp is currently included in the dynamic table.

REFRESH_START_TIME

TIMESTAMP_LTZ

Time when the refresh job started.

REFRESH_END_TIME

TIMESTAMP_LTZ

Time when the refresh completed.

COMPLETION_TARGET

TIMESTAMP_LTZ

Time by which this refresh should complete to keep lag under the TARGET_LAG parameter for the dynamic table. This is equal to the DATA_TIMESTAMP of the last refresh + TARGET_LAG.

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_GRAPH_HISTORY function.

LAST_COMPLETED_DEPENDENCY

OBJECT

Contains the following properties: . - qualified_name: The qualified name of the latest dependency to become available. . - data_timestamp: The refresh version of that dependency.

STATISTICS

OBJECT

Contains the following properties: . - numInsertedRows: The number of inserted rows. . - numDeletedRows: The number of rows that were deleted. . - numCopiedRows: The number of rows that were copied unchanged. . - numAddedPartitions: The number of added partitions. . - numRemovedPartitions : The number of removed partitions. . For example: If an UPDATE statement updates 1 row in a partition with 10 rows. Then the metrics above show 1 row inserted, 1 deleted, and 9 copied. Additionally, 1 partition is removed and 1 partition added.

REFRESH_ACTION

TEXT

One of: . - NO_DATA - no new data in base tables. Doesn’t apply to the initial refresh of newly created dynamic tables regardless of whether or not the base tables have data. . - REINITIALIZE - base table changed or source table of a cloned dynamic table was refreshed during clone. . - FULL - Full refresh, because dynamic table contains query elements that are not incrementalizable (see SHOW DYNAMIC TABLE refresh_mode_reason) or because full refresh was cheaper than incremental refresh. . - INCREMENTAL - normal incremental refresh.

REFRESH_TRIGGER

TEXT

One of: . - SCHEDULED - normal background refresh to meet target lag or downstream target lag. . - MANUAL - user/task used ALTER DYNAMIC TABLE <name> REFRESH . - CREATION - refresh performed during the creation DDL statement, triggered by the creation of the dynamic table or any consumer dynamic tables.

TARGET_LAG

TEXT

Describes the target lag value for the dynamic tables 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 to clarify which version of a dynamic table a specific refresh corresponds to. This value can also be NULL if the corresponding dynamic table hasn’t been created.

Usage notes¶

  • Latency for the view may be up to 3 hours.

  • To query this view, use a role that is granted the SNOWFLAKE.USAGE_VIEWER database role.

Examples¶

Find failed dynamic table refreshes during the past week.

SELECT
    data_timestamp,
    database_name,
    schema_name,
    name,
    state,
    state_message,
    query_id
  FROM snowflake.account_usage.dynamic_table_refresh_history
  WHERE state = 'FAILED' AND data_timestamp >= dateadd(WEEK, -1, current_date())
  ORDER BY data_timestamp DESC
  LIMIT 10;
Copy
Language: English