ref-ing a model created in a different database

Help
1

In dbt cloud, I tried to create model ‘A’ in database ‘X’ by referencing model ‘B’ in database ‘Y’ (created in the same dbt project) with the ref function. However, when I run dbt run, it tries to reference model ‘B’ in database ‘X’ and I get an error.
Can I use the ref function only for models in the same database?
If I want to reference a model in a different database, do I need to use the source function?

Please let me know if you know.

2

I’m kind of new, but I’d expect you’d need a source set in dbt to reference database ‘Y’. Assuming you have that, maybe it’d help if you posted the code you’re using to create model ‘A’?

1 Like
3

Agreed that it would be helpful to see the code!

You should be able to set a model’s database using Custom databases | dbt Developer Hub, and the ref macro should then correctly find model B.

4

Thanks for your reply.

In dbt cloud, after deploying model ‘B’ to database ‘Y’ in one job, the following error occurred when deploying model ‘A’ to database ‘X’ in another job.

08:28:34 002003 (42S02): SQL compilation error:
08:28:34 Object ‘< Database ‘X’ >. < Schema >.< Model ‘B’ >’ does not exist or not authorized.

Also, the code for model ‘A’ is as simple as

> with final as (
> select 
> 	 id,
> 	created_date
> from {{ ref('b') }}
> )
> 
> select * from final
5

What does the models section of your dbt_project.yml look like? I’d expect if you’ve specified the database param in there for model B it should work.

6

Plz check below things

  1. Ensure u have configured proper database Y to model B using Custom databases | dbt Developer Hub

  2. As model A in database X depends on model B ensure u have build model B first before model A

1 Like