SCHEMATA_REPLICAS view

TheINFORMATION_SCHEMA.SCHEMATA_REPLICAS view contains information about schemata replicas.

Required role

To get the permissions that you need to query theINFORMATION_SCHEMA.SCHEMATA_REPLICAS view, ask your administrator to grant you theBigQuery Data Viewer (roles/bigquery.dataViewer) IAM role on the project. For more information about granting roles, seeManage access to projects, folders, and organizations.

You might also be able to get the required permissions throughcustom roles or otherpredefined roles.

Schema

TheINFORMATION_SCHEMA.SCHEMATA_REPLICAS view contains information aboutdataset replicas.TheINFORMATION_SCHEMA.SCHEMATA_REPLICAS view has the following schema:
ColumnTypeDescription
catalog_nameSTRINGThe project ID of the project that contains the dataset.
schema_nameSTRINGThe dataset ID of the dataset.
replica_nameSTRINGThe name of the replica.
locationSTRINGThe region or multi-region the replica was created in.
replica_primary_assignedBOOLIf the value isTRUE, the replica has the primary assignment.
replica_primary_assignment_completeBOOLIf the value isTRUE, the primary assignment is complete. If the value isFALSE, the replica is not (yet) the primary replica, even ifreplica_primary_assigned equalsTRUE.
creation_timeTIMESTAMPThe replica's creation time. When the replica is first created, it is not fully synced with the primary replica untilcreation_complete equalsTRUE. The value ofcreation_time is set beforecreation_complete equalsTRUE.
creation_completeBOOLIf the value isTRUE, the initial full sync of the primary replica to the secondary replica is complete.
replication_timeTIMESTAMP

The value forreplication_time indicates the staleness of the dataset.

Some tables in the replica might be ahead of this timestamp. This value is only visible in the secondary region.

If the dataset contains a table with streaming data, the value ofreplication_time will not be accurate.

sync_statusJSONThe status of the sync between the primary and secondary replicas forcross-region replication anddisaster recovery datasets. ReturnsNULL if the replica is a primary replica or the dataset doesn't use replication.
replica_primary_assignment_timeTIMESTAMPThe time at which the primary switch to the replica was triggered.
replica_primary_assignment_completion_timeTIMESTAMPThe time at which the primary switch to the replica was completed.

For stability, we recommend that you explicitly list columns in your information schema queries instead ofusing a wildcard (SELECT *). Explicitly listing columns prevents queries frombreaking if the underlying schema changes.

Scope and syntax

Queries against this view must include aregion qualifier.The following table explains the region scope for this view:

View nameResource scopeRegion scope
[PROJECT_ID.]`region-REGION`.INFORMATION_SCHEMA.SCHEMATA_REPLICAS[_BY_PROJECT]Project levelREGION
Replace the following:
  • Optional:PROJECT_ID: the ID of your Google Cloud project. If not specified, the default project is used.
  • REGION: anydataset region name. For example,`region-us`.

    Note: You must usea region qualifier to queryINFORMATION_SCHEMA views. The location of the query execution must match the region of theINFORMATION_SCHEMA view.

Examples

This section lists example queries of theINFORMATION_SCHEMA.SCHEMATA_REPLICAS view.

Example: List all replicated datasets in a region

The following example lists all the replicated datasets in theUS region:

SELECT*FROM`region-us`.INFORMATION_SCHEMA.SCHEMATA_REPLICAS;

The result is similar to the following:

+---------------------+-------------------+--------------+----------+--------------------------+-------------------------------------+---------------------+-------------------+------------------+|    catalog_name     |    schema_name    | replica_name | location | replica_primary_assigned | replica_primary_assignment_complete |    creation_time    | creation_complete | replication_time |+---------------------+-------------------+--------------+----------+--------------------------+-------------------------------------+---------------------+-------------------+------------------+| myproject           | replica1          | us-east7     | us-east7 |                     true |                                true | 2023-04-17 20:42:45 |              true |             NULL || myproject           | replica1          | us-east4     | us-east4 |                    false |                               false | 2023-04-17 20:44:26 |              true |             NULL |+---------------------+-------------------+--------------+----------+--------------------------+-------------------------------------+---------------------+-------------------+------------------+

Example: List replicated datasets and the primary replica for each

The following example lists all replicated datasets and their primary replica in theUS region:

