BankOrg Docs

In progress

BankOrg Data Platform / Environment Setup

Connect dbt to Snowflake

Connect the bankorg dbt project to Snowflake with key-pair authentication and environment variables.

dbt connects to Snowflake as DBT_USER with the private key. Every connection value comes from environment variables, so no credentials are stored in the profile or the repository.

Prerequisites

Set the environment variables

The values are kept in dbt/.env, created from dbt/.env.example.

dbt/.env
# dbt/.env (git-ignored)
SNOWFLAKE_ACCOUNT=
SNOWFLAKE_USER=
SNOWFLAKE_PRIVATE_KEY_PATH=
SNOWFLAKE_PRIVATE_KEY_PASSPHRASE=
SNOWFLAKE_ROLE=
SNOWFLAKE_WAREHOUSE=
VariableValue
SNOWFLAKE_ACCOUNTAccount identifier, in orgname-accountname format
SNOWFLAKE_USERThe service user, DBT_USER
SNOWFLAKE_PRIVATE_KEY_PATHAbsolute path to the encrypted private key (.p8)
SNOWFLAKE_PRIVATE_KEY_PASSPHRASEPassphrase of the private key
SNOWFLAKE_ROLEThe role dbt uses, DBT_ROLE
SNOWFLAKE_WAREHOUSEThe warehouse dbt uses, DBT_WH

Create the profile

The profile is dbt/profiles.yml, created from dbt/profiles.yml.example. Its name, bankorg, matches dbt_project.yml. Target dev writes to DEV_DWH, and prod to PRD_DWH.

dbt/profiles.yml
bankorg:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}"
      user: "{{ env_var('SNOWFLAKE_USER') }}"
      private_key_path: "{{ env_var('SNOWFLAKE_PRIVATE_KEY_PATH') }}"
      private_key_passphrase: "{{ env_var('SNOWFLAKE_PRIVATE_KEY_PASSPHRASE') }}"
      role: "{{ env_var('SNOWFLAKE_ROLE') }}"
      warehouse: "{{ env_var('SNOWFLAKE_WAREHOUSE') }}"
      database: DEV_DWH
      schema: PUBLIC
      threads: 4
    prod:
      # same connection settings as dev
      database: PRD_DWH
      schema: PUBLIC
      threads: 4

Load the variables and test the connection

Terminal
set -a; source dbt/.env; set +a
cd dbt
dbt debug

The variables are loaded into the shell, then dbt debug reports that all checks passed, including the connection.

Output of dbt debug.