sqlmacrosjinja2dbt

Declare var in other macro to be used in a different macro for DBT


The main idea is to have a constant variable and then have the possibility to use it in other parts of the code or macros for DBT.

Example of a macro that contains constants:

{% macro constant_vars() -%}
    
{%
set var_1 = {
 "0": ["0"],
 "1": ["1", "11", "111"]
}
%}

{%
set var_2 = {
 "2": ["2"],
 "3": ["3"]
}
%}
{%- endmacro -%}

Macro that use a constant from the previous macro:

{% macro evaluate(
    column_to_check
) -%}

CASE
    {% for map_key in var_1 -%}
    WHEN ({{column_to_check}} IN UNNEST( {{ var_1[map_key] }})) THEN
        '{{ map_key }}'
    {% endfor -%}
    ELSE
        "-1"
END
{%- endmacro -%}

SQL sentence created for DBT:

SELECT 
[..]
  evaluate(column1)
[..]
FROM
 table

DBT compiled query:

SELECT 
[..]
CASE
    WHEN (column1 IN UNNEST(["0"])) THEN
        '0'
    WHEN (column1 IN UNNEST(["1", "11", "111"])) THEN
        '1'
    
    ELSE
        "-1"
END
[..]
FROM
 table

Is it possible? Exist another way to do that?

Thanks!


Solution

  • Variables you declare using {% set my_var = ... %} are local in scope, meaning that they won't be declared in any contexts outside of where you set them.

    dbt also supports a var macro, which can act as global variables. Docs for var

    You set var in your dbt_project.yml file like this:

    vars:
      var_1:
        0: ["0"]
        1: ["1", "11", "111"]
    

    and then access the values using {{ var('var_1') }}.

    Another option is to return the variable(s) from the macro. This would work well if the values are not static/literals:

    -- in macros/get_var_1.sql
    {% macro get_var_1() %}
    {% set var_1 = {
        "0": ["0"],
        "1": ["1", "11", "111"]
    } %}
    {{ return(var_1) }}
    {% endmacro %}
    

    Then you can call the macro and assign it to a variable in your model:

    -- in models/my_model.sql
    {% set my_var = get_var_1() %}