MOD-086 — Funds Transfer Pricing Engine¶

Status: Built (pending CI) Repo: bank-risk-platform System: SD06 Phase: 2 (depends on MOD-085 — Built+Deployed) ADRs: ADR-002, ADR-039, ADR-046, ADR-054

The bank's FTP framework in Snowflake. After EOD curve publication by MOD-085, computes a daily nine-tenor TP rate grid (ON, 1M, 3M, 6M, 1Y, 2Y, 3Y, 5Y, 10Y) per jurisdiction by blending market.swap_curve + market.ois_curve and overlaying a Treasury-configured liquidity premium. Joins the grid to every active loan and deposit balance from the CDC replica to produce daily NIM attribution by product code × business line × jurisdiction. A write-back Lambda upserts the TP grid to SD01 Postgres treasury.tp_rates for SD05 (credit decisioning) and SD01 (product rate config).

Functional requirements¶

ID	Requirement	How satisfied
FR-385	Daily TP grid for 9 tenor buckets/jurisdiction from MOD-085 curves + Treasury premium	`transfer_prices` DT, FULL refresh, 30-min target_lag
FR-386	TP grid written to `ftp.transfer_prices` + SD01 `treasury.tp_rates` within 30 min of EOD	DT lag (30 min) + cron schedule firing 35 min after EOD; write-back Lambda upserts both surfaces
FR-387	Daily NIM attribution by product/business_line, allocating TP cost/benefit per balance	`nim_attribution` DT, FULL refresh, 4-hour target_lag
FR-388	Full version history of TP grids — effective_date, curve_source_version, premium_bps; ≥7-year retention	`ftp.writeback_runs` audit log; transfer_prices DT retains daily history; NFR-024 immutability via absent UPDATE/DELETE/TRUNCATE on consumer roles
NFR-014	Snowflake write-back ≤ 60s	Lambda timeout 120s; pg connection pool 1; 18-row UPSERT typically < 5s
NFR-019	RTO ≤ 4h / RPO ≤ 1h Tier-1	Daily run on cron; recovery = next scheduled fire (or manual invoke)
NFR-024	Audit log mutability = 0	`ftp.writeback_runs` SHOW GRANTS test confirms BANK_DBT_ROLE has no UPDATE/DELETE/TRUNCATE

Policies satisfied¶

Policy	Mode	Test
REP-001	CALC	`tests/policy/REP-001-calc.test.ts` (DT shape, refresh mode, target_lag, cluster keys per SD06 wiki) + `tests/integration/snowflake-objects.test.ts` (live config)
CLQ-003	CALC	`tests/policy/CLQ-003-calc.test.ts` — int_tp_rates SQL unconditionally joins LIQUIDITY_PREMIUM_CONFIG and computes `tp_rate_bps = base_curve_bps + premium_bps` (no bypass path)

Snowflake objects¶

Object	Type	Owner	Notes
`BANK_{ENV}_RISK.FTP`	schema	DCM v2 (single-project, MOD-086 only)	New schema; no co-ownership concerns
`FTP.LIQUIDITY_PREMIUM_CONFIG`	table	DCM v2 + pre-dbt seed	18 rows (9 tenors × NZ/AU); Treasury overrides via UPSERT
`FTP.WRITEBACK_RUNS`	table	DCM v2	FR-388 audit log; INSERT-only on consumer roles
`FTP.TRANSFER_PRICES`	Dynamic Table	dbt	FULL refresh, 30-min target_lag, cluster (rate_date, jurisdiction), target.warehouse, post_hook RESUME. Schema matches SD06 wiki §`ftp.transfer_prices` exactly
`FTP.NIM_ATTRIBUTION`	Dynamic Table	dbt	FULL refresh, 4-hour target_lag, cluster (attribution_date, business_line). Schema matches SD06 wiki §`ftp.nim_attribution` exactly
`FTP.V_TRANSFER_PRICES_CURRENT`	view	dbt	ADR-046 §3 published contract
`FTP.V_NIM_ATTRIBUTION_DAILY`	view	dbt	ADR-046 §3 published contract

The wiki does not yet have entries for ftp.liquidity_premium_config or ftp.writeback_runs. See docs/handoffs/MOD-086-sd06-wiki-update.handoff.md for the requested data-model addition.

SSM contract¶

Reads¶