SELECTcatalog_name,schema_name,replica_nameASprimary_replica_name,locationASprimary_replica_location,replica_primary_assignment_completeASis_primary,FROM`region-us`.INFORMATION_SCHEMA.SCHEMATA_REPLICASWHEREreplica_primary_assignment_complete=TRUEANDreplica_primary_assigned=TRUE;

The result is similar to the following:

+---------------------+-------------+----------------------+--------------------------+------------+|    catalog_name     | schema_name | primary_replica_name | primary_replica_location | is_primary |+---------------------+-------------+----------------------+--------------------------+------------+| myproject           | my_schema1  | us-east4             | us-east4                 |       true || myproject           | my_schema2  | us                   | US                       |       true || myproject           | my_schema2  | us                   | US                       |       true |+---------------------+-------------+----------------------+--------------------------+------------+

Example: List replicated datasets and their replica states

The following example lists all replicated datasets and their replica states:

SELECTcatalog_name,schema_name,replica_name,CASEWHEN(replica_primary_assignment_complete=TRUEANDreplica_primary_assigned=TRUE)THEN'PRIMARY'WHEN(replica_primary_assignment_complete=FALSEANDreplica_primary_assigned=FALSE)THEN'SECONDARY'ELSE'PENDING'ENDASreplica_state,FROM`region-us`.INFORMATION_SCHEMA.SCHEMATA_REPLICAS;

The result is similar to the following:

+---------------------+-------------+--------------+---------------+|    catalog_name     | schema_name | replica_name | replica_state |+---------------------+-------------+--------------+---------------+| myproject           | my_schema1  | us-east4     | PRIMARY       || myproject           | my_schema1  | my_replica   | SECONDARY     |+---------------------+-------------+--------------+---------------+

Example: List when each replica was created and whether the initial backfill iscomplete

The following example lists all replicas and when that replica was created. When a secondary replica is created, its data is not fully synced with the primary replica untilcreation_complete equalsTRUE.

SELECTcatalog_name,schema_name,replica_name,creation_timeAScreation_time,FROM`region-us`.INFORMATION_SCHEMA.SCHEMATA_REPLICASWHEREcreation_complete=TRUE;

The result is similar to the following:

+---------------------+-------------+--------------+---------------------+|    catalog_name     | schema_name | replica_name |    creation_time    |+---------------------+-------------+--------------+---------------------+| myproject           | my_schema1  | us-east4     | 2023-06-15 00:09:11 || myproject           | my_schema2  | us           | 2023-06-15 00:19:27 || myproject           | my_schema2  | my_replica2  | 2023-06-15 00:19:50 || myproject           | my_schema1  | my_replica   | 2023-06-15 00:16:19 |+---------------------+-------------+--------------+---------------------+

Example: Show the most recent synced time

The following example shows the most recent timestamp when the secondary replicacaught up with the primary replica.

You must run this query in the region that contains the secondary replica. Sometables in the dataset might be ahead of the reported replication time.

SELECTcatalog_name,schema_name,replica_name,-- Calculate the replication lag in seconds.TIMESTAMP_DIFF(CURRENT_TIMESTAMP(),replication_time,SECOND)ASreplication_lag_seconds,-- RLS-- Calculate the replication lag in minutes.TIMESTAMP_DIFF(CURRENT_TIMESTAMP(),replication_time,MINUTE)ASreplication_lag_minutes,-- RLM-- Show the last sync time for easier interpretation.replication_timeASsecondary_replica_fully_synced_as_of_time,FROM`region-us`.INFORMATION_SCHEMA.SCHEMATA_REPLICAS

The result is similar to the following:

+---------------------+-------------+--------------+-----+-----+-------------------------------------------+|    catalog_name     | schema_name | replica_name | rls | rlm | secondary_replica_fully_synced_as_of_time |+---------------------+-------------+--------------+-----+-----+-------------------------------------------+| myproject           | my_schema1  | us-east4     |  23 |   0 |                       2023-06-15 00:18:49 || myproject           | my_schema2  | us           |  67 |   1 |                       2023-06-15 00:22:49 || myproject           | my_schema1  | my_replica   |  11 |   0 |                       2023-06-15 00:28:49 || myproject           | my_schema2  | my_replica2  | 125 |   2 |                       2023-06-15 00:29:20 |+---------------------+-------------+--------------+-----+-----+-------------------------------------------+

A value ofNULL indicates that the secondary replica was never fully synced tothe primary replica.

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-12-15 UTC.