I'm using dbt with BigQuery across two GCP projects, ProjectA and ProjectB. All my models are currently implemented and executed in ProjectA. Now, I'm trying to execute the same models in ProjectB, which has the same structure as ProjectA except for one model, which is a snapshot in ProjectA but not in ProjectB.
However, I'm encountering the following error:
Compilation Error
dbt found two resources with the database representation "`ecom`.`schema`.`customers`".
dbt cannot create two resources with identical database representations. To fix this,
change the configuration of one of these resources:
- model.ecom.customers (models/semantic/customers.sql)
- snapshot.ecom.customers (snapshots/semantic/customers.sql)
I'd like to keep these resources in the same dataset(semantic ) but across different projects. Is there a way to achieve this?
It functioned successfully when executed in distinct datasets. For instance, "customers.sql" (snapshot) with the schema named "semantic_snp" and "customers.sql" with the schema labeled "semantic". But, the goal is to have them in the same dataset name (in two different projects)
One solution with minimal changes can be:
customers.sql model and snapshot to customers_stg.sqlenabled config depending on the target.project to bothcustomers.sql in the models folder, that will reference customers_stgsnapshots/semantic/customers_stg.sql:
{% snapshot customers_stg %}
{{
config(
target_schema=...,
unique_key=...,
strategy=...,
enabled=target.project == 'ProjectA',
)
}}
SELECT ...
{% endsnapshot %}
models/semantic/customers_stg.sql:
{{
config(
enabled=target.project == 'ProjectB'
)
}}
SELECT ...
models/semantic/customers.sql:
SELECT * FROM {{ ref('customers_stg') }}