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
- dbt project initialised, see Initiating dbt Project
- Encrypted private key, see Generate RSA Key Pair
- Service user, role and warehouse, see Create Snowflake Service User
Set the environment variables
The values are kept in dbt/.env, created from dbt/.env.example.
# dbt/.env (git-ignored)
SNOWFLAKE_ACCOUNT=
SNOWFLAKE_USER=
SNOWFLAKE_PRIVATE_KEY_PATH=
SNOWFLAKE_PRIVATE_KEY_PASSPHRASE=
SNOWFLAKE_ROLE=
SNOWFLAKE_WAREHOUSE=| Variable | Value |
|---|---|
| SNOWFLAKE_ACCOUNT | Account identifier, in orgname-accountname format |
| SNOWFLAKE_USER | The service user, DBT_USER |
| SNOWFLAKE_PRIVATE_KEY_PATH | Absolute path to the encrypted private key (.p8) |
| SNOWFLAKE_PRIVATE_KEY_PASSPHRASE | Passphrase of the private key |
| SNOWFLAKE_ROLE | The role dbt uses, DBT_ROLE |
| SNOWFLAKE_WAREHOUSE | The 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.
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: 4Load the variables and test the connection
set -a; source dbt/.env; set +a
cd dbt
dbt debugThe variables are loaded into the shell, then dbt debug reports that all checks passed, including the connection.