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!
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() %}