Categories:: Information Schema , Table Functions

DYNAMIC_TABLE_REFRESH_HISTORY¶

This table function returns information about each refresh (completed and running) of dynamic tables.

This table function returns all refreshes that are in progress as well as all refreshes that have a REFRESH_VERSION within 1 day of the current time.

Syntax¶

DYNAMIC_TABLE_REFRESH_HISTORY(
  [ DATA_TIMESTAMP_START => <constant_expr> ]
  [ , DATA_TIMESTAMP_END => <constant_expr> ]
  [ , RESULT_LIMIT => <integer> ]
  [ , NAME => '<string>' ]
  [ , NAME_PREFIX => '<string>' ]
  [ , ERROR_ONLY => { TRUE | FALSE } ]
)

Copy

Arguments¶

All the arguments are optional. If no arguments are provided, 100 refreshes from all dynamic tables in the account will be returned.

DATA_TIMESTAMP_START => constant_expr , . DATA_TIMESTAMP_END => constant_expr

Time range (in TIMESTAMP_LTZ format) during which the refreshes occurred.

If neither a start version nor an end version is specified, the default range will be the past day.
If an end version is not specified, CURRENT_TIMESTAMP is used as the end of the range.
If a start version is not specified, the range starts 1 day prior to the start of DATE_TIMESTAMP_END.

RESULT_LIMIT => integer

A number specifying the maximum number of rows returned by the function.

If the number of matching rows is greater than this limit, the refreshes that finished most recently (and those that are still running) are returned, up to the specified limit.

Range: 1 to 10000

Default: 100.

NAME => string

The name of a dynamic table.

Names must be single-quoted and are case insensitive.

You can specify the unqualified name (dynamic_table_name), the partially qualified name (schema_name.dynamic_table_name), or the fully qualified name (database_name.schema_name.dynamic_table_name).

For more information on object name resolution, refer to Object Name Resolution.

The function returns the refreshes for this table.

NAME_PREFIX => string

A prefix for dynamic tables.

Name prefixes must be single-quoted and are case insensitive.

The function returns refreshes for tables with names that start with this prefix.

You can use this argument to return the refreshes for dynamic tables in a specific database or schema.

ERROR_ONLY => TRUE | FALSE

When set to TRUE, this function returns only refreshes that failed or were cancelled.

Output¶

The function returns the following columns:

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.
STATE	TEXT	Status of the refresh for the dynamic table. The status can be one of the following: SCHEDULED: refresh scheduled, but not yet executed. EXECUTING: refresh in progress. SUCCEEDED: refresh completed successfully. FAILED: refresh failed during execution. CANCELLED: refresh was cancelled before execution. SKIPPED: indicating that the automated refresh process did not perform the refresh for some reason. For example, because an input dynamic table was not refreshed or to reduce the load for consistent performance.
STATE_CODE	TEXT	Code representing the current state of the refresh.
STATE_MESSAGE	TEXT	Description of the current state of the refresh.
QUERY_ID	TEXT	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. Equal to the previous REFRESH_VERSION + 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. 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

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.

Examples¶

Retrieve the refreshes that failed or were cancelled:

SELECT
  name,
  state,
  state_code,
  state_message,
  query_id,
  data_timestamp,
  refresh_start_time,
  refresh_end_time
FROM
  TABLE (
    INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY (
      NAME_PREFIX => 'MYDB.MYSCHEMA.', ERROR_ONLY => TRUE
    )
  )
ORDER BY
  name,
  data_timestamp;

Copy