How to use two different databases and their schemas

[pablomendesfaria]

The problem I’m having

I have two databases in snowflake, raw and analytics. In raw I have two schemas, raw and staging, and in analytics I have the mart schema. In the profiles.yml file I configured it to use the analytics database together with the mart schema, but I also need to access the raw database and its schemas to be able to create the staging views there.

The context of why I’m trying to do this

According to what I have researched and studied, this approach would be the best practice, separating them into two different databases and leaving the staging schema in the raw database, because in the end the staging data is basically the raw data with minimal processing, and data analysts should not have access to the raw data, so putting the staging in the analytics database does not make sense from this point of view.

Is it possible to do what I want? Is it possible to access the raw database through dbt-core even if the profiles.yml is configured to use the analytics database? If so, how do I do it?

What I’ve already tried

I haven’t tried anything yet, because I don’t even know if it’s possible to do this, and if so, I don’t know how. This is one of the first pipelines I’m building, so I’m doing it while I learn.

The profiles.yml

[a_slack_user]

Sure, as long as you get your db role permissions correct, you can set the db in your project file per resource-path. The reason you wouldn’t want to do that though, is that RAW may not be easily recreated, while dbt wants your transformation layer idempotent where you could easily drop the db and recreate from scratch. From that point of view, it makes more sense to keep staging apart from RAW. You can easily set permissions so that your analysts don’t have access to the staging schema and it would achieve the same results.

_{Note: @Eugene Kim (Orrum) originally posted this reply in Slack. It might not have transferred perfectly.}