MOD-099 — Infrastructure cost reports¶

Purpose¶

A standalone Snowflake-resident cost-reporting data product. Five read-only objects in BANK_{ENV}_PLATFORM.REPORTS that surface MOD-098's metering data in a canonical shape, queryable directly from Snowsight + Streamlit consumers without needing access to the SD06 BANK_{ENV}_RISK database.

FR scope: FR-400 (internal finance view), NFR-024 (audit log immutability — structural, since views and Dynamic Tables can't store mutable state).

Architectural decision: This is a Snowflake-DDL-only module — no Lambdas, no Postgres, no AWS compute, no events. Entire scope is six SQL files + a deploy script + verification.

Architecture¶

                   BANK_DEV_RISK              BANK_DEV_PLATFORM
                   (SD06 / MOD-098)           (SD07 / MOD-099)
                   ┌──────────────────┐       ┌─────────────────────────────────┐
                   │ METERING.        │       │ REPORTS.                        │
                   │  AWS_COST_DAILY ─┼─────► │  V_AWS_COST_DAILY (View)        │
                   │  SNOWFLAKE_      ─┼────► │  V_SNOWFLAKE_CREDITS_DAILY (V)  │
                   │   CREDIT_DAILY   │       │  V_INFRASTRUCTURE_COST_MONTHLY  │
                   │  BILLING_PERIOD_ │       │   (Dynamic Table, FULL, 24h)    │
                   │   SUMMARY *      │       │  V_COST_TREND_90D (View)        │
                   │  UNIT_ECONOMICS *│       │  V_TENANT_COST_SUMMARY (View) ✱ │
                   └──────────────────┘       └─────────────────────────────────┘
                                 │  cross-DB SELECT
                                 │  via BANK_REPORTS_OWNER_ROLE
                                 │  (granted by MOD-102 0021)

* Not yet shipped by MOD-098 — V_TENANT_COST_SUMMARY is a placeholder
  with the canonical column shape but zero rows until those
  upstream Dynamic Tables land.

✱ Placeholder view (see "V_TENANT_COST_SUMMARY status" below).

Five objects in `REPORTS`¶

Object	Type	Source
`V_AWS_COST_DAILY`	View	`BANK_{ENV}_RISK.METERING.AWS_COST_DAILY` (column-projected to canonical shape)
`V_SNOWFLAKE_CREDITS_DAILY`	View	`BANK_{ENV}_RISK.METERING.SNOWFLAKE_CREDIT_DAILY` (aggregated + projected)
`V_INFRASTRUCTURE_COST_MONTHLY`	Dynamic Table — FULL refresh, 24 h target lag, cluster `(period_year, period_month)`	Aggregates the two source views by month
`V_COST_TREND_90D`	View	Daily AWS + SF cost rolled up across the trailing 90 calendar days
`V_TENANT_COST_SUMMARY`	View	Placeholder — column shape only. Real source: `METERING.BILLING_PERIOD_SUMMARY` + `METERING.UNIT_ECONOMICS` once MOD-098 ships those Dynamic Tables

Column shapes match the SD07 wiki §"Snowflake schema: REPORTS" exactly. MOD-098's actual table columns differ from the wiki's canonical names (e.g. service vs service_name, amortized_cost_usd vs cost_usd) — the views project to the canonical shape so downstream consumers don't have to know about MOD-098's internals.

RBAC + connection¶

Cross-domain: SD07 reads from SD06's METERING schema. The role that does this is BANK_REPORTS_OWNER_ROLE — created by MOD-102 migration 0021_reports_role_and_grants.sql. Privileges:

Warehouse: NONPROD_WH (dev/uat) + PROD_ANALYTICS_WH (prod)
CREATE SCHEMA on BANK_{DEV,UAT,PROD}_PLATFORM
Cross-DB SELECT on BANK_DEV_RISK.METERING.* (existing + future tables/views/Dynamic Tables)
DATABASE-level USAGE on BANK_UAT_RISK + BANK_PROD_RISK (schema-level grants are added in follow-up MOD-102 migrations when MOD-098 deploys to those envs)

The deploy service user BANK_REPORTS_DEPLOY holds this role. Key-pair JWT auth only. Per-env credentials in AWS Secrets Manager at bank-platform/{env}/snowflake-reports-deploy.

After MOD-099 creates the REPORTS schema, V001 also issues: - GRANT USAGE on the schema to BANK_ANALYTICS_ROLE - GRANT SELECT on existing views + Dynamic Tables to BANK_ANALYTICS_ROLE

The FUTURE variants of those grants need account-level MANAGE GRANTS (only BANK_SECURITY_ROLE holds it). Follow-up MOD-102 migration 0022 — pending — will add them. V1 doesn't need it because the schema's object set is fixed.

Repository layout¶

MOD-099-infrastructure-cost-reports/
├── package.json
├── tsconfig.json
├── sst.config.ts                                   # publishes /bank/{env}/snowflake/reports-schema
├── snowflake/
│   ├── setup/
│   │   ├── V001__create_reports_schema.sql         # CREATE SCHEMA + analyst grants
│   │   ├── V002__create_aws_cost_daily_view.sql
│   │   ├── V003__create_snowflake_credits_daily_view.sql
│   │   ├── V004__create_infrastructure_cost_monthly_dynamic_table.sql
│   │   ├── V005__create_cost_trend_90d_view.sql
│   │   └── V006__create_tenant_cost_summary_view.sql
│   └── teardown/
│       └── V001-V006__drop_*.sql                   # paired teardowns, reverse order
├── src/
│   ├── connection.ts                               # Snowflake key-pair JWT, decrypt-in-process
│   └── log.ts
├── scripts/
│   └── deploy.ts                                   # reads setup/V*.sql, applies in alphabetical order
└── tests/
    └── verify-objects.ts                           # asserts 5 objects + analyst SELECT

SQL conventions¶

Filenames: Flyway-style V001__name.sql. Numeric ordering, _ (single underscore) inside the human-readable part, __ (double) separating the version from the name.
Templating: deploy script substitutes {ENV} (DEV/UAT/PROD), {ENV_LOWER} (dev/uat/prod), {WAREHOUSE} (NONPROD_WH for dev/uat, PROD_ANALYTICS_WH for prod). Plain string replacement; no quoting risks because all substitutions are bareword identifiers.
Idempotency: every DDL is CREATE OR REPLACE (views, Dynamic Tables) or CREATE … IF NOT EXISTS (schema). No checksum-locked tracking — re-running deploy is safe and applies the latest definition.
Statement splitter in scripts/deploy.ts is single-quote-aware (Snowflake '' escape recognised) so ; inside COMMENT 'foo; bar' clauses doesn't terminate a statement prematurely.

Deploy + verify flow¶

One-time per env (orchestrator, with BANK_PLATFORM_DEPLOY ACCOUNTADMIN creds):

# 1. Apply MOD-102 migration 0021 (creates BANK_REPORTS_OWNER_ROLE + BANK_REPORTS_DEPLOY user)
cd MOD-102-snowflake-account-configuration
SNOWFLAKE_ACCOUNT=AQUTVUB-DC22437 SNOWFLAKE_USER=BANK_PLATFORM_DEPLOY \
  SNOWFLAKE_PRIVATE_KEY_PASSPHRASE=... AWS_PROFILE=bank-dev STAGE=dev \
  pnpm provision

# 2. Provision the BANK_REPORTS_DEPLOY keypair + Secrets Manager entry
AWS_PROFILE=bank-dev STAGE=dev pnpm provision-deploy-user-key \
  --user BANK_REPORTS_DEPLOY \
  --secret bank-platform/dev/snowflake-reports-deploy \
  --default-warehouse NONPROD_WH \
  --default-role BANK_REPORTS_OWNER_ROLE

Per deploy of MOD-099 (CI or operator):

cd MOD-099-infrastructure-cost-reports
AWS_PROFILE=bank-dev STAGE=dev pnpm run deploy        # apply Snowflake DDL
AWS_PROFILE=bank-dev STAGE=dev pnpm run deploy:sst    # publish SSM path
AWS_PROFILE=bank-dev STAGE=dev pnpm verify            # assert 11 checks

V_TENANT_COST_SUMMARY status¶

The 5th object is a placeholder. It declares the canonical column shape from the SD07 wiki spec (10 columns, all NULL-typed to the right precision) but selects zero rows via WHERE 1 = 0. This is because its upstream sources don't exist yet:

BANK_{ENV}_RISK.METERING.BILLING_PERIOD_SUMMARY — promised by MOD-098 design doc as a Dynamic Table; not yet shipped
BANK_{ENV}_RISK.METERING.UNIT_ECONOMICS — same status

MOD-098's published SSM contract listed METERING.V_BILLING_PERIOD_SUMMARY + METERING.V_UNIT_ECONOMICS — those SSM paths return view names that don't exist. When MOD-098 ships those Dynamic Tables, MOD-099 ships a follow-up that re-creates V_TENANT_COST_SUMMARY against the real sources. CREATE OR REPLACE VIEW keeps the migration safe to re-apply.

The placeholder approach was chosen over deferring the object entirely because:

The user spec asks for "all 5 objects exist in all three environments"
Streamlit dashboards built against the column shape today work without modification when the rows arrive
Consumers querying it return zero rows rather than a "view not found" error

SSM contract¶

Path	Value	Consumer
`/bank/{env}/snowflake/reports-schema`	`BANK_{ENV}_PLATFORM.REPORTS`	Streamlit apps, Snowsight workbooks, future cost-dashboard modules

That's the entire SSM surface. No Lambda ARNs (no Lambdas exist), no DLQ, no event-source names.

Tests¶

tests/verify-objects.ts — single-script verification (no Jest/Vitest framework; just tsx):
Schema exists in BANK_{ENV}_PLATFORM
All 5 named objects present with the right type (View vs Dynamic Table)
Row-count spot check on the two source views (>0 in dev/prod; skipped in uat where MOD-098 isn't deployed)
Dynamic Table queryable + refreshed_at populated (or queryable-and-empty when no upstream data)
V_TENANT_COST_SUMMARY queryable (placeholder; expected 0 rows)
BANK_ANALYTICS_ROLE analyst-access check (skipped when run as BANK_REPORTS_DEPLOY since that user doesn't hold the analyst role; must be run by an analyst-role-holding user to actually verify)
11/11 checks pass against deployed dev. Verified 2026-05-01.

V1 deferrals¶

Item	Why	Path forward
UAT + PROD METERING grants for `BANK_REPORTS_OWNER_ROLE`	MOD-098 hasn't deployed to those envs yet — schema doesn't exist	Follow-up MOD-102 migrations `0022_reports_uat_metering_grants.sql`, `0023_reports_prod_metering_grants.sql` when MOD-098 lands
`V_TENANT_COST_SUMMARY` real implementation	MOD-098 hasn't shipped `BILLING_PERIOD_SUMMARY` + `UNIT_ECONOMICS` Dynamic Tables yet	When those land, replace V006's body via `CREATE OR REPLACE VIEW` + redeploy
`FUTURE VIEWS` / `FUTURE DYNAMIC TABLES` analyst grants	Needs MANAGE GRANTS (only `BANK_SECURITY_ROLE` holds it)	Add a SECURITYADMIN-driven MOD-102 migration `0022` if/when REPORTS adds new objects analysts need automatic SELECT on. V1's object set is fixed so this doesn't block
Streamlit / Snowsight consumer dashboards	Out of scope per user spec — REPORTS is the data product, dashboards consume it	Future module, separate repo (or Snowflake-native Streamlit app)
GitHub Actions workflow for CI	Manual deploy is fine for V1; DDL changes infrequent	Wire CI when change frequency justifies it

Reconciliation note¶

The MOD-099 wiki yaml is on the new scope (slug infrastructure-cost-reports, capability mode CALC, FRs FR-400 + NFR-024). The markdown body (bank-wiki/source/entities/modules/MOD-099.md) still has the old billing-dashboard-and-invoice-generation content. Flagged for refresh when convenient — won't block the build or downstream consumers.