How do I solve this nested variable problem

The problem I’m having

I want to generate 10 models, the only difference is table to join. So I want to create a macro for this

The context of why I’m trying to do this

What I’ve already tried

select * from {{ source(‘blu’, {{loan_table"}})}}

I would like to generate the result
select * from “FIDW”.“dbt_raw”.“EXT_Hushall”

You can achieve this by using a macro with a parameter for the table name. The issue in your example is with the incorrect syntax for injecting the variable. Try defining a macro like this:

{% macro get_table(table_name) %}
select * from {{ source(‘blu’, table_name) }}
{% endmacro %}

Then, in your model, call it dynamically:
{{ get_table(‘loan_table_name’) }}

If you need multiple models with different tables, you can use dbt variables in dbt_project.yml or an iteration loop in a macro to generate them dynamically.

1 Like