How to Duplicate a Table in BigQuery

Duplicating a table in BigQuery is a common task when you want to back up data, create a staging table, or experiment without modifying the original dataset. Here’s a step-by-step guide on how to do it using the BigQuery web UI, SQL, and the command line.

Option 1: Use the BigQuery Web UI

1. In the Google Cloud Console, go to BigQuery.
2. In the Explorer panel, navigate to the table you want to duplicate.
3. Click the table name, then click the Copy button in the toolbar.
4. In the dialog, specify the destination dataset and destination table name.
5. Click Copy. BigQuery will create an exact copy of the table, including its schema and data.

Option 2: Use SQL to Duplicate a Table

BigQuery SQL makes it easy to duplicate a table using the CREATE TABLE AS SELECT statement:

CREATE TABLE `project_id.dataset.new_table` AS
SELECT *
FROM `project_id.dataset.original_table`;

This will create a new table with the same schema and data as the original.

Option 3: Use the bq Command Line Tool

1. Open your terminal and run the following command:

   bq cp project_id:dataset.original_table project_id:dataset.new_table

2. This command copies the table, including both data and schema, to the new table.

Best Practices

• Make sure you have write permissions on the destination dataset.
• Consider adding a date or version suffix to the new table name (e.g., table_20250507).
• If you only need the schema without data, use CREATE TABLE ... LIKE or bq mk --schema.

Summary

Duplicating a table in BigQuery is fast and flexible whether you use the UI, SQL, or CLI. It’s a handy way to back up data or experiment safely without risking your production tables.