I am trying to create a dbt macro that can load a table from the macro's arguments. I can do it when I specify input directly, e.g.: ref('data_categories') (data_categories is a real table name). However, my problem occurs when manipulating the string to pass to ref. Below are my error function and an error during compiling time.
{% macro my_fnc(prefix=None) %}
{% set load_table %}
{% if prefix is defined %}
{{ prefix }} ~ '_data_categories'
{% else %}
'data_categories'
{% endif %}
{% endset %}
{% set sql_statement %}
select *
from {{ ref( load_table ) }}
{% endset %}
...
{% endmacro %}
My error:
Model X depends on a node named '
'data_categories'
' which was not found
Because the error message is not clear and there is a symbol ' and spaces, my thinking now is there is something wrong in {{ ref( load_table ) }} because Jinja couldn't understand the intention of load_table My naive question: What is a correct to pass my defined table to ref
Using a block set command is going to give you all sorts of issues with whitespaces, return carriages, etc. You can remove whitespace by changing your tag declarations from {% ... %} to {%- ... -%}. But in your case, I would remove the block set tag completely:
{% macro my_fnc(prefix=None) %}
{% if prefix is defined %}
{% set load_table = prefix ~ '_data_categories' %}
{% else %}
{% set load_table = 'data_categories' %}
{% endif %}
{% set sql_statement %}
select *
from {{ ref( load_table ) }}
{% endset %}
...
{% endmacro %}