Apply joins on 2 DBT models which having same column names

Question

I want to apply joins on 2 DBT models and those 2 models has the same column names. Hence when I am trying to apply any joins. I am getting the following error.

column "rateplan_amendmenttype" specified more than once

Here is snippet of my code which I am trying:

with a_in as (
     select * from {{source('dbt_alice', 'common_a')}}
)

select * from a_in cross join {{source('dbt_alice', 'common_c')}}

Answer 1

Using the adapter function of DBT.We can achieve the result.

-- store the columns from a_in and b_in as a list in jinja
{%- set common_a_cols = adapter.get_columns_in_relation(source('dbt_alice', 'common_a')) -%}
{%- set common_c_cols = adapter.get_columns_in_relation(source('dbt_alice', 'common_c')) -%}

-- select every field, dynamically applying a rename to ensure there are no conflicts
select
  {% for col in common_a_cols %}
    common_a.{{col.name}} as a_{{col.name}},
  {% endfor %}
  {% for col2 in common_c_cols %}
    {% if not loop.last %}
     common_c.{{col2.name}} as b_{{col2.name}},
    {% else %}
      common_c.{{col2.name}} as b_{{col2.name}}
    {% endif %}
  {% endfor %}
from
  {{source('dbt_alice', 'common_a')}} as common_a
cross join
  {{source('dbt_alice', 'common_c')}} as common_c