BigQuery Temporary Tables - Storage & Billing

[kabkumar]

1) BigQuery Temporary tables are scoped within the session / multi statement query. Will its storage as a dataset get expired only after 24 hours of the creation, unless dropped explicitly, even though can't find with created table name after session ?

2) Such created BigQuery Temporary tables will be stored within the BigQuery Compute Project (as Anonymous Datasets until dropped or expired) and billed under Active Storage of Compute Project Billing ?

3) Since BQ Storage Billing Model is Logical by default (if unspecified during creating the dataset), these Anonymous datasets (which gets created with random names for sessions/ script jobs) will be billed under Logical Model and tagged as Active Logical Storage within the BigQuery Compute Project ?

@ms4446 
@kolban 
@nickorlove 

[nickorlove]

Hi Kabkumar,

When used within a multi-statement transaction/query, temporary tables are indeed automatically deleted after 24 hours and do incur a storage cost. You can delete these temp tables manually if preferred to minimize costs.

To learn more, check out these two links:

https://cloud.google.com/bigquery/docs/multi-statement-queries#temporary_tables

https://cloud.google.com/bigquery/docs/writing-results#temporary_and_permanent_tables 

[kabkumar]

Hi @nickorlove  / Team,
 
Thanks for the references and inputs.

BigQuery Temporary tables will be stored within the Compute Project (as Anonymous Datasets until dropped or expired) and gets billed under Active Storage of Compute Project by Logical Billing (since Logical Billing is the default model for any new Dataset) .

Was not able to get info at this level of detail. Can you please confirm on it?

[nickorlove]

Yes, that is correct. When querying across projects, the temporary table will be stored under the compute project (where the query originated from) and not the project where the data resides. Billing for the temporary table will follow the storage billing model of the compute project, which unless changed would be logical billing.

https://cloud.google.com/bigquery/docs/updating-datasets#update_storage_billing_models

[kabkumar]

Hi @nickorlove  / Team,

Thanks again for the confirmations and references.
Since Storage Billing is defined at the level of Dataset and not at (Compute) Project Level, won't these Anonymous Datasets (created within Sessions/ Multi-query from Temp Tables) always be charged as (Active) Logical Billing ?

Can't be these  anonymous or temporary dataset / cached query results stored under Physical Model without Time-Travel and Fail-Safe Bytes ?

[kolban]

I did a search in Google's internal list of customer questions and found a similar question (for my reference 294167456).  In there, support states that temp tables are created/stored with the default billing model which is currently the same as saying "logical".   The notion of "physical" storage doesn't feel like it applies to temp tables.  Remember, when you bill under the physical model, you are charged for a minimum period of time travel and fail safe.  Since the temp tables, by definition, don't last more than 24 hours, they don't have (obvious) time travel or fail safe considerations.  

You are not charged for cached results.

[kabkumar]

Thanks @kolban for the details and explanation.

[kabkumar]

Hi @kolban / @nickorlove / Team,

To be further specific on the question & details:
TEMP Tables can be created only within sessions.
1) And within this session, is there difference on how TEMP tables are stored/maintained/billed within normal query jobs & multi-statement query jobs ?

2) In the following GCP documents, it is stated that temp tables are not stored beyond Session in "Session" doc link,  whereas mentioned as will for charged till 24 hours unless dropped explicitly  in "Temporary Multi-statement Query" doc link and" Temporary / Permanent Tables" doc link

Session:
https://cloud.google.com/bigquery/docs/sessions-write-queries#use_temporary_tables_in_sessions
Temporary / Permanent Tables:
https://cloud.google.com/bigquery/docs/writing-results#temporary_and_permanent_tables
Temporary Multi-statement Query:

https://cloud.google.com/bigquery/docs/multi-statement-queries#temporary_tables

Can you please clarify if temp tables have to dropped explicitly at session end ? Will it be the same case if the session has multi-statement queries as well ?

[nickorlove]

Correct, a temporary table generated via a session does operate differently from a temporary generated from a query result or a multi-statement transaction. Where the temp table for the query result and MSTx exist for up to 24 hours, the temp table generated via a session is immediately deleted once the session ends.

You can actually observe this same behavior yourself by following this blog and trying it out: https://cloud.google.com/blog/products/data-analytics/announcing-the-ga-of-bigquery-multi-statement-...