Default Table Expiration - Set to Never, Data still deleted after 60 days.

[Jeremiah_Barret]

Originally I had the default 60 day table expiration on bigquery tables due to it being default when tables are set up and not connected to a billing account (per Google).

Now that the billing account has been linked and I have updated the table expiration to "Never", the tables in my bigquery database are still getting deleted after the 60 day period. 

[ms4446 · Accepted solution]

1. • expiration is set, it will override the default table expiration for any new tables created within that dataset. To check and modify the dataset-level expiration, navigate to the Datasets page in the BigQuery console, select the dataset in question, and view the Dataset info section.

2. Partitioned Tables:

   • If you're using partitioned tables, each partition can have its own expiration setting that might be different from the main table. Managing and viewing expiration settings for partitions typically requires SQL commands or API calls, rather than direct interactions in the console.

3. Billing Account and Sandbox Limitations:

   • If you initially used BigQuery in the free tier (sandbox mode), datasets have a default 60-day expiration. Linking a billing account should lift this limitation, but ensure that you've also updated the expiration settings for your datasets and tables after linking the billing account.

4. Bug in BigQuery:

   • If you've explored all the above possibilities and the issue persists, there might be an unexpected behavior or bug in BigQuery. In such cases, it's advisable to contact Google Cloud support for further assistance.

Additional Troubleshooting Tips:

• To list all of your tables and their expiration settings, you can use the following query:

SELECT
table_name,
option_name,
option_value
FROM
`project_id.dataset_id.INFORMATION_SCHEMA.TABLE_OPTIONS`
WHERE
option_name = 'expiration_timestamp';

This will help you identify tables with specific expiration settings.

• If you suspect the issue might be with a specific expiration setting, try changing it to a longer value, such as 365 days, and monitor the behavior.

[ms4446 · Accepted solution]

1. • expiration is set, it will override the default table expiration for any new tables created within that dataset. To check and modify the dataset-level expiration, navigate to the Datasets page in the BigQuery console, select the dataset in question, and view the Dataset info section.

2. Partitioned Tables:

   • If you're using partitioned tables, each partition can have its own expiration setting that might be different from the main table. Managing and viewing expiration settings for partitions typically requires SQL commands or API calls, rather than direct interactions in the console.

3. Billing Account and Sandbox Limitations:

   • If you initially used BigQuery in the free tier (sandbox mode), datasets have a default 60-day expiration. Linking a billing account should lift this limitation, but ensure that you've also updated the expiration settings for your datasets and tables after linking the billing account.

4. Bug in BigQuery:

   • If you've explored all the above possibilities and the issue persists, there might be an unexpected behavior or bug in BigQuery. In such cases, it's advisable to contact Google Cloud support for further assistance.

Additional Troubleshooting Tips:

• To list all of your tables and their expiration settings, you can use the following query:

SELECT
table_name,
option_name,
option_value
FROM
`project_id.dataset_id.INFORMATION_SCHEMA.TABLE_OPTIONS`
WHERE
option_name = 'expiration_timestamp';

This will help you identify tables with specific expiration settings.

• If you suspect the issue might be with a specific expiration setting, try changing it to a longer value, such as 365 days, and monitor the behavior.

[rengler-noodle]

@ms4446 what SQL is necessary to remove the expiration setting from the partitioned tables? i need to set each partitioned table to never expire.

[kolban · Accepted solution]

You can change the partition expiration value for a table by using the recipes described here:

Update the partition expiration

[AndreiHero]

Hello, @Jeremiah_Barret! How did you solve that issue? I have the same with one of four projects and solutions provided in this topic don't work.