MOD-102 — Snowflake account configuration & governance¶

Purpose¶

Bootstrap the Snowflake side of the data platform. Establishes account-level governance: warehouses + resource monitors, per-domain databases (7 domains × 3 envs = 21), RBAC roles (20), storage / API / notification integrations, network policy, PII tag + masking policies, and ACCOUNT_USAGE export tasks for MOD-076.

FR scope: FR-409 (warehouses), FR-410 (databases + roles), FR-411 (integrations + governance), FR-412 (migrations contract).

Architectural decisions: ADR-035 (Snowflake), ADR-025 (SST is the default IaC — but MOD-102 is the explicit exception: Snowflake objects aren't Pulumi resources, they're SQL).

Architecture¶

snowflake/setup/0001..0014.sql   ← versioned migrations (read-only after apply)
snowflake/rollback/0001..0014.sql ← paired rollback stubs (FR-412)
src/runner.ts                     ← apply | plan | rollback CLI
src/connection.ts                 ← key-pair JWT auth, decrypt-in-process
src/migrations.ts                 ← discovery, ordering, checksum, statement split
                                    ↓
                                  Snowflake account AQUTVUB-DC22437
                                    ├── BANK_PLATFORM_SETUP db (governance)
                                    │     ├── MIGRATIONS.APPLIED   (sha256 + commit)
                                    │     ├── GOVERNANCE           (PII tags + masking)
                                    │     └── OBSERVABILITY        (export tasks)
                                    ├── 6 warehouses + 6 resource monitors
                                    ├── 21 per-domain databases
                                    ├── 20 RBAC roles
                                    ├── BANK_S3_INTEGRATION (storage)
                                    ├── BANK_SNS_INTEGRATION (notification, OUTBOUND)
                                    ├── BANK_GITHUB_INTEGRATION (API)
                                    └── BANK_NETWORK_POLICY

Migration sequence¶

Setup scripts apply in numeric order. Once a script is recorded in MIGRATIONS.APPLIED, its content is locked: any byte change is a checksum mismatch and the runner refuses to apply. Bug fixes are new files with higher version numbers.

