Creating a partitioned version of a BigQuery table scheduled for daily updates

Question

I am faced with the following situation: among the BigQuery datasets which I am handling there is a rather large table - let us call it lt - that undergoes daily updates (more specifically, this table is set as the destination of the result set of a certain SQL query scheduled to run daily). The original table lt is not partitioned and for purposes of improving query efficiency I would like to either apply a partition to it or to create a new partitioned version of it.

There is only one approach I can think of to this end, namely that of running the code below:

CREATE TABLE `data_project.dataset.lt_part`(lt_column_1 type_1,...lt_column_n type_n)
PARTITION BY date_column # this will of course be one of the columns comprised in the above listing
AS (
  SELECT *
  FROM `data_project.dataset.lt`
)

however I believe that this approach would only create a partitioned snapshot of the original lt table - the one corresponding to the day when the above code is run - and not a table that dynamically updates to keep up with the daily updates of the original lt.

Thus, my question is what methods would I have available in order to create a daily updating, partitioned version of the original lt? Thank you for your time and attention.

Answer 1

In BigQuery you cannot add partition to an existing table.

In this case you only option is to create a new table from the original one data_project.dataset.lt => data_project.dataset.lt_part using the query you wrote and then update you daily schedule to point a new destination, the data_project.dataset.lt_part.

After this you can delete the old table data_project.dataset.lt.