BigQuery Create snapshot table

A BigQuery snapshot table preserves the contents of a table (called the base table) at a particular time. (i.e you can take a backup of a base table into a snapshot table) You can save a snapshot of a current table, or create a snapshot of a table as it was at any time in the past seven days. With Snapshot tables, you pay only for storage of only the partitions modified in the base table, thereby saving on cost

Create Snapshot table example

CREATE SNAPSHOT TABLE myDataset.Employee
    CLONE myDataset.Staff;

Create snapshot table with auto expiration

CREATE SNAPSHOT TABLE myDataset.Employee
    CLONE myDataset.Staff
    OPTIONS (expiration_timestamp = TIMESTAMP "2022-04-20 12:30:00.00-08:00");

Create snapshot table using time travel

    CREATE SNAPSHOT TABLE myDataset.Employee
      CLONE myDataset.Staff
      FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);

Explanation

BigQuery snapshot table has the following limitations

• Table snapshots are read-only
• A table snapshot must be in the same project as its base table.
• 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 overwrite an existing table or table snapshot when you create a table snapshot.
• You can't take a snapshot of a BigQuery view or materialized view.
• You can't take a snapshot of a table that has data in streaming buffers.
• Table snapshots are not fully supported in the Cloud Console. For best results, use the bq command-line tool

Syntax reference

{CREATE SNAPSHOT TABLE | CREATE SNAPSHOT TABLE IF NOT EXISTS}
[[snapshot_project_name.]snapshot_dataset_name.]table_snapshot_name
CLONE [[source_project_name.]source_dataset_name.]source_table_name
[FOR SYSTEM_TIME AS OF time_expression]
[OPTIONS(snapshot_option_list)]