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	VARCHAR	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. - `queuedTimeMs`: The time (in milliseconds) spent in the queued state. - `compilationTimeMs`: The time (in milliseconds) spent compiling the refresh query. - `executionTimeMs`: The time (in milliseconds) spent executing the refresh query. For successful refreshes, this column includes both the row/partition statistics and the time distribution information. For failed refreshes, this column is populated with the time distribution information only. 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	VARCHAR	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	VARCHAR	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_SEC	NUMBER	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;