Error when referencing model from another dbt project which was installed as a package

Help
, ,
1

The problem I’m having

I have 2 dbt projects I am working on.

In ProjectA , I have installed ProjectB as a package successfully.

I now want to reference a model from ProjectB inside a model from Project A using :
FROM {{ref(‘ProjectB’,‘model’)}}

The table that this ‘model’ has created lives inside B.B.model.

but when I run dbt, it tries to find the ‘model’ inside A.B.model which does not exist.

How can I get dbt to read FROM {{ref(‘ProjectB’,‘model’)}} correctly from the B.B.model rather than A.B.model?

The context of why I’m trying to do this

The rationale behind installing ProjectB as a package in ProjectA is to see all DAGs in one dbt lineage graph.
My company wants to go for a multi repo approach and I wanted to see if it was possible to reference another model built in ProjectB rather than using Source so we could truly see all dependencies clearly in our lineage graph.

What I’ve already tried

I have tried tweaking configurations inside the dbt_project.yml and schema.yml and profiles.yml files but I have not been successful (although I may be doing it wrong).

Some example code or error messages

PS C:\Users\DLy\dev\dbt_pam> dbt run --select Silver.HDS
22:56:10  Running with dbt=1.6.0
22:56:10  Registered adapter: snowflake=1.6.1
22:56:11  Unable to do partial parsing because a project config has changed
22:56:12  Found 112 models, 1 snapshot, 47 sources, 0 exposures, 0 metrics, 493 macros, 0 groups, 0 semantic models
22:56:12  
22:56:21  Concurrency: 1 threads (target='dev')
22:56:21  
22:56:21  1 of 2 START sql table model PAM.hds_attribute_data_1 .......................... [RUN]
22:56:22  1 of 2 ERROR creating sql table model PAM.hds_attribute_data_1 ................. [ERROR in 0.62s]
22:56:22  2 of 2 SKIP relation PAM.hds_attribute_data_3 .................................. [SKIP]
22:56:22
22:56:22  Finished running 2 table models in 0 hours 0 minutes and 9.96 seconds (9.96s).
22:56:22  
22:56:22  Completed with 1 error and 0 warnings:
22:56:22
22:56:22  Database Error in model hds_attribute_data_1 (models\Silver\HDS\hds_attribute_data_1.sql)
22:56:22    002003 (02000): SQL compilation error:
22:56:22    Schema 'SILVER.HOMETRACK' does not exist or not authorized.
22:56:22    compiled Code at target\run\dbt_pam\models\Silver\HDS\hds_attribute_data_1.sql
22:56:22
22:56:22  Done. PASS=0 WARN=0 ERROR=1 SKIP=1 TOTAL=2

    FROM {{ref('dbt_raw_base','hds_attribute_data_base')}} ##what I am using to reference the 'model' from ProjectB (package 'dbt_raw_base')

below is how dbt compiles the above, it should be looking for the ‘hds_attribute_data_base’ model/table inside the BASE.hometrack.hds_attribute_data_base

    FROM SILVER.hometrack.hds_attribute_data_base
2

Hi Darren!

Something to check first, which might make your life a lot easier: Are you using dbt Cloud? If so, you can use the native cross-project functionality instead, and dbt Cloud’s metadata API will take responsibility for resolving pointers to an upstream project.

If you’re not, it is possible to import another project as a package and run it from there, but this is generally recommended for “utility” packages (e.g. for a common set of macros that every project needs access to) as opposed to projects focused on models.

Can you clarify what A.B.model means here? Is this meant to be database.schema.table? If you define the database and schema in your upstream package, that will be respected unless you then overwrite it in your downstream package’s dbt_project.yml

You’re spot on with this - the “source hack” used to be one of the only ways to resolve models across projects, but it’s not a good pattern. I strongly recommend this session from Coalesce this year, where Jeremy and Cameron discuss the tradeoffs in each approach to multi-project (a monolithic project, individual silos, installation as packages, the “source hack”, and a full dbt Mesh paradigm):

3

Hi @joellabes,

Thank you for your response.

My company is not using dbt cloud at the moment. We most likely won’t be using dbt cloud.

Yes, A.B.model means Database.schema.table.

For further context, ProjectB is a package I have installed in ProjectA.
It has a model that I would like to reference in ProjectA, when ProjectB runs this model, it successfully materializes as a table inside the BASE.HOMETRACK schema (BASE being database and HOMETRACK being the schema).

In ProjectA, when I try to reference the same model from the ProjectB package, it looks for the table inside the SILVER.HOMETRACK schema (SILVER being database and HOMETRACK being the schema).

In ProjectB (the package), the profiles.yml file is set up to generate all models inside the BASE.DARREN schema by default.
In its schema.yml file, it correctly sources that particular model and generates a table as expected in the BASE.HOMETRACK file as the dbt_project.yml file is configured to generate all models from the hometrack folder into the HOMETRACK schema which overrides the profiles.yml default schema settings.

In ProjectA (the project where I want to use Project B as a package), the profiles.yml file is set up to generate all models inside the SILVER.PAM schema by default.
In its schema.yml file, it doesnt have anything configured to reference the model from the package as I assumed the way I am referencing the model would look into packageB schema.yml file - FROM {{ref(‘packageB’,‘hds_attribute_data_base’)}} - not ProjectA’s schema.yml file. ProjectA’s dbt_project.yml file is configured to generate all models from the Silver folder into the SILVER database which is correct.

I can easily use {{ source(‘HOMETRACK’, ‘HDS_ATTRIBUTE_DATE_BASE’) }} from ProjectA with no problems. But I want to use {{ref(‘packageB’,‘hds_attribute_data_base’)}} instead to get a better view of our data lineage.

When you mention the following, can you tell me what you meant by upstream and downstream?
ProjectA is not a package it is a Project which is using ProjectB as a package, so would ProjectA be upstream and ProjectB mean downstream?:

Can you clarify what `A.B.model` means here? Is this meant to be `database.schema.table`? If you define the database and schema in your upstream package, that will be respected unless you then overwrite it in your downstream package's `dbt_project.yml`

Can you also elaborate further on ‘Source Hack’ please?

Is it possible that because ProjectA is trying to reference a model from ProjectB, the configuration inside ProjectA’s dbt_project.yml file, to generate tables inside SILVER Database overrides packageB’s dbt_project.yml file?

This doesn’t really make sense to me as referencing using - {{ref(‘packageB’,‘hds_attribute_data_base’)}} - doesn’t run any models from packageB, or does it…?

Would really appreciate your thoughts on this.

Many thanks,

Darren