CODEWITHSUNDEEP
dbt
Manoj Kumar
Manoj Kumar

Reputation: 51

dbt cannot create two resources with identical database representations

I have a situation here as below:

There are two models in my dbt project

  1. model-A
{{ config(
    materialized='ephemeral',
    alias='A_0001',
    schema=var('xxx_yyy_dataset')
) }}
  1. 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 %}

Upvotes: 5

Views: 6841

Answers (2)

João Ramos
João Ramos

Reputation: 105

I had been struggling with the same problem here, I wanted to create a pipeline of tests that would only be written to a single incremental table and it triggers the same error message, but I am afraid it is not possible with DBT.

To resolve it, I created a main model that selects and unions the info from all the individual test models that I created (I previously created a model/table for each test to be applied) and that in the end with the post_hook I just drop the individual tables previously created, thus, I only stick to a final testing table that keeps all the information.

It is not what I really desired since it is not a dynamic implementation, because every test that is created needs to be added to the main table union and also to the drop statement in the post_hook, however if any test breaks individually it would not break all the other tests, neither a bunch of tables exists in my database when I start my work, you just need to orchestrate it at the right time for you.

(Another possible approach could be creating 1 model, where in the pre_hook, you create all the tables that you want, since dbt cannot make models write to the same table, in the "main" part of the model, you select and union the info of all the pre-hook tables, and then in the post-hook you delete the tables created before, not sure if this can work, not tested, but you do reduce the amount of tables written to the Database, which is the main drawback of the 1st approach although for a short period of time)

Upvotes: 0

Claire Carroll
Claire Carroll

Reputation: 759

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.

Upvotes: 5

Related Questions