Using different target schemas in DBT

Help
1

I’ve been working with BDT for 1 year or so and I don’t have anyone more experienced to turn to because the seniors ones left the company. Our environment is a little messed up and I’d like to rearrange it.
We use Bigquery as DB. And I would like to work with 3 different target datasets: Raw, Stage and Analytics. I would also like to use the custom dataset feature to be able to write the data to raw_googleads, raw_googleanalytics, raw_facebook, stage_marketing, stage_operations, analytics_marketing and so on…
But I couldn’t figure out how to set up different target schemas that could be linked to the custons schemas to direct the dbt processing output to the correct datasets… someone could help me?
Thanks in advanced.

2

See the updated answer below :point_down:

1 Like
4

This solution needs a small complement.
To record data at differents datasets is perfect, however as we use the REF macro in our codes and this macro searches for a table with the name passed as a parameter in the dataset pointed by {{this}}, it will be necessary to create a “Refer” macro like this one down here.

{% macro refer(dataset,tablename) %}

    {% if dataset =='*'  %}
       {{ ref('{{tablename}}') }}
    {% else %}
       {{dataset}}.{{tablename}}
    {% endif %}

{% endmacro %}

or something similar to this code. Then you will need replace “From {{ ref(‘table’) }}” with “From {{ refer(‘*’,‘table’) }}” when you want to keep ref based code or “From {{ refer(‘dataset’,‘table’) }}” when you want to force the reading of the table in another dataset informed by you, producing something like this “From dataset.table”.
if anyone has a better idea to solve the problem of reading in different datasets, please share it here.

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
6

I I will delete the previous answer, because I think this latter one is simpler and more intuitive. So future readers don’t get confused

7

I am having a similar problem - also in another thread…

I made it through the dbt fundamentals lab - but really stuck on how to connect everything together with developing in 1 space, deploying to another. the deployement “target” just seems to break things

  1. Developing in the “default” environment - this works
  2. Trying to “deploy” to another schema - fails every time, stuck on what changes need to be made everywhere in the project. Note on this - there is no “profile.yml” created, i try to create it, but it doesnt appear that it gets called by the project…

Any help appreciated… if i stick in the “dev” area i created, everything works, if i change to a new target schema in the general settings, everything breaks…

Any help appreciated
thanks

8

Are you using dbt cloud or dbt core?

9

Hi,

dbt Cloud for testing/learning

10

I don’t have experience with dbt cloud, just core :frowning: maybe @joellabes knows the answer.

11

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.