I am facing issue when trying to call a macro as a parameter and then passing it to a variable. Can anyone guide me where I am going wrong??

Help
1

Wrong_code

In the correct_code file, I am just hard coding the macro name under sql_text.

But in the wrong_code file, I am trying to pass the macro name to a variable and then using the variable name with the select command under sql_text block. So it should ne read as a macro (which is : select {{poc1_macro()) instead it is reading the whole thing as a variable itself and throwing an error:

syntax error line 1 at position 8 unexpected '{'.
  syntax error line 1 at position 19 unexpected '('.
2

Hey @ashishk9, let me check I’m understanding you correctly:
You want to dynamically call a macro, by taking the name of a macro and surrounding it with curly braces (and parentheses for an empty function signature)?

This won’t work because dbt only goes through one rendering pass - as you’ve seen, the compiled result will just be the string {{poc1_macro()}} instead of whatever poc1_macro does.

Can you explain why you’re trying to do this? There might be a different approach you can take.

3

Hi @joellabes, I have three macros one which inserts data, one updates the table, and the last one is the main macro where I want to pass any of the two macros name as a parameter at a time. So that it performs only task which is insert or update.

4

dbt itself takes responsibility for doing inserts and updates, you shouldn’t need to be writing macros to be executing DDL etc.

Have you gone through the getting started tutorial or read the dbt introduction? It may be helpful to get an indication of what dbt can do for you

5

I have gone through dbt fundamentals. My requirement here is to see whether I can call a macro in another macro or not? insert or update is just an option not my end result.

But when I try to call the macro as parameter, it reads that as a variable.

6

You can call macros from other macros, but it can’t be totally dynamic.

For example you could do

{% macro macro_1() %}
  {{ macro_2('hello') }}
{% endmacro %}

# in a separate file 
{% macro macro_2(msg) %}
  {{ msg }}
{% endmacro %}

and call macro_1 from a sql file. But you can’t set which macro you want to call in a variable or pass that in as another argument.

That’s why I want to know your actual goal, so we can find an approach that does work.

7

Option-1:

U create a macro and pass macro name to it and call the macro using if else


{% macro macro_m1(macro_name) -%}
    {% if macro_name == 'table_1_macro' %}
        {{ table_1_macro() }}
    {% elif macro_name == 'table_2_macro' %}
        {{table_2_macro()}}
    {% endif %}
{%- endmacro %}

{% macro table_1_macro() -%}

{{print("inside table_1 macro")}}

{%- endmacro %}

{% macro table_2_macro() -%}

{{print("inside table_2 macro")}}

{%- endmacro %}

Option-2

I think u can also use adapter.dispatch to dynamically call a macro

2 Likes
8

adapter.dispatch still requires the specific macro name to be specified in advance through a if/else block like your first example, i.e. you can’t do

{% set macro_name = 'my_macro' %}
{{ adapter.dispatch(macro_name)() }}

to call my_macro().

1 Like
9

I am learning about sources in dbt. My understanding the sources define raw table location like database, schema, table name and so on, so we don’t hardcode the table name in the models (*.sql) . In the model I still define

Note: @Ram originally posted this reply in Slack. It might not have transferred perfectly.