Search code examples
jinja2dbt

Using a string as an argument in a Jinja macro in dbt

I want to create a dbt macro to simplify the following lines

COALESCE(LOWER(tags::TEXT) ~ '.*my-first-query.*', FALSE),
COALESCE(LOWER(tags::TEXT) ~ '.*my-second-query.*', FALSE),
COALESCE(LOWER(tags::TEXT) ~ '.*my-other-query.*', FALSE)

I would like to convert the computation to a function such that I can convert the lines to

 {{ extract_clean_tag(my-first-query) }},
 {{ extract_clean_tag(my-second-query) }},
 {{ extract_clean_tag(my-other-query) }}

How can I write this macro in dbt? I'm having issues passing strings as arguments to the functions.

So far, I've tried something like

{% macro extract_clean_tag(tag_regex) %}

    COALESCE(LOWER(tags::TEXT) ~ '.*{{ tag_regex }}.*', FALSE)

{% endmacro %}

And calling it via extract_clean_tag(my-first-query), but dbt returns:

column "my-first-query" does not exist
Solution

  • You have to call it with 'my-first-query' as an argument, like this:

    {{ extract_clean_tag('my-first-query') }}

    Without the quotes, the Jinja parser is looking for a variable named my-first-query, whereas the quotes denote that you're passing a string.

    See also here: https://docs.getdbt.com/docs/building-a-dbt-project/jinja-macros/#macros (the cents_to_dollars example)