Categories:: Information Schema , Table functions

REPLICATION_GROUP_LAG_HISTORY¶

Returns the replication lag history for a secondary failover group over a time window. The results show how the replication lag for the group changed over time, returned as a step function that you can plot.

The lag at a point in time is the difference between that time and the primary snapshot timestamp of the most recent completed refresh. Between refreshes, the lag increases as time passes. When a refresh completes, the lag drops because a newer snapshot becomes available.

By default (when no date-range arguments are provided), the function returns data for the last 24 hours.

See also:: REPLICATION_GROUP_REFRESH_HISTORY, REPLICATION_GROUP_REFRESH_HISTORY_ALL , REPLICATION_GROUP_REFRESH_HISTORY view

Syntax¶

REPLICATION_GROUP_LAG_HISTORY(
      REPLICATION_GROUP_NAME => '<string>'
      [ , START_TIME => <constant_expr> ]
      [ , END_TIME => <constant_expr> ] )

Arguments¶

REPLICATION_GROUP_NAME => 'string': Name of the secondary failover group to report on. The value must be a non-NULL constant and enclosed in single quotes. This argument is required.

The following arguments are optional.

START_TIME => constant_expr , END_TIME => constant_expr

The TIMESTAMP_LTZ time window (inclusive) for which to return the lag history:

If neither START_TIME nor END_TIME is specified, the default is the last 24 hours.
If START_TIME is omitted, it defaults to 24 hours before the current timestamp.
If END_TIME is omitted, it defaults to the current timestamp.

Both arguments must be constant values.

Output¶

The function returns the following columns:

Column Name	Data Type	Description
TIMESTAMP	TIMESTAMP_LTZ	Point in time for the lag measurement.
LAG_SECONDS	NUMBER	Replication lag, in seconds, at that timestamp. This is the difference between `TIMESTAMP` and `PRIMARY_SNAPSHOT_TIMESTAMP`.
PRIMARY_SNAPSHOT_TIMESTAMP	TIMESTAMP_LTZ	Primary snapshot timestamp from the most recent completed refresh in effect at that timestamp.

Usage notes¶

When no START_TIME or END_TIME arguments are provided, the function returns data for the last 24 hours. To retrieve data beyond the last 24 hours, specify the time window explicitly.
The function returns two rows for each refresh interval so that the lag history renders as a step (sawtooth) function: one row at the moment a refresh completes (the start of the step), and one row just before the next refresh, or before the window’s end time for the final interval (the end of the step). Rows are ordered by TIMESTAMP, and then by LAG_SECONDS.
Only returns rows for a secondary replication or failover group in the current account.
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 replication lag history for the last 24 hours (default) for secondary group myrg:

SELECT TIMESTAMP, LAG_SECONDS, PRIMARY_SNAPSHOT_TIMESTAMP
  FROM TABLE(
      INFORMATION_SCHEMA.REPLICATION_GROUP_LAG_HISTORY(
          REPLICATION_GROUP_NAME => 'myrg')
  );

Retrieve the replication lag history for a specific time window for secondary group myrg:

SELECT TIMESTAMP, LAG_SECONDS, PRIMARY_SNAPSHOT_TIMESTAMP
  FROM TABLE(
      INFORMATION_SCHEMA.REPLICATION_GROUP_LAG_HISTORY(
          REPLICATION_GROUP_NAME => 'myrg',
          START_TIME => '2024-01-01'::TIMESTAMP_LTZ,
          END_TIME => '2024-01-31'::TIMESTAMP_LTZ)
  );