Select columns from another query in DBT cloud

Question

In DBT, I have a table in which each row is a field, i.e. something like this

field_id | field_name
---------------------
1        | id
2        | name
3        | last_name
4        | birthdate
etc.

I want to get those fields and then select them from another table which has the following structure.

id     |   name   |    last_name    |   birthdate   |  email            |  created_at
---------------------------------------------------------------------------------------
1      |  Bruce   |   Wayne         |   01-01-1980  | batman@gmail.com  |  03-12-1999
2      |  Clark   |   Kent          |   02-02-1981  | super@gmail.com   |  06-01-1995

So, basically, select only certain fields programatically. Is there a way to do this in DBT?

I tried the star macro but I couldn't make it work.

with fields as select field_name from {{ref('fields_table')}}
select {{ dbt_utils.star(from=ref('fields')) }} from {{ref('superheroes')}}

Answer 1

There's probably a better way, but one possible solution is to split this into 2 models. One to pivot the field table and then the 2nd to use dbt_utils.star(). You have to put them in separate models because the star() macro will only work on Relation objects.

model1:

with pivot as (
  select
    {{ dbt_utils.pivot('field_name', dbt_utils.get_column_values(ref('field_table'), 'field_name')) }}
  from {{ ref('foo') }} 
  limit 1
)

select * from pivot

model2:

select {{ dbt_utils.star(from=ref('model1')) }} from {{ref('superheroes')}}