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

user_5166548 0
Viewed 359

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!

dbt jinja2 macros sql
1 Answers

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

user_108130820
Related