Populate project variable from query, save static result

Help
1

The problem I’m having

I’m using a query to populate a project variable in dbt_project.yml
The query is executed every time the start_date var is referenced.

The context of why I’m trying to do this

I want to avoid multiple database hits and potentially getting a different result for each execution.
I expect the query to be executed only once per dbt run execution.

vars:
  start_date: '{{ get_start_date_from_query() }}' # macro to get the start_date

How can I set the value by executing the query only once and saving it statically?

2

Have you looked at on-run-start for this?

3

Yes, but is there a way to set a global project variable with the on-run-start hook?

4

alright, my suggestion was maybe not amazing, since the var function only has a default value function and not an update value function.

do you have to get a date value from the database or could you pass it in or use another variable?
pass it in meaning set the --vars value within the run cmd with the desired date.
or use a different variable like run_started_at ? that should be static to when the run started.

if you have to get it from the database, the on-run-start could be the macro to set the value:
update admin_schema.admin_datetime_table set needed_date_variable = '<somedate>'
and then all macro calls to fetch needed_date_variable would be the same until the on-run-start runs with the next run. Wouldn’t solve the multiple db hits, but should be a static value.

1 Like