Getting Error on BigQuery table while creating snapshot

Google Cloud Data Analytics
1

Team,

I have data owner access for Bigquery, but I’m unable to create a snapshot for a specific BigQuery table containing PII data in some of its columns. When I try to create a snapshot for this table, I get the following error. Can you suggest what could be the reason for below error.

mmadasamy_0-1720090168553.png

Thanks,

Madasamy M

2 Likes
2

Hi @mmadasamy ,

Welcome to Google Cloud Community!

Based on the error that you provided, I presume you already retried the snapshot creation but the issue persists. Let’s try to break it down on what are the possible reasons why you’re unable to create a snapshot on BigQuery:

  • There’s a good chance you don’t have at least a “Writer” role on the destination dataset where you want to store the snapshot.
  • Your table snapshot might not be in the same region and the same organization as the base table as it’s one of the limitations of BigQuery snapshot.
  • Very large tables might take a long time to snapshot. The error could be due to timeouts or resource constraints during the snapshot process.

Here’s the workaround that you can do to address the error that you’re getting:

  • Create a service account with the following role/permission for BigQuery:

  • Use bigquery.admin role to grant full access to all BigQuery resources, including snapshot creation.

  • Apply the following permissions for snapshot creation:

  • bigquery.tables.get

  • bigquery.tables.getData

  • bigquery.tables.createSnapshot

  • bigquery.datasets.get

  • bigquery.jobs.create

  • Ensure that you have the proper write permission on the destination dataset where you want to store your snapshot.

  • Double check if your table snapshot is in the same region and should be under the same organization as your source table.

  • If you suspect concurrency is an issue, attempt the snapshot creation during off-peak hours when there’s less load on BigQuery resources.

Here are some limitations of a table snapshot in BigQuery:

  • You can only take a snapshot of a table’s data as it was seven days ago or more recently, due to the seven-day limit for time travel.
  • You can’t take a snapshot of an external table.
  • Table snapshots are read-only; you can’t update the data in a table snapshot unless you create a standard table from the snapshot and then update the data.
  • You can’t copy a table snapshot
  • If you snapshot a table that has data in time travel, the data in time travel is not included in the table snapshot.

I hope the above information is helpful.

2 Likes