Using Macro in the dbt modal

Help
,
1

Hi there,

{% macro manage_null_value(column_name) %}
    {% set column = column_name %}
    {%- if column == none -%}
        {% set value = 0 %}
    {%- else -%}
        {% set value = 1 %}
    {%- endif -%}
 {{ log("cool", info=True) }}
    {{ return(value) }}
{% endmacro %}

When we run the dbt models the macro is not used properly while using the dbt run

{{ macro  manage_null_value(model_name)}}

Let me know if you need any other additional information

2

Do you want to check if the value of your columns is null?

If so, you can’t do that with macros, Jinja code does not have access to the row values in the database, dbt just uses it to compile SQL code and sends the compiled code to be executed in your DW

Can you provide more details about what you want to accomplish?

1 Like
3

Hey @brunoszdl
I am trying here to making my sql transformation into macro. And also use them in the model. If I make them into macro then I will only test macro along to verify the transformation logic.

I am trying here to write macro called manage_null_value which is used to checks values if it is null then return 0 or else 1 to the respective model.

But when i look at the snowflake sql code the value is hardcoded as 1. This jinja logic is not translated in the snowflake sql

{% macro manage_null_value(column_name) %}
    {% set column = column_name %}
    {%- if column == none -%}
        {% set value = 0 %}
    {%- else -%}
        {% set value = 1 %}
    {%- endif -%}
 {{ log("cool", info=True) }}
    {{ return(value) }}
{% endmacro %}

Thank you

referring

4

Understood, your jinja code cannot access row values, so you can’t see with jinja if you have a null value.

here is what you macro is doing:

  • You pass an argument called column_name, which is a string
  • You assign this argument to the variable column
  • You check if the string you passed is none
  • Your string is not none, is a string with the name of the column, so you fall into the else case and assign value to 1
  • You return the number 1

When you call the macro in your model, it compiles it as 1

So, the jinja logic is being translated, but it not doing what you expect.

To check if you have null values in your column you should

  1. use a test
  2. if you want to store this information in a more persistent, detailed and custom way you can create another model referencing this one. Then you can use just normal SQL
  3. another option would be using dbt python models, but be sure it isn’t a much complex solution for this problem. Check if you can use the previous options
5

Hey @brunoszdl

Currently found other way to make my transformation logic in modular format by creating udf function in snowflake placing inside macro and calling them in the dbt model

{% macro handle_null_and_empty_value() %}

use database {{target.database}};

drop function if exists handle_null_and_empty_value(VARCHAR);

create or replace function handle_null_and_empty_value(column_name VARCHAR)
  returns varchar
  as
  $$
      
            iff(
                column_name is null or trim(column_name)='',
                '0',
                column_name
            ) 
         
  $$
  ;
{% endmacro %}

Thanks for your help :smiley:

1 Like
6

Hi @Sam777, awesome, I didn’t think about UDFs, but you are right, you can use them!

I would just recommend checking if it is not better to move this macro to a pre-hook or on-run-start, so you don’t have DDL statements in your model (bad practice). Then you can also drop this function with a post-hook or on-run-end.

But it is just a suggestion! If it is working this way I’m good

7

Hey @brunoszdl
Yes you are right it better to move the macro to pre-hook and drop the function in post-hook function
Thanks for the advice :smiley:

1 Like
8

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