edw_streamlit — Snowflake CI/CD Setup & Automation

This document describes the one-time Snowflake setup and the GitHub Actions automation that ships Streamlit-in-Snowflake apps from the edw_streamlit repo into the APPS_PROD_DB database.

It is the single source of truth for: what the admin must provision in Snowflake, how the deploy pipeline works, and how new apps are added going forward.

1. Goals

Developers author Streamlit apps locally and deploy them to DEV (APPS_DEV_DB) using their own credentials.
All app code lives in the GitHub repo edw_streamlit. Changes go through Pull Requests for review.
When a PR is merged to main, GitHub Actions automatically deploys to PROD (APPS_PROD_DB).
Future apps (DataHawk, FinOps, etc.) require zero further admin tickets.

2. Architecture overview

┌─────────────────────────┐    ┌──────────────────────┐    ┌─────────────────────┐
│ Developer laptop / VM   │    │ GitHub repo          │    │ Snowflake           │
│  - snow CLI             │    │  edw_streamlit       │    │                     │
│  - personal user (RSA)  ├───►│  PR → main           │    │  APPS_DEV_DB        │
│                         │    │   Actions on merge   ├───►│  APPS_PROD_DB       │
└─────────────────────────┘    │   (svc user, RSA)    │    │                     │
                               └──────────────────────┘    └─────────────────────┘

DEV deploys are manual, run by the developer.
PROD deploys are automated by GitHub Actions on merge to main.
Both environments use key-pair authentication (no passwords).

3. One-time Snowflake setup (admin)

Run once per environment. After this, no per-app Snowflake work is required.

3.1 Create the deployer role on `APPS_PROD_DB`

USE ROLE SECURITYADMIN;
CREATE ROLE IF NOT EXISTS APPS_PROD_DEPLOYER;

USE ROLE SYSADMIN;

-- Database-level
GRANT USAGE         ON DATABASE APPS_PROD_DB TO ROLE APPS_PROD_DEPLOYER;
GRANT CREATE SCHEMA ON DATABASE APPS_PROD_DB TO ROLE APPS_PROD_DEPLOYER;

-- FUTURE grants — apply automatically to every new schema in APPS_PROD_DB
GRANT USAGE            ON FUTURE SCHEMAS IN DATABASE APPS_PROD_DB TO ROLE APPS_PROD_DEPLOYER;
GRANT CREATE STREAMLIT ON FUTURE SCHEMAS IN DATABASE APPS_PROD_DB TO ROLE APPS_PROD_DEPLOYER;
GRANT CREATE STAGE     ON FUTURE SCHEMAS IN DATABASE APPS_PROD_DB TO ROLE APPS_PROD_DEPLOYER;
GRANT CREATE VIEW      ON FUTURE SCHEMAS IN DATABASE APPS_PROD_DB TO ROLE APPS_PROD_DEPLOYER;
GRANT CREATE TABLE     ON FUTURE SCHEMAS IN DATABASE APPS_PROD_DB TO ROLE APPS_PROD_DEPLOYER;

-- Warehouse
GRANT USAGE ON WAREHOUSE APPS_PROD_WH TO ROLE APPS_PROD_DEPLOYER;

3.2 Make the read role auto-see all future app objects

So the running apps don't need per-object grants.

GRANT USAGE  ON FUTURE SCHEMAS IN DATABASE APPS_PROD_DB TO ROLE APPS_PROD_RO;
GRANT SELECT ON FUTURE VIEWS   IN DATABASE APPS_PROD_DB TO ROLE APPS_PROD_RO;
GRANT SELECT ON FUTURE TABLES  IN DATABASE APPS_PROD_DB TO ROLE APPS_PROD_RO;

3.3 Create the service user (key-pair auth)

The developer will generate the RSA key pair locally and provide only the public key.

USE ROLE USERADMIN;
CREATE USER SVC_APPS_PROD_DEPLOY
  RSA_PUBLIC_KEY='<paste public key body here>'
  DEFAULT_ROLE=APPS_PROD_DEPLOYER
  DEFAULT_WAREHOUSE=APPS_PROD_WH
  TYPE=SERVICE
  MUST_CHANGE_PASSWORD=FALSE
  COMMENT='GitHub Actions deploy for edw_streamlit (PROD)';

