Best way to backfill table with range of dates?


I currently have several models that take a date variable, and use it to get data from the previous week. I need to backfill these tables, and I’m trying to find the most painless way to do it. So far I have a macro that creates a date range with weekly intervals from a start date and end date. If I could run my models with each of those dates, I would be set, but I don’t want to create a dbt job and fill out the date variable 60 different times for each of the 8 models. I wanted to create a macro that builds a model using a different date in a loop, but it looks like it’s impossible to run the build command from within a macro. Anyone have insight on how to do this? I’m trying to avoid creating a separate script for each of my models just to backfill. So something like this would be ideal:

for run_date in date_range:
for model in my_models:
dbt run --select model --vars ‘{“execution_date”:run_date}’
(and insert results into appropriate table)

I’m really stumped on how to do this and any help would be appreciated

Have you considered doing it with a bash script?

I had the same thought, but I’m not sure if it will work in the dbt Cloud UI where we run our staging and prod jobs, but I’ll look into it. Thanks for the suggestion.

Have you considered using a macro?

Hello @sbrown

I am trying to do approximately the same thing. Did you solve the problem ? I would love some help on this.