drive-snowflake

Prerequisites & setup

Run this once, the first time you work with the stock or Redbook dbt projects on a new machine.

By the end of this page you can run Snow CLI commands against staging and prod, run the unit tests, and build/deploy locally.

Requirements

  • Snow CLI
  • AWS CLI, configured with profiles aws-drive-staging-admin and aws-drive-prod-admin
  • Python 3.8+
  • gcloud CLI (optional — only needed for GCP/GA4 integration work; see docs/gcloud_cli_setup.md in the repo)

There are two separate Snowflake identities in play, and they're set up differently:

Used forAuth
Personal admin connection (DRIVE_STAGING_ADMIN / DRIVE_PROD_ADMIN, role ACCOUNTADMIN)Ad-hoc snow sql queries, the scripts under scripts/Your own RSA key pair, configured in ~/.snowflake/config.toml
dbt service account (DRIVE_STAGING_DBT / DRIVE_PROD_DBT)dbt runs, schemachange migrationsShared service-account key, pulled from SSM into a venv-scoped env var

Both are set up below.

Steps

1. Configure Snow CLI connections

Snow CLI reads named connections from ~/.snowflake/config.toml. Create one nested [connections.<name>] block per environment — this is what snow sql --connection <name> and snow connection test --connection <name> use.

snippet.tomltoml
default_connection_name = "snowflake-drive-staging-admin"

[connections]
[connections.snowflake-drive-staging-admin]
account = "gn76638.ap-southeast-2"
user = "DRIVE_STAGING_ADMIN"
database = "SPECIFICATIONS_DB"
schema = "SPECIFICATIONS_SCHEMA"
warehouse = "DRIVE_WH"
role = "ACCOUNTADMIN"
authenticator = "SNOWFLAKE_JWT"
private_key_file = "/Users/<you>/.ssh/snowflake/snowflake_staging_admin_rsa_key.p8"

[connections.snowflake-drive-prod-admin]
account = "gw29919.ap-southeast-2"
user = "DRIVE_PROD_ADMIN"
database = "SPECIFICATIONS_DB"
schema = "SPECIFICATIONS_SCHEMA"
warehouse = "DRIVE_WH"
role = "ACCOUNTADMIN"
authenticator = "SNOWFLAKE_JWT"
private_key_file = "/Users/<you>/.ssh/snowflake/snowflake_prod_admin_rsa_key.p8"

Some tooling — schemachange, used by the migrations scripts — reads the older flat [<name>] format instead of the nested one. Add the same two connections again at the root level so both forms exist side by side:

snippet.tomltoml
# Additional connection for schemachange (root-level format)
[snowflake-drive-staging-admin]
account = "gn76638.ap-southeast-2"
user = "DRIVE_STAGING_ADMIN"
database = "SPECIFICATIONS_DB"
schema = "SPECIFICATIONS_SCHEMA"
warehouse = "DRIVE_WH"
role = "ACCOUNTADMIN"
authenticator = "SNOWFLAKE_JWT"
private_key_file = "/Users/<you>/.ssh/snowflake/snowflake_staging_admin_rsa_key.p8"

[snowflake-drive-prod-admin]
account = "gw29919.ap-southeast-2"
user = "DRIVE_PROD_ADMIN"
database = "SPECIFICATIONS_DB"
schema = "SPECIFICATIONS_SCHEMA"
warehouse = "DRIVE_WH"
role = "ACCOUNTADMIN"
authenticator = "SNOWFLAKE_JWT"
private_key_file = "/Users/<you>/.ssh/snowflake/snowflake_prod_admin_rsa_key.p8"

Keep both blocks for a connection in sync if you ever rotate the key path — nothing enforces they match.

You'll need an RSA key pair registered against your DRIVE_STAGING_ADMIN/DRIVE_PROD_ADMIN Snowflake users to point private_key_file at; request this from whoever manages Snowflake user access if you don't have one yet.

2. Test both connections

snippet.bashbash
snow connection test --connection snowflake-drive-staging-admin
snow connection test --connection snowflake-drive-prod-admin

3. Create the dbt virtual environment (per project — stock and Redbook each have their own)

snippet.bashbash
cd src/stock/dbt
python3 -m venv venv
source venv/bin/activate
python -m pip install dbt-core dbt-snowflake schemachange

4. Retrieve the dbt service account private keys and set the env vars the venv needs on every activation

snippet.bashbash
mkdir -p ~/.ssh/snowflake

# Staging
aws ssm get-parameter \
  --profile aws-drive-staging-admin \
  --region ap-southeast-2 \
  --name "/staging/drive/snowflake/service-users/drive_staging_dbt/private_key" \
  --with-decryption \
  --query "Parameter.Value" \
  --output text \
  > ~/.ssh/snowflake/snowflake_staging_dbt_key.p8

# Production
aws ssm get-parameter \
  --profile aws-drive-prod-admin \
  --region ap-southeast-2 \
  --name "/prod/drive/snowflake/service-users/drive_prod_dbt/private_key" \
  --with-decryption \
  --query "Parameter.Value" \
  --output text \
  > ~/.ssh/snowflake/snowflake_prod_dbt_key.p8

chmod 600 ~/.ssh/snowflake/snowflake_staging_dbt_key.p8 ~/.ssh/snowflake/snowflake_prod_dbt_key.p8

echo 'export DRIVE_STAGING_DBT_KEY_PATH="$HOME/.ssh/snowflake/snowflake_staging_dbt_key.p8"' \
  >> venv/bin/activate
echo 'export DRIVE_PROD_DBT_KEY_PATH="$HOME/.ssh/snowflake/snowflake_prod_dbt_key.p8"' \
  >> venv/bin/activate

DRIVE_STAGING_DBT_KEY_PATH and DRIVE_PROD_DBT_KEY_PATH are read by profiles.yml's staging and prod targets (private_key_path: "{{ env_var('DRIVE_STAGING_DBT_KEY_PATH', '') }}", and the prod equivalent) — without them, dbt commands against that target fail auth.

5. Install dbt package dependencies

snippet.bashbash
dbt deps

6. Repeat steps 3-5 for src/redbook/dbt

Needed if you're working on the Redbook project too — it has its own venv, and its own keys at the equivalent SSM paths under drive_staging_dbt/drive_prod_dbt.

Verify

snippet.bashbash
dbt debug --target staging
dbt debug --target prod

Confirms the connection, the key, and the profile all resolve for both targets. If both pass, you're set up.

What you can do from here

  • Run Snow CLI commands — snow sql -q "<query>" --connection snowflake-drive-staging-admin (or -prod-admin).
  • Run unit tests — make test (staging-target unit tests) or dbt test --select "test_type:unit" directly. See Makefile command reference.
  • Build and deploy locally — dbt run/dbt build per Running dbt locally, and deploy-migrations.sh per Deploying migrations locally.

See also

Esc
Esc Close