Can you have multiple bigquery destinations in a single project?

In-Depth Discussions
1

We are looking to create some datasets in one gcp project and other datasets in a separate gcp project. I know that you can have multiple sources in different gcp projects but we need to have multiple destinations. Any help would be greatly appreciated!

2

You can easily define the database (GCP project) where your model will be created by specifying it on each model configuration block:

{{-
	config(
		database="your-gcp-project"
	)
-}}

SELECT 1

This should do the trick. You can also define this logic in your dbt_project.yml instead (for example, all the models contained in this path will use this database, and the models contained in this other path will use this other database instead).

If you have dev and prod environments pairs for each GCP project, and you want to separate the runs, you can create different targets in your profile and define a different GCP project for each target. This will remove the need to specify the database for each model or path. For the execution, I would recommend to create a tag convention for your project (one for each GCP project, for example), and assign the tags to the models (or on your dbt_project.yml file) to include or exclude them on runs. That way, you can call dbt like dbt run --select tag:your-tag --target your-target, and execute only the desired target (which for you translates to building to models for a specific GCP project). If you have a considerable complex model execution logic, you can also give [selectors] a try.

1 Like