#	File	What it creates
1	account_basics	BANK_PLATFORM_SETUP db, MIGRATIONS table, BANK_FUNCTIONAL_ROLE, BANK_SECURITY_ROLE
2	warehouses	PROD_ETL_WH, PROD_ANALYTICS_WH, PROD_RISK_WH, PROD_DECISIONS_WH, PROD_DBT_WH, NONPROD_WH + 6 resource monitors
3	databases	21 per-domain databases (7 domains × prod/uat/dev)
4	schemas	RAW, STAGING, TRANSFORM, GOVERNANCE per database
5	roles_prod	BANK_PROD_{CORE,KYC,AML,PAYMENTS,CREDIT,RISK,PLATFORM}_ROLE
6	roles_nonprod	BANK_NONPROD_* counterparts
7	roles_cross_domain	BANK_ANALYTICS_ROLE, BANK_REPORTING_ROLE, BANK_DEVELOPER_ROLE, BANK_DBT_ROLE
8	grants	per-domain USAGE + per-schema CREATE TABLE/VIEW + cross-domain SELECT
9	storage_integration	BANK_S3_INTEGRATION → bank-iceberg-{prod,uat,dev}
10	notification_integration	BANK_SNS_INTEGRATION (OUTBOUND, ENABLED=FALSE — MOD-076 enables)
11	git_integration	BANK_GITHUB_INTEGRATION + CREATE GIT REPOSITORY grants
12	network_policy	BANK_NETWORK_POLICY (IP allowlist + auth scope)
13	pii_tags_and_masking	PII_CLASSIFICATION tag + 3 masking policies (string, date, number)
14	account_usage_export	OBSERVABILITY schema + 6 export tasks (3 hourly, 3 daily, all SUSPENDED)
15	risk_platform_deploy_user	BANK_RISK_PLATFORM_DEPLOY service user with role memberships scoped to SD06 + dbt; CREATE SCHEMA on BANK_*_RISK; SELECT/INSERT on BANK_PLATFORM_SETUP.MIGRATIONS.APPLIED
16	unset_risk_platform_network_policy	UNSET NETWORK_POLICY on BANK_RISK_PLATFORM_DEPLOY (15 incorrectly set BANK_NETWORK_POLICY which blocks GitHub-hosted Actions runners; parity with BANK_PLATFORM_DEPLOY which has no network policy)
17	warehouse_grants	USAGE on each warehouse to the roles that run queries against it. 0008 covered DB/schema/table grants but missed warehouses; symptom was `CURRENT_WAREHOUSE=null` after connect + canonical "Object does not exist" on `USE WAREHOUSE`. Per-warehouse mapping per the warehouse selection rule.
18	dbt_role_grants_extension	Widens BANK_DBT_ROLE from "TRANSFORM schemas only, prod databases only" to "any analytical schema in any per-domain database, all envs" via ALL/FUTURE SCHEMAS USAGE + CREATE TABLE/VIEW/DYNAMIC TABLE + ALL/FUTURE TABLES SELECT. Required by SD06 wiki data model which uses per-module schemas (`market`, `metering`, `risk_capital`, ...) not the platform-level `RAW/STAGING/TRANSFORM/GOVERNANCE` quartet.
19	dbt_role_snowflake_db	`GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE BANK_DBT_ROLE` — gives dbt read access to Snowflake's built-in metadata share (ACCOUNT_USAGE views, etc.) for query-attribution tests and dbt project-evaluator-style checks. ACCOUNTADMIN-only grant.
20	risk_role_task_account_usage	Two grants for SD06 risk roles: `EXECUTE TASK ON ACCOUNT` (required for the role to RESUME any Snowflake Task it owns) + `IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE` (for the Task body to read ACCOUNT_USAGE at fire time, since Tasks run as their owning role). Granted to BANK_NONPROD_RISK_ROLE + BANK_PROD_RISK_ROLE. ACCOUNTADMIN-only. Required by MOD-098's 4-hour cost-attribution Task.
21	reports_role_and_grants	New `BANK_REPORTS_OWNER_ROLE` (SD07's cross-domain reader of SD06's METERING) + new service user `BANK_REPORTS_DEPLOY` (key-pair JWT, no network policy). USAGE + CREATE SCHEMA on `BANK__PLATFORM`; cross-DB SELECT on `BANK_DEV_RISK.METERING.` (existing + future). Database-level USAGE only on UAT/PROD risk DBs (schema-level grants land in follow-ups when MOD-098 deploys to those envs). Required by MOD-099 (infrastructure cost reports).
22	alert_metric_grants	`EXECUTE ALERT ON ACCOUNT` granted to BANK_NONPROD_RISK_ROLE + BANK_PROD_RISK_ROLE — required for the role that owns a Snowflake Alert to RESUME it. Same shape as 0020's EXECUTE TASK. Required by MOD-038 (data-quality monitor) and the ADR-049 retrofit of MOD-085/MOD-098 monitor Lambdas.
23	dbt_role_dmf_account	`EXECUTE DATA METRIC FUNCTION ON ACCOUNT` granted to BANK_DBT_ROLE — required for any role that schedules or runs dbt-managed Data Metric Functions. Completes the `EXECUTE *` family of account-level scheduled-execution privileges (TASK in 0020, ALERT in 0022, DMF here). Privilege-name history: the original handoff requested `APPLY METRIC ON ACCOUNT` (not valid SQL) and a guess at `APPLY DATA METRIC FUNCTION` (also not valid). Verified against live Snowflake — the actual privilege is `EXECUTE DATA METRIC FUNCTION`.

Manual one-time bootstrap¶

Before the runner can connect, two human-admin steps in the Snowflake UI as a personal ACCOUNTADMIN:

