How to Pass Column Values to DBT Macro When Column Names Have '.' Characters

Question

I have a simple macro

{% macro parse_url_string_format(input_value) %}
  {% if 'utm' in input_value %}
    {{ input_value }}
  {% else %}
    ''
  {% endif %}
{% endmacro %}

I want to use it in the following way:

select {{parse_url_string_format(col_a)}} as parsed_a
from table_1

Okay, that works fine. However, the name of my column is actually my.column.a... it has a bunch of . characters in it. No worries; the usual way to handle this in a select statement is merely to surround the column name with " characters. But, when I do that:

select {{parse_url_string_format(my.column.a)}} as parsed_a
from table_1

Then dbt actually sends the string 'my.column.a into the macro, and not the value of the column. What is the syntax to send the value of the column?

Answer 1

Assuming you are in a pg-compatible dialect, and use double quotes for identifiers, e.g., "my.column.a"...

You can either nest quotes in the call to the macro:

select {{parse_url_string_format('"my.column.a"')}} as parsed_a

or you can rewrite the macro to quote the identifier:

{% macro parse_url_string_format(input_value) %}
  {% if 'utm' in input_value %}
    "{{ input_value }}"
  {% else %}
    ''
  {% endif %}
{% endmacro %}

Keep in mind that the DATA from your database does NOT pass through your macro; in this case the input_value for your macro is a string that represents the name of a column; accordingly, when you write {% if 'utm' in input_value %}, you mean "if 'utm' is in the NAME OF THE COLUMN stored in the variable input_value", not "if 'utm' is in data stored in the column named input_value".

For MySQL or BQ, just replace the double quotes above with backicks (`).