SSM path	From
`/bank/{env}/eventbridge/bank-risk-platform/arn`, `/dlq-arn`	MOD-104
`/bank/{env}/iam/lambda/bank-risk-platform/arn`	MOD-104
`/bank/{env}/observability/adot-layer-arn`	MOD-076
`/bank/{env}/snowflake/account-locator`	MOD-102
`/bank/{env}/snowflake/databases/risk`	MOD-102
`/bank/{env}/snowflake/warehouses/etl`	MOD-102
`/bank/{env}/snowflake/roles/domain-{nonprod,prod}`	MOD-102 (template)
`/bank/{env}/risk-platform/market/swap-curve-table`	MOD-085
`/bank/{env}/risk-platform/market/ois-curve-table`	MOD-085
`/bank/{env}/neon/direct-host`	MOD-103
`/bank/{env}/snowflake/databases/core`	MOD-102 (used to derive Neon DB name)

Plus the Secrets Manager secret bank-risk-platform/{env}/sd01-tp-writeback for the SD01 Postgres write-back username/password (provisioned by MOD-104 / bank-platform secret-bootstrap).

Writes¶

SSM path	Value	Consumed by
`/bank/{env}/risk-platform/ftp/transfer-prices-view`	`FTP.V_TRANSFER_PRICES_CURRENT`	Management accounts, MOD-080, write-back
`/bank/{env}/risk-platform/ftp/nim-attribution-view`	`FTP.V_NIM_ATTRIBUTION_DAILY`	Management accounts, MOD-080
`/bank/{env}/risk-platform/ftp/transfer-prices-table`	`FTP.TRANSFER_PRICES`	History queries
`/bank/{env}/risk-platform/ftp/nim-attribution-table`	`FTP.NIM_ATTRIBUTION`	History queries
`/bank/{env}/risk-platform/ftp/writeback-lambda-arn`	Lambda ARN	Ops

Schedule¶

Time (UTC)	What	Why
22:00 NZT (10:00 UTC)	EOD reference	Curve publication target (MOD-085)
10:30 UTC	`transfer_prices` DT refresh	30-min target_lag from EOD
10:35 UTC	Write-back Lambda (cron)	5-minute buffer past DT refresh
14:00 UTC (4h post-EOD)	`nim_attribution` DT refresh	4-hour target_lag for the larger aggregate

EventBridge contract¶

No custom event publish — positive-flow data sync, not breach detection. The Lambda runs on mod-086-writeback-schedule-{env} cron rule.

Dependencies¶

Module	Why	Status
MOD-042	CDC of `RAW_CDC_CORE.BALANCES` + `RAW_CDC_CORE.ACCOUNT_PRODUCTS` for NIM attribution	Built; lazy-resolved by `stg_balances.sql`
MOD-085	`MARKET.V_SWAP_CURVE`, `V_OIS_CURVE` via dbt source()	Built+Deployed
MOD-102	Snowflake account, RISK database, domain roles	Built
MOD-103	Neon SD01 (bank_core) connection details for write-back	Built
MOD-104	Lambda IAM, EB bus, ADOT, KMS, SNS	Built
bank-core (SD01)	`treasury.tp_rates` table (Flyway migration)	Pending — see handoff

V1 limitations + future work¶

treasury.tp_rates migration pending in bank-core. The write-back Lambda will fail on every invocation with TABLE_NOT_FOUND until bank-core ships the Flyway migration (handoff filed: docs/handoffs/MOD-086-bank-core-treasury-tp-rates-migration.handoff.md). The Lambda fails gracefully with a structured error and records status='FAILED' + error_detail in ftp.writeback_runs. Once the table exists, subsequent invocations succeed without redeploy.
CDC RAW_CDC_CORE.BALANCES / ACCOUNT_PRODUCTS are bootstrap-resilient. When CDC isn't materialised yet, stg_balances returns 0 rows and nim_attribution is empty. No redeploy needed when CDC arrives — the lazy adapter.get_relation branch swaps automatically.
Treasury liquidity premium is a flat 50 bp seed in dev. Treasury policy in real envs reviews quarterly; overrides via UPSERT on FTP.LIQUIDITY_PREMIUM_CONFIG with new effective_from + effective_to set on the prior row.

V1 numerical reference¶

For the integration test fixture (planned out-of-scope this commit):

Inputs:
  NZ 1Y swap rate              = 4.75%   (475 bps base_curve_bps)
  NZ 1Y liquidity premium      = 50 bps   (LIQUIDITY_PREMIUM_CONFIG)
  TP rate                      = 525 bps

  Mortgage book balance        = $100m
  Mortgage customer rate       = 6.55%   (655 bps customer_rate_bps)
  margin_bps                   = 655 - 525 = 130 bps
  Daily NIM                    = $100m × 130 / 10000 / 365 = $3,562

After upsert to SD01 treasury.tp_rates:
  18 rows expected (9 tenors × NZ + AU)
  ON CONFLICT DO UPDATE on (rate_date, tenor_bucket, jurisdiction) —
  duplicate run on the same day converges to the same 18 rows.