ARTICLE AD BOX
I've partitioned a table (EventDate). Each day we extract data into a staging table which contains full days. What I'd like to do is overwrite partitions in the production table based on the dates available in the staging table.
I have some code which I believe works, however I feel like it might be inefficient as I'm having to loop through distinct dates, query staging table each time, and start a new import job. I also ideally want it rollback if any partition fails to overwrite.
Is there a better way to do this that I'm unaware of?
# Distinct Dates currently in staging table dates = ['2025-12-13', '2025-12-14', '2025-12-15', '2025-12-16', '2025-12-17', '2025-12-18'] # For each distinct date in staging table, create or overwrite partition in production table for date in dates: query = f"SELECT * FROM `my_staging_table` WHERE DATE(EventDate) = '{date}'" job_config = bigquery.QueryJobConfig( write_disposition = bigquery.WriteDisposition.WRITE_TRUNCATE, destination = f"my_prod_table${date.replace('-', '')}" ) job = bq_client.query(query, job_config = job_config) job.result()