Deploying models into multiple datasets within BigQuery

[1cadumagalhaes]

Hello everyone.
I'm studying dbt, and in my current warehouse we have our data divided into different datasets.
For example:

• A media dataset, where there are some aggregated tables generated during other transformations and that will be used later
• A data_online dataset with tables that will be used in dashboards and that depends of the media
• An audiences dataset where we store tables that will be used in other applications (such as sending them to specific platforms)

For us this organization is crucial, since multiple teams access our project and we use dataset level permissions.

The problem is in dbt (I'm using the core) we declare our target, and that will be used by every model. I know we could use dbt run with the --target option, but I wish we could define the destination dataset within our model code, but that doesn't seem possible.

Has anyone tried to do something similar? Or maybe know a way I could implement this

[morsapaes]

Not an expert in the dbt-bigquery adapter, but as far as I understand from the adapter code: you should be able to use the schema configuration (which is interchangeable with dataset) to define datasets for individual sources and models; either in the config block or in dbt_project.yml.

Example (source):

models:
    stacktonic_example_project:
        marts:
            core:
                materialized: table
                +dataset: core
                +tags:
                    - "core"
                    - "daily"
            marketing:
                materialized: table
                +dataset: marketing
                +tags:
                    - "marketing"
                    - "daily"
        staging:
            google_analytics:
                materialized: view
                +dataset: staging
                +tags:
                    - "staging"
                    - "daily"

[1cadumagalhaes]

Thank you very much @morsapaes, I'll try that and bring back the results!

[1cadumagalhaes]

Ok, after trying (and reading the full post that references the example you brought), what it does is use the dataset of our profile as a prefix, and what we define in our models as a suffix to create the datasets with <prefix>_<suffix>

So if anyone else is trying to do this, you can do something like

# in your profiles.yml

my_dbt_project:
  target: prod
  outputs:
    prod:
      dataset: data
      ...
    dev:
      dataset: data_dev
      ...

# in your dbt_project.yml
...
models: 
  my_dbt_project:
    model_1:
      +dataset: model_1
      ...
   model_2:
     +dataset: model_2
      ...
   model_3: 
     dataset: model_3 #tried this just to see if it would ignore the prefix set in my profile
      ...

So, when we run in the default target it will create the structure below:

• data_model_1
• data_model_2
• data_model_3

And if we run for the dev target, it will create:

• data_dev_model_1
• data_dev_model_2
• data_dev_model_3

It doesn't give the flexibility I was hoping (since we'll have datasets with the same prefix), but it does solve the problem of creating everything into one single dataset, and I guess this is a good way to make easier to identify everything that's created by dbt. Thanks again!

[jacopobianchini]

Thank you everyone for this explanation. But finally @1cadumagalhaes why the third dataset still follows the prefix pattern even without the "+" in your dbt_project.yml? Moreover (I'm new to DBT and trying to understand), this project DOES create those datasets from scratch in BigQuery or they have to be created before so that DBT can write on them?

Thank you for your attention!

[1cadumagalhaes]

Hey @jacopobianchini . As I commented, I was just testing the behavior, and for some reason it did work even without the +:

model_3: 
     dataset: model_3 #tried this just to see if it would ignore the prefix set in my profile

I'm not sure why, but that's how it works.

And about your other question, DBT will create the datasets by itself

[jacopobianchini]

Thanks again for your reply @1cadumagalhaes . Yeah I was questioning myself why it did work even without the + (seems odd to me :) but fair enough) . And great for the automatic dataset creation! Thanks!

[ngould]

Is @1cadumagalhaes's answer the latest and greatest? It would be nice to be able to name datasets freely without following this sort of <prefix>_<suffix> pattern.

[atd-vdeshbhratar]

Hi @ngould, you can override the existing dbt macro generate_schema_name and customize your schema name. For reference, https://docs.getdbt.com/docs/build/custom-schemas

[ngould]

Thanks @atd-vdeshbhratar!

[ruchak14]

I am trying to follow same pattern in my dbt_project.yml as I want to dump the models output into different dataset.

profiles.yml structure as below

outputs:
    dev:
      dataset: rk
      job_execution_timeout_seconds: 300
      job_retries: 1

dbt_project.yml structure as below

models:
  Demo:
    trade:
      +dataset: trade
      +materialized: view
      
    lead:
      +dataset: lead
      +materialized: view

new to dbt world...not getting exactly where things are going wrong

[ruchak14]

1cadumagalhaes can you help me here