-- 1. Grant ACCOUNTADMIN to the service user.
USE ROLE ACCOUNTADMIN;
GRANT ROLE ACCOUNTADMIN TO USER BANK_PLATFORM_DEPLOY;

-- 2. Create + assign a bootstrap warehouse so the runner has
--    compute for the migration-tracking INSERT.
CREATE WAREHOUSE IF NOT EXISTS BANK_PLATFORM_SETUP_WH
  WITH WAREHOUSE_SIZE = 'XSMALL'
       AUTO_SUSPEND = 60
       AUTO_RESUME = TRUE
       INITIALLY_SUSPENDED = TRUE;
ALTER USER BANK_PLATFORM_DEPLOY
  SET DEFAULT_WAREHOUSE = BANK_PLATFORM_SETUP_WH
      DEFAULT_ROLE      = ACCOUNTADMIN;

Both steps are documented as a one-time bootstrap because Snowflake itself can't do them via the runner — the chicken-and-egg is real.

Authentication¶

Key-pair JWT (per Snowflake's deprecation of password auth). Service account BANK_PLATFORM_DEPLOY has an encrypted PKCS#8 private key (~/.snowflake/bank-platform-deploy/rsa_key.p8); the public key is attached to the user via ALTER USER.

The runner reads the encrypted PEM, decrypts it in-process via Node's crypto.createPrivateKey, and passes the unencrypted PKCS#8 to snowflake-sdk (which rejects encrypted PEMs with error 404026). Plaintext key never leaves the process. Diagnostic logs print only length + first/last 40 chars (PEM markers — non-sensitive).

Deferred objects¶

These exist as capabilities but the actual operational object is created by a downstream module:

Object	Status	Owner module
GIT REPOSITORY handle	API integration only; repo handle deferred	future dbt module (needs SECRET for GitHub PAT — MOD-045)
SNS notification topic	integration created with placeholder ARN, ENABLED=FALSE	MOD-076 — wires real topic + IAM role
Snowpipe auto-ingest queues	not modelled here	MOD-042 — Snowflake auto-creates queues per pipe
ACCOUNT_USAGE export tasks	created SUSPENDED	MOD-076 — RESUME after S3 bucket confirmed

Tests¶

Layer	Scope	File
Unit	migration ordering, filename shape, checksum determinism	`__tests__/unit/migrations-ordering.test.ts`
Unit	rollback pairing (FR-412)	`__tests__/unit/rollback-pairing.test.ts`
Unit	DT-002 (no transform DDL in setup)	`__tests__/unit/dt-002-no-transform-ddl.test.ts`
Unit	SQL statement splitter	`__tests__/unit/sql-statement-split.test.ts`
Unit	SSM outputs builder (env-conditional values)	`__tests__/unit/ssm-outputs.test.ts`
Integration	live account objects exist	`__tests__/integration/account-objects.test.ts`
Integration	DT-001 GATE — cross-domain access denied	`__tests__/integration/dt-001-gate.test.ts`
Integration	GOV-007 LOG — migrations table immutability	`__tests__/integration/gov-007-log.test.ts`
Integration	SSM publishing — 19 paths populated + tagged	`__tests__/integration/ssm-publishing.test.ts`

SSM outputs¶

Implemented. pnpm provision (and the dedicated pnpm publish-ssm command) reconcile 19 paths under /bank/{env}/snowflake/... to the canonical Snowflake object names declared in src/ssm-outputs.ts. The formal contract lives in bank-wiki/source/pages/design/modules/MOD-102.md §"SSM outputs" — the runner is the implementation of that table.

SSM path	Value	Consumed by
`/bank/{env}/snowflake/account-locator`	`AQUTVUB-DC22437`	All SD06 Pulumi stacks, dbt profiles
`/bank/{env}/snowflake/warehouses/etl`	`PROD_ETL_WH` (prod) / `NONPROD_WH` (dev, uat)	Lambda ingest handlers
`/bank/{env}/snowflake/warehouses/analytics`	`PROD_ANALYTICS_WH` / `NONPROD_WH`	MOD-076, ad-hoc queries
`/bank/{env}/snowflake/warehouses/risk`	`PROD_RISK_WH` / `NONPROD_WH`	MOD-032/033/034/035
`/bank/{env}/snowflake/warehouses/decisions`	`PROD_DECISIONS_WH` / `NONPROD_WH`	MOD-039/040/041/079
`/bank/{env}/snowflake/warehouses/dbt`	`PROD_DBT_WH` / `NONPROD_WH`	All dbt models
`/bank/{env}/snowflake/databases/core`	`BANK_{ENV}_CORE`	MOD-042, any module reading CDC core data
`/bank/{env}/snowflake/databases/kyc`	`BANK_{ENV}_KYC`	Any module reading CDC kyc data
`/bank/{env}/snowflake/databases/aml`	`BANK_{ENV}_AML`	MOD-037 and AML analytics
`/bank/{env}/snowflake/databases/payments`	`BANK_{ENV}_PAYMENTS`	MOD-032/033 (payment flows)
`/bank/{env}/snowflake/databases/credit`	`BANK_{ENV}_CREDIT`	MOD-033 (credit RWA)
`/bank/{env}/snowflake/databases/risk`	`BANK_{ENV}_RISK`	All bank-risk-platform modules (SD06)
`/bank/{env}/snowflake/databases/platform`	`BANK_{ENV}_PLATFORM`	MOD-097/098/099
`/bank/{env}/snowflake/roles/domain-prod`	`BANK_PROD_{DOMAIN}_ROLE` (template)	Per-domain prod Lambda handlers — substitute `{DOMAIN}`
`/bank/{env}/snowflake/roles/domain-nonprod`	`BANK_NONPROD_{DOMAIN}_ROLE` (template)	Per-domain dev/uat Lambda handlers — substitute `{DOMAIN}`
`/bank/{env}/snowflake/roles/dbt`	`BANK_DBT_ROLE`	All dbt runs
`/bank/{env}/snowflake/roles/analytics`	`BANK_ANALYTICS_ROLE`	Cross-domain read consumers
`/bank/{env}/snowflake/integrations/s3`	`BANK_S3_INTEGRATION`	MOD-042 External Table DDL, Snowpipe
`/bank/{env}/snowflake/integrations/sns`	`BANK_SNS_INTEGRATION`	MOD-076 alert wiring

{ENV} = uppercase env (PROD / UAT / DEV). Database names follow BANK_{ENV}_{DOMAIN} — the order matches snowflake/setup/0003_databases.sql, which is the source of truth. The two roles/domain-* paths store the template literally; consumers substitute {DOMAIN} for the domain they represent.

Resolution example¶

// In a consumer Pulumi stack (deploy-time read)
const dbName = aws.ssm.getParameterOutput({
  name: `/bank/${stage}/snowflake/databases/core`,
}).value;

// In a Lambda handler (runtime read; needs MOD-104 secrets-read attached)
const ssm = new SSMClient({ region });
const { Parameter: { Value: tpl } } = await ssm.send(
  new GetParameterCommand({
    Name: `/bank/${stage}/snowflake/roles/domain-prod`,
  }),
);
const role = tpl.replace("{DOMAIN}", "RISK"); // → BANK_PROD_RISK_ROLE

Lifecycle¶

pnpm provision applies pending migrations and auto-publishes SSM at the end (success or no-op). Idempotent — re-running on every invocation keeps SSM converged with the contract.
pnpm publish-ssm reconciles SSM only — useful when SSM is wiped or out of sync without needing to re-apply Snowflake migrations.

Failure semantics¶

If SSM publishing fails after a successful Snowflake apply, the runner logs ssm_publish_failed at ERROR and exits non-zero. Snowflake state is not rolled back — the objects are correctly provisioned; SSM is a publishing artefact. Operator re-runs pnpm publish-ssm to reconcile.

V1 stance — CI deploy users vs network policy¶

BANK_NETWORK_POLICY (created in 0012) is not applied to either deploy service user (BANK_PLATFORM_DEPLOY or BANK_RISK_PLATFORM_DEPLOY). The allowlist in that policy covers AWS NAT EIPs + the admin bastion only — GitHub-hosted Actions runners are dynamically allocated from Microsoft's Azure ranges and are not on the allowlist. The original migration 0012 flagged this explicitly:

"GitHub Actions hosted runner ranges are too broad to allowlist exhaustively. CI/CD that touches Snowflake should use a self-hosted runner with a fixed EIP, OR connect via the bank-platform-cicd OIDC role assuming a Snowflake-deployer Lambda."

Migration 0015 initially set BANK_NETWORK_POLICY on BANK_RISK_PLATFORM_DEPLOY; that immediately blocked the SD06 CI workflow. Migration 0016 reverses it. The user's security perimeter is now:

Snowflake key-pair JWT auth — private key in AWS Secrets Manager at bank-risk-platform/{env}/snowflake-deploy, encrypted with a passphrase that's only stored in the same secret payload.
Role grants scoped to SD06 — BANK_PROD_RISK_ROLE / BANK_NONPROD_RISK_ROLE / BANK_DBT_ROLE. No ACCOUNTADMIN, no cross-domain DDL.
AWS-side Secrets Manager access — requires assuming bank-platform-cicd via OIDC with the trust policy's sub claim restricted to repo:totara-bank/bank-risk-platform:environment:{dev,uat,prod}.

V2 path (per 0012 design intent, not blocking V1): wire a Snowflake-deployer Lambda inside the dev VPC. CI assumes bank-platform-cicd via OIDC, invokes the Lambda, and the Lambda's egress goes through the NAT EIP that IS on BANK_NETWORK_POLICY's allowlist. The network policy can then be re-applied to both deploy users without losing CI access.

Same posture applies to BANK_PLATFORM_DEPLOY (the orchestrator's local-laptop user) for symmetry — both are key-pair-only service users with bounded role grants.

Operational runbook¶

Apply pending migrations: pnpm provision (also publishes SSM)
Plan: pnpm provision:plan
Publish SSM only: pnpm publish-ssm — reconcile SSM without touching Snowflake. Useful after an SSM wipe or to verify the contract from CI.
Rollback (manual): rollback stubs raise an explicit error to prevent accidental destructive runs. To roll back: connect via the Snowflake UI as ACCOUNTADMIN, run snowflake/rollback/<NNNN>.sql, then DELETE FROM BANK_PLATFORM_SETUP.MIGRATIONS.APPLIED WHERE VERSION = NNNN;.
Add a new migration: create snowflake/setup/NNNN_name.sql and paired snowflake/rollback/NNNN_name.sql. The runner enforces contiguous versions and matching pairs at preflight.

Known fixes during initial deploy¶

For posterity (so the next deploy of a new account doesn't relearn):

Snowflake removed NOTIFICATION_PROVIDER = AWS_SQS for inbound integrations. Use AWS_SNS with DIRECTION = OUTBOUND instead; Snowpipe auto-ingest doesn't need a NOTIFICATION INTEGRATION (the pipe creates its own SQS internally).
CREATE GIT REPOSITORY against private GitHub repos requires GIT_CREDENTIALS = <secret>. Without a Snowflake-resident PAT, the repository handle creation fails at clone time. We defer it to a downstream module that has access to the PAT secret.
BANK_SECURITY_ROLE has account-level grants (MANAGE GRANTS, APPLY MASKING POLICY, APPLY TAG) but not schema-level CREATE TAG / CREATE MASKING POLICY. ACCOUNTADMIN must create the schema and grant per-object CREATE privileges before the security role can land tags + policies.
CREATE GIT REPOSITORY is a separate schema-level privilege not bundled into the standard CREATE TABLE/VIEW/DYNAMIC TABLE grant.
The runner needs a default warehouse to record migrations. Bootstrap warehouse BANK_PLATFORM_SETUP_WH is the one-time human step.