Accessing a dynamically-set variable in multiple models

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

2 Likes

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 }}

:warning: 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.

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.