在两个具有相同列名的DBT模型上应用联接

Question

我想在两个DBT模型上应用联接，而这两个模型具有相同的列名。因此，当我尝试应用任何联接时。我得到以下错误。

column "rateplan_amendmenttype" specified more than once

以下是我正在尝试的代码片段：

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

Answer 1

使用DBT的adapter函数。我们可以取得结果。

-- 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