BigQuery Create table with expiration options

Create table with partiiton expiration (data automatically gets removed past the expiration date) and table expiration (table is removed after the expiration period)

Create table options example

CREATE TABLE mydataset.Employee
    (
    Id INT64, 
    Name STRING,
    Join_Date DATE,
    Branch INT64
    )
    PARTITION BY Join_Date
    CLUSTER BY Branch, Id
    OPTIONS(partition_expiration_days=7);

Create table with table expiration

CREATE TABLE mydataset.Employee
    (
    Id INT64, 
    Name STRING,
    DOJ DATE,
    Branch INT64
    )
    PARTITION BY DOJ
    CLUSTER BY Branch, Id
    OPTIONS(expiration_timestamp=TIMESTAMP "2021-12-31 00:00:00");

CTAS with table expiration

CREATE TABLE myDataset.myTable_New
    PARTITION BY COLUMN1
    CLUSTER BY COLUMN2
    OPTIONS(expiration_timestamp=TIMESTAMP "2021-09-23 00:00:00")
    AS
    SELECT * FROM myDataset.Mytable;

Explanation

The table option list allows you to set table options such as a label and an expiration time. You can include multiple options using a comma-separated list.

Specify a table option list in the following format: NAME=VALUE, ...

NAME and VALUE must be one of the following combinations:

• expiration_timestamp, Example: expiration_timestamp=TIMESTAMP "2025-01-01 00:00:00 UTC"
• partition_expiration_days, Example: partition_expiration_days=7 Sets the partition expiration in days. For more information, see Set the partition expiration. By default, partitions do not expire. This property can only be set if the table is partitioned.
• require_partition_filter. Example: require_partition_filter=true. Specifies whether queries on this table must include a a predicate filter that filters on the partitioning column. For more information, see Set partition filter requirements. The default value is false. This property can only be set if the table is partitioned.
• kms_key_name. Example: kms_key_name="projects/project_id/locations/location/keyRings/keyring/cryptoKeys/key". This property is equivalent to the encryptionConfiguration.kmsKeyName table resource property.