dbt cannot create two resources with identical database representations

Question

I have a situation here as below:

There are two models in my dbt project

model-A

{{ config(
    materialized='ephemeral',
    alias='A_0001',
    schema=var('xxx_yyy_dataset')
) }}

model-B

{{ config(
    materialized='ephemeral',
    alias='B_0002',
    schema=var('xxx_yyy_dataset')
) }}

And these are getting materialized as incremental in same schema as xxx_yyy_dataset.Table_DDD

{{ config(
    materialized='incremental',
    alias='Table_DDD',
    schema=var('xxx_yyy_dataset')
) }}
SELECT * FROM {{ref('A_0001')}}
UNION ALL
SELECT * FROM {{ref('B_0002')}}

This is working fine and it is ingesting records into target table.

Now I have introduced another model - model-C ind different package model-C

{{ config(
    materialized='incremental',
    alias='Table_DDD',
    schema=var('xxx_yyy_dataset')
) }}

This gives me the following error:

$ dbt compile --profiles-dir=profile --target ide
Running with dbt=0.16.0
Encountered an error:
Compilation Error
  dbt found two resources with the database representation "xxx_yyy_dataset.Table_DDD".
  dbt cannot create two resources with identical database representations. To fix this,
  change the "schema" or "alias" configuration of one of these resources:
  - model.eplus_rnc_dbt_project.conrol_outcome_joined (models/controls/payment/fa-join/conrol_outcome_joined.sql)
  - model.eplus_rnc_dbt_project.dq_control_outcome_joined (models/controls/dq/dq-join/dq_control_outcome_joined.sql)

I have configured macro for custom macro as below :

{% macro generate_schema_name(custom_schema_name, node) -%}
    {%- set default_schema = target.schema -%}
    {%- if custom_schema_name is none -%}
        {{ default_schema }}
    {%- else -%}
        {{ custom_schema_name }}
    {%- endif -%}
{%- endmacro %}


{% macro generate_alias_name(custom_alias_name=none, node=none) -%}
    {%- if custom_alias_name is none -%}
        {{ node.name }}
    {%- else -%}
        {{ custom_alias_name | trim }}
    enter code here
    {%- endif -%}
{%- endmacro %}

Hi Manoj — in general we recommend not anonymizing your table names. It can introduce extra mistakes that can mask the underlying issue! For example, here the error message does not match the sample code, which makes things harder to debug. — Claire Carroll

Claire Carroll Claire Carroll · Accepted Answer · 2020-08-31T20:34:24

dbt is doing its job here!

You have two models that share the exact same configuration — conrol_outcome_joined and dq_control_outcome_joined.

This means that they'll both try to write to the same table: xxx_yyy_dataset.Table_DDD.

dbt is (rightfully) throwing an error here to avoid a problem.

As the error message suggests, you should update one of your models to use a different schema or alias so that it gets represented in your BigQuery project as a separate table.

dbt cannot create two resources with identical database representations

1 Answers