I’m a newbie. And I have a question about using multiple BigQuery datasets (or projects).
dbt means by “warehouse” as “BigQuery: a single BigQuery account, with access to one or more projects”. So in the documents, it just shows how to config and work with one dataset only.
My scenario is as shown below.
In the same project of bigquery, I have multiple datasets of data sources e.g. recurly, saleforces, …
and a dataset for transformed data e.g. edw.
Is it possible in dbt project when using multiple BigQuery datasets (or multiple BigQuery projects)? If so, please let me know how to configure in profiles.yml and give me a sample model using these configs.
I’m dealing with the exact same issue.
My query consists out of data from different bigquery projects and different bigquery datasets. But if I read the documentation it seems like only querying within the same Bigquery project is allowed.
Any updates if this is possible?
Yes, this is possible to do using custom database and schema.
In the simplest form you would use the database and schema parameters. You can use those parameters when defining sources, to specify where your source datasets are. And you can use them to define where dbt should build your models, either in your dbt_project.yml or model config.
For even more control you can also consider changing the generate_schema_name or generate_database_name macros.
Also note that the project_id you define in your profiles.yml will be the project used for BQ billing.
You’ve even already answered the question I wanted to ask next