Bigquery Schedule query to load data to a particular partition

Question

I am using the bigquery schedule query functionality to run a query every 30 mins. My destination table will be a partitioned table and the partionining column is 'event_date'

The schedule query that i am using will be to copy today's data from source_table -> Dest_table (like select * from source_table where event_date = CURRENT_DATE()) every 30 mins , but i would like it to write_truncate existing partition without write truncating the whole table.(since i don't want to duplicate today's data every 30 mins)

Currently when i schedule this query with partition_field set to event_date and write_truncate , it is truncating the whole table and this causes the previous data to be lost . Is there something else that i am missing

Yun Zhang Yun Zhang · Accepted Answer · 2020-01-24T08:56:10

Instead of specifying destination table, you may use MERGE to truncate only one partition.

It is unfortunately more expensive, for you also pay for deleting the data from dest_table. (Insert is still free)

MERGE dest_table t
USING source_table
ON FALSE
WHEN NOT MATCHED BY SOURCE AND event_date=CURRENT_DATE() THEN DELETE
WHEN NOT MATCHED BY TARGET THEN INSERT ROW

Bigquery Schedule query to load data to a particular partition

1 Answers