Create a Sql Statement IN DBT macro and use the query in the dbt model

Question

my_macro.sql

{macro my_macro(value)}

{% set query  %}

select A from value

{{ return(query) }}

{% endset %}

{% endmacro %}

model.sql

select * from B
where A in ({{ my_macro(value) }}

when i compile the code I get the result as

output:

select * from B
Where A in (     )

I'm not getting the sql statement as output of the macro

I know that we can execute the query and get the results can we pass the query as string ?

Is this actually possible in DBT?.

Answer 1

You almost get it. Just three things:

1. Move {% endset %} before return
2. Change value in {{ value }}
3. Try to avoid hardcoding table references and use either source() or ref()

{macro my_macro(value)}

{% set query  %}

select A from {{ value }}

{% endset %}

{{ return(query) }}

{% endmacro %}