Using different target schemas in DBT

Help
5

Ok, in this case I think now understood your question better, see if it works for you:

You can have just one target in your profiles.yml

my_profile:
  outputs:
    dev:
      dataset: dev
      ...
      type: bigquery
  target: dev

Now you can override the generate_schema_name (Custom schemas | dbt Developer Hub) macro to

{% macro generate_schema_name(custom_schema_name, node) -%}

    {%- set default_schema = target.schema -%}
    {%- if custom_schema_name is none -%}

        {{ default_schema }}

    {%- else -%}

        {{ custom_schema_name | trim }}

    {%- endif -%}

{%- endmacro %}

And in your dbt_project.yml you can change the schemas to

name: 'my_project'
version: '1.0.0' 

config-version: 2

profile: 'my_profile'
  
models:
  my_project:
    some_folder:
      +schema: raw_googleads
    another_folder:
      +schema: stage_marketing
...

In this way you don’t need to worry about {{ ref() }}

3 Likes