Create table snapshots
This document describes how to create a snapshot of a table by using theGoogle Cloud console, theCREATE SNAPSHOT TABLESQL statement, thebq cp --snapshot command,or thejobs.insert API. Thisdocument is intended for users who are familiar with BigQuerytable snapshots.
Permissions and roles
This section describes theIdentity and Access Management (IAM) permissionsthat you need to create a table snapshot, and thepredefined IAM rolesthat grant those permissions.
Permissions
To create a table snapshot, you need the following permissions:
| Permission | Resource | Notes |
|---|---|---|
All of the following:bigquery.tables.getbigquery.tables.getDatabigquery.tables.createSnapshotbigquery.datasets.getbigquery.jobs.create | The table that you want to snapshot. | Because snapshot expiration deletes the snapshot at a later time, to create a snapshot with an expiration time you must have thebigquery.tables.deleteSnapshot permission. |
bigquery.tables.createbigquery.tables.updateData | The dataset that contains the table snapshot. |
Roles
The predefined BigQuery roles that provide the requiredpermissions are as follows:
| Role | Resource | Notes |
|---|---|---|
At least one of the following:bigquery.dataViewerbigquery.dataEditorbigquery.dataOwnerAnd at least one of the following: bigquery.jobUserbigquery.studioUserbigquery.userbigquery.studioAdminbigquery.admin | The table that you want to snapshot. | Onlybigquery.dataOwner,bigquery.admin, andbigquery.studioAdmin can be used for creating a snapshot with anexpiration time. |
At least one of the following:bigquery.dataEditorbigquery.dataOwnerbigquery.studioAdminbigquery.admin | The dataset that contains the new table snapshot. |
Limitations
For information about table snapshot limitations, seetable snapshot limitations.
In addition, table snapshot creation is subject to the following limitations,which apply to alltable copy jobs:
- When you create a table snapshot, its name must adhere to thesamenaming rules as when youcreate a table.
- Table snapshot creation is subject to BigQuerylimits on copy jobs.
- The table snapshot dataset must be in the sameregion, and under the sameorganization,as the dataset that contains the table you are taking a snapshot of. For example,you cannot create a table snapshot in a US-based dataset of a table locatedin an EU-based dataset. You would need to make a copy of the table instead.
- The time that BigQuery takes to create table snapshots mightvarysignificantly across different runs because the underlying storage is manageddynamically.
- When creating a table snapshot using the BigQuery CLI, the snapshot has thedefault encryption key of the destination dataset. When creating a tablesnapshot using SQL, the snapshot has the same encryption key as thesource table.
Create a table snapshot
Best practice is to create a table snapshot in a different dataset from thebase table. This practice allows the base table to berestored from itstable snapshot even if the base table's dataset is accidentally deleted.
When you create a table snapshot, you specify the table youwant to snapshot and a unique name for the table snapshot. You can optionallyspecify thetime of the snapshot and the tablesnapshot'sexpiration.
Create a table snapshot with an expiration
You can create a snapshot of a table that expires after 24 hours by usingone of the following options:
Console
In the Google Cloud console, go to theBigQuery page.
In the left pane, clickExplorer:
If you don't see the left pane, clickExpand left pane to open the pane.
In theExplorer pane, expand your project, clickDatasets, andthen click the dataset.
ClickOverview> Tables, and then click the name of thetable that you want to snapshot.
In the details pane that appears, clickSnapshot.
In theCreate table snapshot pane that appears, enter theProject,Dataset, andTable information for the new table snapshot.
In theExpiration time field, enter the date and time for 24 hoursfrom now.
ClickSave.
SQL
Use theCREATE SNAPSHOT TABLE DDL statement:
In the Google Cloud console, go to theBigQuery page.
In the query editor, enter the following statement:
CREATESNAPSHOTTABLESNAPSHOT_PROJECT_ID.SNAPSHOT_DATASET_NAME.SNAPSHOT_NAMECLONETABLE_PROJECT_ID.TABLE_DATASET_NAME.TABLE_NAMEOPTIONS(expiration_timestamp=TIMESTAMP'TIMESTAMP_VALUE');
Replace the following:
SNAPSHOT_PROJECT_ID: the project ID of the project in which to create the snapshot.SNAPSHOT_DATASET_NAME: the name of the dataset in which to create the snapshot.SNAPSHOT_NAME: the name of the snapshot you are creating.TABLE_PROJECT_ID: the project ID of the project that contains the table you are creating the snapshot from.TABLE_DATASET_NAME: the name of the dataset that contains the table you are creating the snapshot from.TABLE_NAME: the name of the table you are creating the snapshot from.TIMESTAMP_VALUE: Atimestamp value representing the date and time 24 hours from now.
ClickRun.
For more information about how to run queries, seeRun an interactive query.
Note: The snapshot inherits the source table's encryption key.bq
Enter the following command in the Cloud Shell:
bqcp\--snapshot\--no_clobber\--expiration=86400\TABLE_PROJECT_ID:TABLE_DATASET_NAME.TABLE_NAME\SNAPSHOT_PROJECT_ID:SNAPSHOT_DATASET_NAME.SNAPSHOT_NAME
Replace the following:
TABLE_PROJECT_ID: the project ID of the project that contains the table you are creating the snapshot from.TABLE_DATASET_NAME: the name of the dataset that contains the table you are creating the snapshot from.TABLE_NAME: the name of the table you are creating the snapshot from.SNAPSHOT_PROJECT_ID: the project ID of the project in which to create the snapshot.SNAPSHOT_DATASET_NAME: the name of the dataset in which to create the snapshot.SNAPSHOT_NAME: the name of the snapshot you are creating.
The--no_clobber flag is required.
API
Call thejobs.insert method withthe following parameters:
| Parameter | Value |
|---|---|
projectId | The project ID of the project to bill for this operation. |
| Request body | {"configuration":{"copy":{"sourceTables":[{"projectId":"TABLE_PROJECT_ID","datasetId":"TABLE_DATASET_NAME","tableId":"TABLE_NAME"}],"destinationTable":{"projectId":"SNAPSHOT_PROJECT_ID","datasetId":"SNAPSHOT_DATASET_NAME","tableId":"SNAPSHOT_NAME"},"operationType":"SNAPSHOT","writeDisposition":"WRITE_EMPTY","destinationExpirationTime":"TIMESTAMP_VALUE"}}} |
Replace the following:
TABLE_PROJECT_ID: the project ID of the project that contains the table you are creating the snapshot from.TABLE_DATASET_NAME: the name of the dataset that contains the table you are creating the snapshot from.TABLE_NAME: the name of the table you are creating the snapshot from.SNAPSHOT_PROJECT_ID: the project ID of the project in which to create the snapshot.SNAPSHOT_DATASET_NAME: the name of the dataset in which to create the snapshot.SNAPSHOT_NAME: the name of the snapshot you are creating.TIMESTAMP_VALUE: Atimestamp value representing the date and time 24 hours from now.
As with tables, if an expiration is not specified, then the table snapshotexpires after thedefault table expiration timeor the dataset that contains the table snapshot.
Note: Because expiring a snapshot is the same as deleting it at a later time,creating a snapshot with an expiration time requires thebigquery.tables.deleteSnapshot permission.Create a table snapshot using time travel
You can create a table snapshot of a table as it was one hour ago by usingone of the following options:
Console
In the Google Cloud console, go to theBigQuery page.
In the left pane, clickExplorer:
In theExplorer pane, expand your project, clickDatasets, andthen click the dataset.
ClickOverview> Tables, and then click the name of thetable that you want to snapshot.
In the details pane that appears, clickSnapshot.
In theCreate table snapshot pane that appears, enter theProject,Dataset, andTable information for the new table snapshot.
In theSnapshot time field, enter the date and time for 1 hour ago.
ClickSave.
SQL
Use theCREATE SNAPSHOT TABLE DDL statementwith aFOR SYSTEM_TIME AS OF clause:
In the Google Cloud console, go to theBigQuery page.
In the query editor, enter the following statement:
CREATESNAPSHOTTABLESNAPSHOT_PROJECT_ID.SNAPSHOT_DATASET_NAME.SNAPSHOT_NAMECLONETABLE_PROJECT_ID.TABLE_DATASET_NAME.TABLE_NAMEFORSYSTEM_TIMEASOFTIMESTAMP_SUB(CURRENT_TIMESTAMP(),INTERVAL1HOUR);
Replace the following:
SNAPSHOT_PROJECT_ID: the project ID of the project in which to create the snapshot.SNAPSHOT_DATASET_NAME: the name of the dataset in which to create the snapshot.SNAPSHOT_NAME: the name of the snapshot you are creating.TABLE_PROJECT_ID: the project ID of the project that contains the table you are creating the snapshot from.TABLE_DATASET_NAME: the name of the dataset that contains the table you are creating the snapshot from.TABLE_NAME: the name of the table you are creating the snapshot from.
ClickRun.
For more information about how to run queries, seeRun an interactive query.
bq
Enter the following command in the Cloud Shell:
bqcp\--no_clobber\--snapshot\TABLE_PROJECT_ID:TABLE_DATASET_NAME.TABLE_NAME@-3600000\SNAPSHOT_PROJECT_ID:SNAPSHOT_DATASET_NAME.SNAPSHOT_NAME
Replace the following:
TABLE_PROJECT_ID: the project ID of the project that contains the table you are creating the snapshot from.TABLE_DATASET_NAME: the name of the dataset that contains the table you are creating the snapshot from.TABLE_NAME: the name of the table you are creating the snapshot from.SNAPSHOT_PROJECT_ID: the project ID of the project in which to create the snapshot.SNAPSHOT_DATASET_NAME: the name of the dataset in which to create the snapshot.SNAPSHOT_NAME: the name of the snapshot you are creating.
The--no_clobber flag is required.
API
Call thejobs.insertmethod with the following parameters:
| Parameter | Value |
|---|---|
projectId | The project ID of the project to bill for this operation. |
| Request body | {"configuration":{"copy":{"sourceTables":[{"projectId":"TABLE_PROJECT_ID","datasetId":"TABLE_DATASET_NAME","tableId":"TABLE_NAME@-360000"}],"destinationTable":{"projectId":"SNAPSHOT_PROJECT_ID","datasetId":"SNAPSHOT_DATASET_NAME","tableId":"SNAPSHOT_NAME"},"operationType":"SNAPSHOT","writeDisposition":"WRITE_EMPTY"}}} |
Replace the following:
TABLE_PROJECT_ID: the project ID of the project that contains the table you are creating the snapshot from.TABLE_DATASET_NAME: the name of the dataset that contains the table you are creating the snapshot from.TABLE_NAME: the name of the table you are creating the snapshot from.SNAPSHOT_PROJECT_ID: the project ID of the project in which to create the snapshot.SNAPSHOT_DATASET_NAME: the name of the dataset in which to create the snapshot.SNAPSHOT_NAME: the name of the snapshot you are creating.
For more information about specifying a past version of a table, seeAccessing historical data using time travel.
Table access control
To control access to tables in BigQuery, seeControl access to resources with IAM.
When you create a table snapshot,table-level access to the tablesnapshot is set as follows:
- If the table snapshot overwrites an existing table, then the table-levelaccess for the existing table is maintained.Tagsaren't copied from the base table.
- If the table snapshot is a new resource, then the table-level access for thetable snapshot is determined by the access policies of the dataset in whichthe table snapshot is created. Additionally,tags arecopied from the base table to the table snapshot.
What's next
- Update a table snapshot's description, expiration date, oraccess policy.
- Restore a table snapshot.
- Create monthly snapshots of a table by using a service account that runs a scheduled query.
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.