Accessing a dynamically-set variable in multiple models

[daniele.frigo]

I know I can execute a query in a model and assign the result to a variable in this way:

{% if execute %}
{% set last_audit_id = run_query("SELECT LAST_AUDIT_ID FROM CFG.PAR_SCDM_AUDIT WHERE FLOW = ‘ORDER_MOVEMENT’ ").columns[0][0] %}
{% endif %}

but this variable is local to this model.
Is there a way to do the same thing in dbt_project.yml and thus feed a project-wide variable?
Thanks
Daniele

[joellabes]

You can’t dynamically set a global/project-wide variable, but if you need to use the same value in multiple locations you could extract it into a macro:

{% macro get_last_audit_id() %}
  {% if execute %}
    {% set last_audit_id = run_query("SELECT LAST_AUDIT_ID FROM CFG.PAR_SCDM_AUDIT WHERE FLOW = ‘ORDER_MOVEMENT’ ").columns[0][0] %}
  {% else %}  
    {% set last_audit_id = -1 %}
  {% endif %}

  {% do return(last_audit_id) %}
{% endmacro %}

And then you can call it from multiple locations

-- model_1.sql
{% set last_audit_id = get_last_audit_id() %}
select * from {{ ref('some_table') }}
where id > {{ last_audit_id }}

-- model_2.sql
{% set last_audit_id = get_last_audit_id() %}
select * from {{ ref('another_table') }}
where id > {{ last_audit_id }}

Note that this won’t be a static value across all invocations in a run. If you need that to happen, you might have to change your macro to write to some sort of temporary table, and then have the macro read from that.