How to setup dbt development environment
with vscode extension

1. Development environment for dbt

To be able to deploy fast in production and development, the configuration for the dbt should be set by environment variables.

This allow us to have quick switch between the profiles, schemas, variables and more..

Why we are doing today is to have an .envrc holding all the configuration for the dbt.

for example in development:

# Snowflake account
export SNOWFLAKE_ACCOUNT=YOURACCOUNT.us-east-1
export SNOWFLAKE_WAREHOUSE=YOURWAREHOUSE

export DBT_PROFILE=development
export DBT_ROLE=DEVELOPMENT

# Tenant / Used for the query tagging
export TENANT_NAME=CLIENT_NAME

# Source
export DBT_SOURCE_DATABASE=RAW_ZONE
export DBT_SOURCE_SCHEMA=${TENANT_NAME}

# Target
export DBT_TARGET_DATABASE=STANDARD_ZONE_DEVELOPMENT
export DBT_TARGET_SCHEMA=${TENANT_NAME}_${SNOWFLAKE_USER}

Note how we add the SNOWFLAKE_USER to the target schema, so that the users will not override their work in case they want to work on the same client.

To make this work this is what we have added profiles.yml

development:
  target: development
  outputs:
    development:
      type: snowflake
      account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}"
      warehouse: "{{ env_var('SNOWFLAKE_WAREHOUSE') }}"
      user: "{{ env_var('SNOWFLAKE_USER') }}"
      password: "{{ env_var('SNOWFLAKE_PASSWORD') }}"
      role: "{{env_var('DBT_ROLE')}}"
      database: "{{ env_var('DBT_TARGET_DATABASE') }}"
      schema: "{{ env_var('DBT_TARGET_SCHEMA') }}"
      threads: 8


production:
  target: production
  outputs:
    production:
      type: snowflake
      account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}"
      user: "{{ env_var('SNOWFLAKE_USER') }}"
      password: "{{ env_var('SNOWFLAKE_PASSWORD') }}"
      role: "{{env_var('DBT_ROLE')}}"
      warehouse: "{{ env_var('SNOWFLAKE_WAREHOUSE') }}"
      database: "{{ env_var('DBT_TARGET_DATABASE') }}"
      schema: "{{ env_var('DBT_TARGET_SCHEMA') }}"
      threads: 8

and to glue everything you need to switch to environment variables in the dbt_project.yml. Note that this feature is is not supported on old dbt versions. In the documentation it is written that we can use environment variables in dbt_project.yml

dbt_project.yml

profile: "{{ env_var('DBT_PROFILE', 'development') }}"

vars:
  source_database: "{{ env_var('DBT_SOURCE_DATABASE') }}"
  source_schema: "{{ env_var('DBT_SOURCE_SCHEMA') }}"

So the final command will be:

  export DBT_PROFILES_DIR="."
  dbt --no-anonymous-usage-stats run

While using justfile:

dbt:
  #!/bin/bash
  echo "SOURCE: $DBT_SOURCE_DATABASE / $DBT_SOURCE_SCHEMA"
  echo "TARGET: $DBT_TARGET_DATABASE / $DBT_TARGET_SCHEMA"
  DBT_PROFILES_DIR="{{invocation_directory()}}"

  poetry run \
    dbt --no-anonymous-usage-stats run \
    --fail-fast

2. Setup of the “dbt Power User” vscode extension

Install the “dbt Power User” as usual.

Open the vscode prefferences, as json.

Point the current folder for the profilesDir like this:

"dbt.profilesDirOverride": "${workspaceFolder}",

Associate the sql files to be used as jinja templates.

  "files.associations": {
        "*.sql": "jinja-sql"
    },

And then make sure you are using the correct python environment.

Enjoy the dbt!