# Using Macro in the dbt modal

**URL:** <https://discourse.getdbt.com/t/using-macro-in-the-dbt-modal/8721>\
**Category:** Help\
**Tags:** jinja, dbt-core\
**Created:** [June 15, 2023, 1:54pm UTC](https://discourse.getdbt.com/t/using-macro-in-the-dbt-modal/8721 "2023-06-15T13:54:28Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![Sam777](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/sam777/32/5137_2.png) [@Sam777](https://discourse.getdbt.com/u/Sam777)\
**Post date:** [June 15, 2023, 1:54pm UTC](https://discourse.getdbt.com/t/using-macro-in-the-dbt-modal/8721/1 "2023-06-15T13:54:28Z")

</div>

Hi there,

```auto
{% 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**

```auto
{{ macro manage_null_value(model_name)}}

```

Let me know if you need any other additional information

---

<div class="post-metadata">

**Author:** ![brunoszdl](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/brunoszdl/32/4113_2.png) [@brunoszdl](https://discourse.getdbt.com/u/brunoszdl)\
**Post date:** [June 15, 2023, 8:57pm UTC](https://discourse.getdbt.com/t/using-macro-in-the-dbt-modal/8721/2 "2023-06-15T20:57:10Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Sam777](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/sam777/32/5137_2.png) [@Sam777](https://discourse.getdbt.com/u/Sam777)\
**Post date:** [June 16, 2023, 4:42am UTC](https://discourse.getdbt.com/t/using-macro-in-the-dbt-modal/8721/3 "2023-06-16T04:42:39Z")

</div>

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

```auto
{% 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

> **[An introduction to unit testing your dbt Packages | dbt Developer Blog](https://docs.getdbt.com/blog/unit-testing-dbt-packages)**
>
> Traditionally, integration tests have been the primary strategy for testing dbt Packages. In this post, Yu Ishikawa walks us through adding in unit testing as well.

---

<div class="post-metadata">

**Author:** ![brunoszdl](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/brunoszdl/32/4113_2.png) [@brunoszdl](https://discourse.getdbt.com/u/brunoszdl)\
**Post date:** [June 16, 2023, 1:36pm UTC](https://discourse.getdbt.com/t/using-macro-in-the-dbt-modal/8721/4 "2023-06-16T13:36:05Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Sam777](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/sam777/32/5137_2.png) [@Sam777](https://discourse.getdbt.com/u/Sam777)\
**Post date:** [June 16, 2023, 1:45pm UTC](https://discourse.getdbt.com/t/using-macro-in-the-dbt-modal/8721/5 "2023-06-16T13:45:50Z")

</div>

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

```auto
{% 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 😃

---

<div class="post-metadata">

**Author:** ![brunoszdl](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/brunoszdl/32/4113_2.png) [@brunoszdl](https://discourse.getdbt.com/u/brunoszdl)\
**Post date:** [June 16, 2023, 5:05pm UTC](https://discourse.getdbt.com/t/using-macro-in-the-dbt-modal/8721/6 "2023-06-16T17:05:20Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Sam777](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/sam777/32/5137_2.png) [@Sam777](https://discourse.getdbt.com/u/Sam777)\
**Post date:** [June 16, 2023, 5:57pm UTC](https://discourse.getdbt.com/t/using-macro-in-the-dbt-modal/8721/7 "2023-06-16T17:57:50Z")

</div>

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 😃

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/1X/a7a7ca1fe379aaf90952b0e13118a817babcd14f.png) [@system](https://discourse.getdbt.com/u/system)\
**Post date:** [June 23, 2023, 5:58pm UTC](https://discourse.getdbt.com/t/using-macro-in-the-dbt-modal/8721/8 "2023-06-23T17:58:38Z")

</div>

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