I have 2 models that are inserting into the same table of my warehouse. (schema='my_schema', alias='my_table', materialized='incremental', incremental_strategy='append')
It raises me the error:
dbt found two resources with the database representation "my_schema.my_table".
dbt cannot create two resources with identical database representations. To fix this,
The context of why I’m trying to do this
I want to migrate my ETL jobs to the ELT dbt. The ETL lets me having different components inside the same job to load the same table, but dbt seems to have stronger requirements when it comes to targets.
Are there ways to get around this issue? Or do I need to use an external tool to handle the different inserts into the same target?
I think there is something with versions but I think it’s not suited if the dataflows of the jobs are different.
But if your models are idempotent, it shouldn’t matter if you run them twice; so you can run them once when resource1 is updated, and a second time when resource2 is updated.
What is not an issue, or shouldn’t be an issue, is running a model more than once… even if the data hasn’t changed. This is following the logic I mentioned above about having an intermediate model joining your 2 sources.
What is an issue, and you won’t be able to do, is to have two models with the same name or the same alias.
And that’s the point of my question.
To insert into a table in different ways, I’m using “insert models”. For every insert models, I’m using the same alias. Each insert model select info from some models (these selected models differ depending of the insert model). The names of the insert models are different.
And I’m wondering if there are ways to get around the issue dbt raises when I do that.
I have the same problem when migrating a job from an old ETL tool to dbt. In the old ETL tool, there are many cases that required separate jobs to insert/update into the same 1 table. In dbt, that won’t be allowed, only 1 model per table. Based on my experience, there are some ways to work around.
Try to “merge“ all the logic into 1 job only, which is quite hard
Use prehook/posthook query. You can run multiple insert/update logic in the same model in prehook/posthook. But it’s quite limited if it has complex logic.
Use a staging layer instead. Write different logic for different staging/intermediate tables/models. Then, union/join all of them in the final model to write to the final table. I’m using this method.
My first thought is that you could have each model write into separate tables, then schedule another model that unions these two tables. This is similar to quangbilly79’s staging layer solution.
dbt requires that each resource in your project has a unique database representation (the combination of database.schema.identifier). If two resources resolve to the same database.schema.alias, dbt will raise the error:
dbt found two resources with the database representation my_schema.my_table. dbt cannot create two resources with identical database representations.
Yes correct this is a core dbt constraint. Two models cannot target the same table, as dbt enforces a strict 1:1 mapping between models and database objects. To work around this, you can use an ephemeral model to combine (e.g., union) multiple sources before materializing them into a single target table, or alternatively use a post-hook configuration.
If you want to write to different target table just modify the inbuilt macro default__generate_alias_name currently there is control flow statement where it checks firs the alias key value and then it goes to version key and then default is node.name.
you can pass some custom key pair inside the meta key too and use it inside this macro own code .
The constraint’s pretty fundamental to dbt’s design — one model, one output. But if your two sources are similar enough, you could just merge them into one model with a union or case logic. Might be simpler than the intermediate route depending on your setup.