DBT : How to Source from a table in different GCP project based on environment ?

Overview on how to source data from a table in a different GCP project based on the environment.

Introduction:
In data warehousing, it is common to have different environments such as development, staging, and production. When working with data in a GCP environment, it is often necessary to source data from tables in different GCP projects based on the environment.

Step 1: Set up service accounts
In order to access the data in the different GCP projects, you will need to set up service accounts for each project. Service accounts are a way for your GCP project to interact with other GCP projects or with other resources outside of your project. You can create a service account for each environment and grant it the necessary permissions to access the data in the other GCP project.

Step 2: Create environment variables
Once you have set up the service accounts, you can create environment variables to store the credentials for each service account. This allows you to switch between service accounts depending on the environment.

For example, you can create the following environment variables:

export DEV_PROJECT_ID=my-dev-project
export DEV_SA_KEY_FILE=my-dev-sa.json

export STAGING_PROJECT_ID=my-staging-project
export STAGING_SA_KEY_FILE=my-staging-sa.json

export PROD_PROJECT_ID=my-prod-project
export PROD_SA_KEY_FILE=my-prod-sa.json

Step 3: Modify your DBT model definition In your DBT model definition, you can use the environment variables to specify the project ID and service account key file based on the environment.

For example, you can use the following code:

{{ config(
    project_id=var("PROD_PROJECT_ID") if env == "prod" else var("STAGING_PROJECT_ID") if env == "staging" else var("DEV_PROJECT_ID"),
    google_cloud_credentials=var("PROD_SA_KEY_FILE") if env == "prod" else var("STAGING_SA_KEY_FILE") if env == "staging" else var("DEV_SA_KEY_FILE"),
    env=env,
) }}

{{% if env == "prod" %}}
    select *
    from `my-prod-project.my_dataset.my_table`
{{% elif env == "staging" %}}
    select *
    from `my-staging-project.my_dataset.my_table`
{{% else %}}
    select *
    from `my-dev-project.my_dataset.my_table`
{{% endif %}}

Step 4: Run your DBT command Finally, you can run your DBT command with the desired environment. For example:

$ dbt run --target prod
$ dbt run --target staging
$ dbt run --target dev