Schema:: ACCOUNT_USAGE

CORTEX_SEARCH_REFRESH_HISTORY view¶

This Account Usage view displays information for Cortex Search service refresh history.

Columns¶

Column Name	Data Type	Description
NAME	VARCHAR	Name of the Cortex Search service.
SCHEMA_NAME	VARCHAR	Name of the schema that contains the Cortex Search service.
DATABASE_NAME	VARCHAR	Name of the database that contains the Cortex Search service.
STATE	VARCHAR	Status of the refresh for the Cortex Search service. 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.
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 Cortex Search service.
REFRESH_START_TIME	TIMESTAMP_LTZ	Time when the refresh job started.
REFRESH_END_TIME	TIMESTAMP_LTZ	Time when the refresh completed.
INDEX_PREPROCESSING_DURATION	NUMBER	Duration of the index preprocessing phase in milliseconds.
INDEX_PREPROCESSING_QUERY_ID	VARCHAR	ID of the query that performed the index preprocessing.
INDEX_PREPROCESSING_STATISTICS	OBJECT	Contains the following properties for index preprocessing: . - `compilationTimeMs`: Time spent compiling the query in milliseconds. . - `executionTimeMs`: Time spent executing the query in milliseconds. . - `queuedTimeMs`: Time spent queued before execution in milliseconds. . - `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.
INDEXING_DURATION	NUMBER	Duration of the indexing phase in milliseconds.
INDEXING_QUERY_ID	VARCHAR	ID of the query that performed the indexing.
REFRESH_ACTION	VARCHAR	One of: . - NO_DATA - no new data in base tables. . - FULL - full refresh of the Cortex Search service. . - INCREMENTAL - incremental refresh of the Cortex Search service.
REFRESH_TRIGGER	VARCHAR	One of: . - SCHEDULED - normal background refresh to keep the service up to date. . - MANUAL - user manually triggered refresh using ALTER CORTEX SEARCH SERVICE. . - CREATION - refresh performed during the creation DDL statement.
TARGET_LAG_SEC	NUMBER	Describes the target lag value for the Cortex Search service at the time the refresh occurred.
WAREHOUSE	VARCHAR	Name of the warehouse used for the refresh operation.
ERROR	VARCHAR	Error message if the refresh failed, otherwise NULL.

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.
The following arguments can be used to filter the results:
- NAME: Filter by Cortex Search service name.
- DATA_TIMESTAMP_START: Filter results to refreshes that occurred on or after the specified timestamp.
- DATA_TIMESTAMP_END: Filter results to refreshes that occurred on or before the specified timestamp.
- RESULT_LIMIT: Limit the number of results returned.

Examples¶

Find failed Cortex Search service refreshes during the past week:

SELECT
    data_timestamp,
    database_name,
    schema_name,
    name,
    state,
    error,
    refresh_trigger
  FROM snowflake.account_usage.cortex_search_refresh_history
  WHERE state = 'FAILED' AND data_timestamp >= dateadd(WEEK, -1, current_date())
  ORDER BY data_timestamp DESC
  LIMIT 10;

Copy

Find recent manual refreshes for a specific Cortex Search service:

SELECT
    data_timestamp,
    refresh_start_time,
    refresh_end_time,
    refresh_action,
    state
  FROM snowflake.account_usage.cortex_search_refresh_history(
    NAME => 'MYSVC',
    DATA_TIMESTAMP_START => dateadd(DAY, -7, current_timestamp()),
    RESULT_LIMIT => 20
  )
  WHERE refresh_trigger = 'MANUAL'
  ORDER BY data_timestamp DESC;

Copy

Analyze refresh performance for a Cortex Search service:

SELECT
    name,
    data_timestamp,
    index_preprocessing_duration,
    indexing_duration,
    (refresh_end_time::TIMESTAMP_NTZ - refresh_start_time::TIMESTAMP_NTZ) AS total_refresh_duration_sec,
    index_preprocessing_statistics:numInsertedRows AS rows_processed
  FROM snowflake.account_usage.cortex_search_refresh_history
  WHERE name = 'MYSVC'
    AND state = 'SUCCEEDED'
    AND data_timestamp >= dateadd(DAY, -30, current_date())
  ORDER BY data_timestamp DESC;

Copy