Using variables in get_columns_in_relation dbt function

Question

I'm fairly new to macros and variables in dbt so apologize if this is really straightforward but I'm attempting the below but it's not recognizing the {{customer_dataset}}.{{table_name}} part

{%- set table_name = 'orders' -%}

{%- set customer_dataset = customer.id -%}

{%- set columns = adapter.get_columns_in_relation(`{{customer_dataset}}.{{table_name}}`) -%}

How do I do this properly if I want to dynamically loop through a bunch of tables for the get_columns_in_relation function?

Answer 1

First off, don't nest your curlies.

Second, get_columns_in_relation expects a Relation, not a string. A Relation is the thing returned by {{ ref() }} or {{ source() }}, so it's easiest to get a Relation from a model name, not a database identifier. If you want to use a table in your database that isn't a dbt model, create a source for it first.

In short, your code should probably look like this:

{%- set columns = adapter.get_columns_in_relation(source(customer_dataset, table_name)) -%}