I.e. We want to have one dev database with personal schemas and one separate production database with schemas such as bronze, silver, gold etc.
However dbt cloud appears to only accept one database in the project connection and only ask for a “schema” for the deployment environment when I try to create the “production” environment.
Is it in fact possible to achieve the setup referenced above using dbt with Redshift?
Maybe by overwriting the database/schema information in some parts of the project?
If not, how are you redshift users out there managing a production environment with all production tables mixed together in one schema? Seems like that would become very unmanageable dealing with table/view level permissions for every single table/view (both intermediate/staging and final) in the prod environment?
For anyone else with this same issue, my research and help from dbt labs support has led me to the following conclusions. Having different redshift databases (prod/dev) is not really possible in a convenient way using a teams plan. Here are three different ways to achieve something like this:
Use dbt Core instead with more flexibility in terms of defining databases, schemas etc. in the profiles.yml file
Obviously you lose whatever benefits you were hoping to get from dbt Cloud.
Upgrade to dbt Cloud enterprise plan
Having the potential to have multiple projects you could have two projects (with different databases) referencing the same git repo. However I don’t personally believe the price is realistic for such a basic feature so I would not opt for this approach.
Experiment with environment variables in the database field
I have not tried this approach and I am not sure it would work for Redshift. It also feels a bit like circumventing the purpose of dbt cloud. But potentially there could be a way to use environment variables to pass different databases to dbt depending on whether you are running the job for dev/prod (something similar to this Environment variables | dbt Developer Hub)
Hopefully we will see some dbt cloud improvements going forward allowing for this kind of business standard practice of having separate databases and schemas.
You can use Extended Attributes to override database name (just make it environment variable). That works fine if all databases are on the same cluster.
What I am trying to figure out is if it possible to connect to different clusters for dev / test / prod (on Teams plan)