google-cloud-platformgoogle-bigquerydbt

DBT conflicts in resource naming across different GCP projects


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)


Solution

  • One solution with minimal changes can be:

    1. Rename the current customers.sql model and snapshot to customers_stg.sql
    2. Add enabled config depending on the target.project to both
    3. Create model customers.sql in the models folder, that will reference customers_stg

    snapshots/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') }}