USE ROLE SECURITYADMIN;
GRANT ROLE APPS_PROD_DEPLOYER TO USER SVC_APPS_PROD_DEPLOY;

3.4 (Recommended) Mirror in DEV

Same SQL, replacing PROD with DEV everywhere. Optional today (DEV deploys are still done manually by the developer), but useful if we want a CI dry-run on PRs later.

3.5 What this setup does NOT grant

No data read on any non-APPS database.
No DROP, no role changes, no warehouse mods.
No password login — service user is key-only.

4. One-time GitHub setup (developer)

4.1 Generate the RSA key pair (on the developer's machine, never on a shared box)

# Encrypted private key
openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out rsa_key.p8
# Public key
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

Send only rsa_key.pub to the admin (paste between -----BEGIN PUBLIC KEY----- and -----END PUBLIC KEY-----).

4.2 Create a `prod` GitHub Environment with required reviewers

Repo Settings → Environments → New environment → name it prod → enable Required reviewers and add yourself / a teammate. Merges to main will then pause for human approval before the PROD deploy runs.

4.3 Add GitHub Actions secrets to the `prod` environment

Secret name	Value
`SNOWFLAKE_ACCOUNT`	`QTS-BHB16011`
`SNOWFLAKE_USER`	`SVC_APPS_PROD_DEPLOY`
`SNOWFLAKE_ROLE`	`APPS_PROD_DEPLOYER`
`SNOWFLAKE_WAREHOUSE`	`APPS_PROD_WH`
`SNOWFLAKE_PRIVATE_KEY`	full PEM contents of `rsa_key.p8`
`SNOWFLAKE_PRIVATE_KEY_PASSPHRASE`	the passphrase you used in step 4.1

Use the environment secret store (not repo-level) so production credentials only unlock during an approved PROD deploy.

5. Repo layout

edw_streamlit/
├── apps/
│   ├── wday_invoice_attachments/
│   │   ├── streamlit_app.py
│   │   ├── environment.yml
│   │   ├── snowflake.yml
│   │   ├── README.md
│   │   └── .gitignore
│   ├── datahawk/
│   └── fin_ops/
├── snowflake/
│   └── views/
│       └── wday_invoice_attachments.sql      # WIA view DDL, env-aware via {{ENV_DB}}
├── .github/
│   └── workflows/
│       └── deploy-prod.yml
└── README.md

Each Streamlit app is a self-contained folder under apps/. The CI workflow discovers all of them and deploys each one.

6. The automation: `.github/workflows/deploy-prod.yml`

Triggers on push to main (i.e. after a PR is merged), runs against the prod GitHub Environment (which holds the secrets and approval gate).

name: Deploy Streamlit apps to PROD

on:
  push:
    branches: [ main ]
  workflow_dispatch:   # also allow manual runs

jobs:
  deploy:
    runs-on: ubuntu-latest
    environment: prod        # uses prod env secrets + required reviewers
    steps:
      - uses: actions/checkout@v4

      - name: Set up Python
        uses: actions/setup-python@v5
        with:
          python-version: "3.11"

      - name: Install Snowflake CLI
        run: |
          python -m pip install --upgrade pip
          pip install snowflake-cli

      - name: Write Snowflake CLI config
        env:
          SF_ACCOUNT:    ${{ secrets.SNOWFLAKE_ACCOUNT }}
          SF_USER:       ${{ secrets.SNOWFLAKE_USER }}
          SF_ROLE:       ${{ secrets.SNOWFLAKE_ROLE }}
          SF_WAREHOUSE:  ${{ secrets.SNOWFLAKE_WAREHOUSE }}
          SF_KEY:        ${{ secrets.SNOWFLAKE_PRIVATE_KEY }}
          SF_PASSPHRASE: ${{ secrets.SNOWFLAKE_PRIVATE_KEY_PASSPHRASE }}
        run: |
          mkdir -p ~/.snowflake
          umask 077
          echo "$SF_KEY" > ~/.snowflake/rsa_key.p8
          cat > ~/.snowflake/config.toml <<EOF
          default_connection_name = "apps_prod"
          [connections.apps_prod]
          account          = "$SF_ACCOUNT"
          user             = "$SF_USER"
          role             = "$SF_ROLE"
          warehouse        = "$SF_WAREHOUSE"
          database         = "APPS_PROD_DB"
          authenticator    = "SNOWFLAKE_JWT"
          private_key_file = "/home/runner/.snowflake/rsa_key.p8"
          EOF
          chmod 600 ~/.snowflake/config.toml ~/.snowflake/rsa_key.p8
        # passphrase is provided via env, never written to disk

      - name: Apply view DDL
        env:
          PRIVATE_KEY_PASSPHRASE: ${{ secrets.SNOWFLAKE_PRIVATE_KEY_PASSPHRASE }}
        run: |
          shopt -s nullglob
          for f in snowflake/views/*.sql; do
            echo "Applying $f"
            snow sql -c apps_prod -f "$f"
          done

      - name: Deploy each Streamlit app
        env:
          PRIVATE_KEY_PASSPHRASE: ${{ secrets.SNOWFLAKE_PRIVATE_KEY_PASSPHRASE }}
        run: |
          set -euo pipefail
          for app_dir in apps/*/; do
            if [ -f "$app_dir/snowflake.yml" ]; then
              echo "::group::Deploying $app_dir"
              ( cd "$app_dir" && snow streamlit deploy -c apps_prod --replace )
              echo "::endgroup::"
            fi
          done

How this satisfies the design:

Runs only on merge to main (not on PR open).
The environment: prod line means the job pauses until a required reviewer approves in the GitHub UI before any Snowflake change happens.
View DDL in snowflake/views/*.sql runs first, so the app finds its supporting objects.
Then each apps/*/snowflake.yml is deployed via snow streamlit deploy.

7. Adding a new app (zero admin work)

Create apps/<new_app>/ with streamlit_app.py, environment.yml, snowflake.yml, README.md.
(If needed) drop a SQL file into snowflake/views/<new_app>.sql for that app's supporting views.
Open a PR in edw_streamlit. Reviewer signs off and merges.
The CI workflow runs, an approver clicks Approve in GitHub, and the app appears in APPS_PROD_DB.<schema>.<app> ready to use.

The deployer role's FUTURE grants (Section 3.1) cover any new schema or object the new app introduces.

8. DEV workflow (unchanged)

Developer side, no admin involvement:

cd apps/<app>
snow streamlit deploy -c apps_dev --replace

The developer's personal Snowflake user already has APPS_DEV_RW and the cross-DB read grants needed for development. Merging is the only path to PROD.

9. Security summary

Concern	Mitigation
Stolen GitHub secret	Service user is scoped to `APPS_PROD_DB` only; rotate one key, no Fivetran impact.
Unreviewed code reaching PROD	PR review + branch protection on `main`; `prod` GH Environment requires manual approval before workflow runs.
Service user doing too much	Role intentionally has zero data-read and no access to other databases.
Password leakage	No password — key-pair only, encrypted private key, passphrase via secret.
Audit trail	All DDL/streamlit deploys appear in query history under `SVC_APPS_PROD_DEPLOY`.

10. Troubleshooting

Symptom	Likely cause	Fix
`JWT token is invalid`	Public key on the service user doesn't match the private key in CI.	Re-register the public key on `SVC_APPS_PROD_DEPLOY` (Section 3.3).
`Encrypted private key, you must provide the passphrase`	`SNOWFLAKE_PRIVATE_KEY_PASSPHRASE` secret missing or wrong.	Re-set in the `prod` GH Environment.
`Insufficient privileges to operate on schema`	New schema didn't pick up FUTURE grants (rare; usually because grants were applied after the schema was created).	Re-grant explicitly on that schema, then verify FUTURE grants exist on the database.
Workflow paused indefinitely	Required reviewer hasn't approved.	Approve in GitHub → Actions → run → "Review deployments".

11. Contacts / change log

Initial setup approved on by .
Owner: Steve Farmer.
Repo: https://github.com/QTS-RC/edw_streamlit

[Diagram]