Best practice for dev environment in a project writing to multiple databases

Help
,
1

I’m following How do I maintain different environments with dbt?

In our Snowflake instance, we use dbt to write to multiple databases.

Let’s say I have:
ANALYTICS_DB (production)

  • marketing
  • finance

ANALYTICS_DEV_DB (dbt development)

  • marketing
  • finance

PARTNER_FACING_DB (production)

  • customers

What is the best way to set up per-engineer schemas to accommodate for writing to more than one database? I have modified generate_schema_name() to give me per-engineer schemas in development, such as john__marketing and bruce__marketing, so that each individual can develop in an isolated environment. This works just fine for the default database specified in profiles.yml

But how do I apply this to multiple databases? Some of the code in our project will write to PARTNER_FACING_DB. If I let my generate_schema_name() macro do it’s thing there, then it’ll try to create bruce__customers in the customer-facing DB, which I don’t want. Is it best to:

  1. do that (and just hide those schemas from everyone except the engineers using privileges)
  2. have a PARTNER_FACING_DEV_DB (if so, how would I do this? with something like database: "{{ 'PARTNER_FACING_DB' if target.name == 'prod' else 'PARTNER_FACING_DEV_DB' }}" in dbt_project.yml ?)
  3. configure something in generate_database_name() (if so, I’m not sure what)
  4. do something like database: "{{ 'PARTNER_FACING_DB' if target.name == 'prod' else 'ANALYTICS_DEV_DB' }}" in dbt_project.yml + prepend the schema name with PARTNER_FACING_DB__ using generate_schema_name()
  5. Other

I apologize if this is a stupid question or is answered elsewhere! I searched around but didn’t come up with anything.