Bigquery + dbt - Running bq command from dbt


Anyone worked with load cases where you need to empty partitions and reload data, like an aggregated data or similar?

BQ doesn’t seem to support truncate in SQL, you need to delete partitions with a command line ‘bq’ command like this example:

/* Delete the partition for March 1, 2016 in a daily partitioned table named mydataset.mytable in your default project: */

bq rm --table 'mydataset.mytable$20160301

For many partitions you need to create a delete command for every day in a loop or similar. Delete partition is no cost so a much better solution than to use DML delete of course.

The problem is that dbt doesn’t seem to support it, not sure though. dbt has some “replace partition” but not sure what is happening under the hood in that case.

Is there a standard method to handle this in dbt, using some macros already available ?

Thanks in advance!

1 Like

Hello. Same use case over here. Did you find an approach?

dbt has the incremental strategy “Insert-Overwrite” option where it can refresh the whole partition and insert data accordingly. That should work for your case.