TABLE_SNAPSHOTS view

The INFORMATION_SCHEMA.TABLE_SNAPSHOTS view contains metadata about your table snapshots. For more information, see Introduction to table snapshots.

Required permissions

To query the INFORMATION_SCHEMA.TABLE_SNAPSHOTS view, you need the bigquery.tables.list Identity and Access Management (IAM) permission for the dataset. The roles/bigquery.metadataViewer predefined role includes the required permission.

For more information about BigQuery permissions, see Access control with IAM.

Schema

When you query the INFORMATION_SCHEMA.TABLE_SNAPSHOTS table, the results contain one row for each table snapshot in the specified dataset or region.

The INFORMATION_SCHEMA.TABLE_SNAPSHOTS table has the following schema. The standard table that the table snapshot was taken from is called the base table.

Column name	Data type	Value
`table_catalog`	`STRING`	The name of the project that contains the table snapshot
`table_schema`	`STRING`	The name of the dataset that contains the table snapshot
`table_name`	`STRING`	The name of the table snapshot
`base_table_catalog`	`STRING`	The name of the project that contains the base table
`base_table_schema`	`STRING`	The name of the dataset that contains the base table
`base_table_name`	`STRING`	The name of the base table
`snapshot_time`	`TIMESTAMP`	The time that the table snapshot was created

Scope and syntax

Queries against this view must include a dataset or a region qualifier. For queries with a dataset qualifier, you must have permissions for the dataset. For queries with a region qualifier, you must have permissions for the project. For more information see Syntax. The following table explains the region and resource scopes for this view:

View name	Resource scope	Region scope
[`PROJECT_ID`.]`region-REGION`.INFORMATION_SCHEMA.TABLE_SNAPSHOTS	Project level	`REGION`
[`PROJECT_ID`.]DATASET_ID.INFORMATION_SCHEMA.TABLE_SNAPSHOTS	Dataset level	Dataset location

Replace the following:

Optional: PROJECT_ID: the ID of your Google Cloud project. If not specified, the default project is used.

REGION: any dataset region name. For example, `region-us`.
DATASET_ID: the ID of your dataset. For more information, see Dataset qualifier.

Note: You must use a region qualifier to query INFORMATION_SCHEMA views. The location of the query execution must match the region of the INFORMATION_SCHEMA view.

Example

-- Returns metadata for the table snapshots in the specified dataset.
SELECT * FROM myDataset.INFORMATION_SCHEMA.TABLE_SNAPSHOTS;

-- Returns metadata for the table snapshots in the specified region.
SELECT * FROM `region-us`.INFORMATION_SCHEMA.TABLE_SNAPSHOTS;

Example

The following query retrieves metadata for the table snapshots in the mydataset dataset. In this example, it displays the table snapshot myproject.mydataset.mytablesnapshot, which was taken from the base table myproject.mydataset.mytable on May 14, 2021, at 12 PM UTC.

SELECT *
FROM
  `myproject`.mydataset.INFORMATION_SCHEMA.TABLE_SNAPSHOTS;

The result is similar to the following:

+----------------+---------------+-----------------+--------------------+-------------------+-----------------+-----------------------------+
| table_catalog  | table_schema  | table_name      | base_table_catalog | base_table_schema | base_table_name | snapshot_time               |
+----------------+---------------+-----------------+----------------------------------------------------------------------------------------+
| myproject      | mydataset     | mytablesnapshot | myProject          | mydataset         | mytable         | 2021-05-14 12:00:00.000 UTC |
+----------------+---------------+-----------------+--------------------+-------------------+-----------------+-----------------------------+