DATABASE_REFRESH_PROGRESS , DATABASE_REFRESH_PROGRESS_BY_JOB | Snowflake Documentation (original) (raw)

The DATABASE_REFRESH_PROGRESS family of functions can be used to query the status of a database refresh along various dimensions:

DATABASE_REFRESH_PROGRESS returns a JSON object indicating the current refresh status for a secondary database by name.
DATABASE_REFRESH_PROGRESS_BY_JOB returns a JSON object indicating the current refresh status for a secondary database by refresh query.

Each function is optimized for querying along the specified dimension.

Note

DATABASE_REFRESH_PROGRESS only returns the database refresh activity for the most recent database refresh if it occurred within the last 14 days.
DATABASE_REFRESH_PROGRESS_BY_JOB returns database refresh activity within the last 14 days.

Syntax¶

DATABASE_REFRESH_PROGRESS( '' )

DATABASE_REFRESH_PROGRESS_BY_JOB( '' )

_secondarydbname_

Name of the secondary database. This argument is optional if the secondary database is the active database in the current session.

Note that the entire name must be enclosed in single quotes.

_queryid_

ID of the database refresh query. The query ID can be obtained from the History page in the web interface.

Only returns results for account administrators (users with the ACCOUNTADMIN role).
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, seeSnowflake Information Schema.
Following is the list of phases in the order processed:
1. SECONDARY_UPLOADING_INVENTORY
2. PRIMARY_UPLOADING_METADATA
3. PRIMARY_UPLOADING_DATA
4. SECONDARY_DOWNLOADING_METADATA
5. SECONDARY_DOWNLOADING_DATA
6. COMPLETED / FAILED / CANCELED

The function returns the following columns:

Column Name	Data Type	Description
PHASE_NAME	TEXT	Name of the replication phases completed (or in progress) so far. For the list of phases, see the usage notes.
RESULT	TEXT	Status of the replication phase. Valid statuses are EXECUTING, SUCCEEDED, CANCELLED, FAILED.
START_TIME	NUMBER	Time when the replication phase began. Format is epoch time.
END_TIME	NUMBER	Time when the phase finished, if applicable. Format is epoch time.
DETAILS	VARIANT	Returned by the DATABASE_REFRESH_PROGRESS function only. A JSON object that provides detailed information for the following phases: . - Primary uploading data: The timestamp of the current snapshot of the primary database. . - Primary uploading data and Secondary downloading data: Total number of bytes in the database refresh as well as the number of bytes copied so far in the phase. . - Secondary downloading metadata: The number of tables, table columns, and all database objects (including tables and table columns) in the latest snapshot of the primary database.

Retrieve the current progress of the database refresh for the mydb1 database:

select * from table(information_schema.database_refresh_progress(mydb1));

Retrieve the current progress of a database refresh by query ID:

select * from table(information_schema.database_refresh_progress_by_job('012a3b45-1234-a12b-0000-1aa200012345'));