Create table snapshots

This document describes how to create a snapshot of a table by using the Google Cloud console, the CREATE SNAPSHOT TABLE SQL statement, the bq cp --snapshot command, or the jobs.insert API. This document is intended for users who are familiar with BigQuery table snapshots.

Permissions and roles

This section describes the Identity and Access Management (IAM) permissions that you need to create a table snapshot, and the predefined IAM roles that grant those permissions.

Permissions

To create a table snapshot, you need the following permissions:

Roles

The predefined BigQuery roles that provide the required permissions are as follows:

Limitations

For information about table snapshot limitations, see table snapshot limitations.

In addition, table snapshot creation is subject to the following limitations, which apply to all table copy jobs:

• When you create a table snapshot, its name must adhere to the same naming rules as when you create a table.
• Table snapshot creation is subject to BigQuery limits on copy jobs.
• The table snapshot dataset must be in the same region, and under the same organization, 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 located in an EU-based dataset. You would need to make a copy of the table instead.
• The time that BigQuery takes to create table snapshots might vary significantly across different runs because the underlying storage is managed dynamically.
• When creating a table snapshot using the BigQuery CLI, the snapshot has the default encryption key of the destination dataset. When creating a table snapshot using SQL, the snapshot has the same encryption key as the source table.

Create a table snapshot

Best practice is to create a table snapshot in a different dataset from the base table. This practice allows the base table to be restored from its table snapshot even if the base table's dataset is accidentally deleted.

When you create a table snapshot, you specify the table you want to snapshot and a unique name for the table snapshot. You can optionally specify the time of the snapshot and the table snapshot's expiration.

Create a table snapshot with an expiration

You can create a snapshot of a table that expires after 24 hours by using one of the following options:

bq cp \
--snapshot \
--no_clobber \
--expiration=86400 \
TABLE_PROJECT_ID:TABLE_DATASET_NAME.TABLE_NAME \
SNAPSHOT_PROJECT_ID:SNAPSHOT_DATASET_NAME.SNAPSHOT_NAME

{
  "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"
    }
  }
}

As with tables, if an expiration is not specified, then the table snapshot expires after the default table expiration time or the dataset that contains the table snapshot.

Create a table snapshot using time travel

You can create a table snapshot of a table as it was one hour ago by using one of the following options:

bq cp \
--no_clobber \
--snapshot \
TABLE_PROJECT_ID:TABLE_DATASET_NAME.TABLE_NAME@-3600000 \
SNAPSHOT_PROJECT_ID:SNAPSHOT_DATASET_NAME.SNAPSHOT_NAME

{
  "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"
    }
  }
}

For more information about specifying a past version of a table, see Accessing historical data using time travel.

Table access control

To control access to tables in BigQuery, see Control access to resources with IAM.

When you create a table snapshot, table-level access to the table snapshot is set as follows:

• If the table snapshot overwrites an existing table, then the table-level access for the existing table is maintained. Tags aren't copied from the base table.
• If the table snapshot is a new resource, then the table-level access for the table snapshot is determined by the access policies of the dataset in which the table snapshot is created. Additionally, tags are copied from the base table to the table snapshot.

What's next

• Update a table snapshot's description, expiration date, or access policy.
• Restore a table snapshot.
• Create monthly snapshots of a table by using a service account that runs a scheduled query.