MOD-085 — Market rates ingestion & normalisation¶

System: SD06 Snowflake Analytics & Risk Platform Repo: bank-risk-platform Status: In progress Owner: Data & Risk Engineering Build date: 2026-04-30

Purpose¶

MOD-085 is the single point of ingestion for all external market reference data consumed by the bank's analytical, risk, and operational systems. It owns the canonical market.* Snowflake schema that decouples downstream consumers (LCR/NSFR, IRRBB, ECL, FTP) from upstream Marketplace provider schemas, and the operational FX write-back path that publishes spot rates to SD04 Postgres for synchronous payment use.

Functional requirements satisfied¶

ID	Summary
FR-381	Ingest FX spot, FX forward (ON/TN/1W/1M/3M/6M/1Y), swap/OIS (3M–10Y), and benchmark rates (RBA/RBNZ OCR, BBSW) from Snowflake Marketplace shares on a configurable schedule
FR-382	Normalise into `market.*` Dynamic Tables — provider swap requires zero consumer changes
FR-383	Upsert FX spot to SD04 `payments.fx_rates` within 10 minutes of refresh; emit `bank.risk-platform/market_rates_updated`
FR-384	Fetch BKBM from NZFMA each NZ business day; carry-forward with quality flag if unavailable; alert if carry-forward >1 business day
NFR-015	Marketplace → SD04 Postgres write-back p99 ≤ 10 minutes
NFR-019	Tier 1 RTO ≤ 4h / RPO ≤ 1h applies to FX write-back path
NFR-024	Audit columns on `market.*` are immutable (UPDATE/DELETE forbidden)

Architecture¶

┌────────────────────────┐    ┌────────────────────────┐
│ Snowflake Marketplace  │    │  NZFMA HTTPS endpoint  │
│  provider data shares  │    │   (BKBM only)          │
└──────────┬─────────────┘    └──────────┬─────────────┘
           │ inbound shares              │ scheduled HTTPS GET
           ▼                             ▼
┌────────────────────────┐    ┌────────────────────────┐
│ MARKET_RAW.* (provider │    │ NZFMA-fetcher Lambda   │
│  schemas, raw)         │    │ (writes MARKET_RAW row)│
└──────────┬─────────────┘    └──────────┬─────────────┘
           │                             │
           ▼                             ▼
┌──────────────────────────────────────────────────────┐
│ MARKET.* canonical Dynamic Tables (5)                │
│   FX_SPOT_CURRENT  FX_FORWARD_CURVE  SWAP_CURVE      │
│   OIS_CURVE        BENCHMARK_RATES                   │
└──────────┬───────────────────────────────────────────┘
           │
           ├──── FX writeback Lambda (every 5 min)
           │         ├──► SD04 Neon payments.fx_rates (upsert)
           │         └──► EventBridge bank-risk-platform
           │              source=bank.risk-platform
           │              detail-type=market_rates_updated
           │
           ├──── Carry-forward monitor Lambda (daily 18:00 NZT)
           │         └──► SNS alerts topic if BKBM cf > 1 day
           │
           └──── Direct Snowflake reads from MOD-032/035/086

Data model — Snowflake `MARKET` schema¶

Five Dynamic Tables. Full column definitions are in infra/snowflake/dynamic-tables/*.sql and are authoritative against the wiki data model page.

Object	Type	Refresh / Lag	Cluster	Role
`MARKET.FX_SPOT_CURRENT`	Dynamic Table	INCREMENTAL · 15 min	`(BASE_CURRENCY, QUOTE_CURRENCY)`	Private
`MARKET.FX_FORWARD_CURVE`	Dynamic Table	FULL · 1 hour	`(BASE_CURRENCY, QUOTE_CURRENCY, RATE_DATE)`	Private
`MARKET.SWAP_CURVE`	Dynamic Table	FULL · 2 hours	`(JURISDICTION, TENOR, RATE_DATE)`	Private
`MARKET.OIS_CURVE`	Dynamic Table	FULL · 2 hours	`(CURVE_NAME, TENOR, RATE_DATE)`	Private
`MARKET.BENCHMARK_RATES`	Dynamic Table	FULL · 4 hours	`(RATE_NAME, RATE_DATE)`	Private
`MARKET.V_FX_SPOT_CURRENT`	View	always fresh	—	Published contract
`MARKET.V_FX_FORWARD_CURVE`	View	always fresh	—	Published contract
`MARKET.V_SWAP_CURVE`	View	always fresh	—	Published contract
`MARKET.V_OIS_CURVE`	View	always fresh	—	Published contract
`MARKET.V_BENCHMARK_RATES`	View	always fresh	—	Published contract
`MARKET.BKBM_LATEST_RATE`	View	always fresh	—	Internal Lambda lookup
`MARKET.BKBM_CARRY_FORWARD_ALARM`	View	always fresh	—	Internal Lambda lookup

ADR-046 §3 view-as-product (pass 4): every Dynamic Table has a V_* view that is the published contract for downstream consumers. SSM outputs point at the views; downstream modules (MOD-032 LCR/NSFR, MOD-035 IRRBB, MOD-086 FTP) reference MARKET.V_* and never the underlying DT directly. Breaking changes follow the _v2 suffix + deprecation pattern. The two BKBM views from pass 3 are internal Lambda↔dbt helpers (no V_ prefix) — not external contracts.

The two BKBM views were added in ADR-046 pass 3: - BKBM_LATEST_RATE is consumed by the nzfma-bkbm-fetcher Lambda as the carry-forward target when NZFMA is unavailable. Replaces inline SQL formerly embedded in the handler. - BKBM_CARRY_FORWARD_ALARM exposes a single-row (consecutive_carry_forward_days, alarm) summary computed in dbt. The carry-forward-monitor Lambda reads alarm and pages ops when TRUE — no streak-counting in JavaScript.

Money/rates use NUMBER(18,8); timestamps use TIMESTAMP_LTZ. Surrogate keys use SHA2(CONCAT_WS('|', ...)) of the natural key components.

Components¶

Snowflake DCM project — `infra/snowflake/`¶

Declarative manifest deploys schemas, Dynamic Tables, and grants via snow dcm deploy. The DCM project takes four variables (env, warehouse, ingest_role, reader_role) supplied by the deployment pipeline from MOD-102 SSM outputs.

Lambdas — `src/modules/MOD-085/handlers/`¶

Lambda	Schedule	Purpose
`nzfma-bkbm-fetcher`	`cron(30 4 ? * MON-FRI *)` (17:30 NZT)	FR-384 — pull BKBM from NZFMA; on failure, look up the carry-forward target from `MARKET.BKBM_LATEST_RATE` and INSERT a carry-forward row
`fx-spot-writeback`	`rate(5 minutes)`	FR-383 — read `MARKET.FX_SPOT_CURRENT`, upsert SD04 `payments.fx_rates`, emit event
`carry-forward-monitor`	`cron(0 5 ? * MON-FRI *)` (18:00 NZT)	FR-384 — thin alert publisher per ADR-046 §6: read the precomputed `alarm` flag from `MARKET.BKBM_CARRY_FORWARD_ALARM` and page ops on SNS when TRUE. No streak-counting in JavaScript.

Reserved concurrency: 5 per Lambda. Memory: 512 MB. Timeout: 60–90 s.

Domain — `src/modules/MOD-085/domain/`¶

Module	Responsibility
`normalisation.ts`	Provider-row → canonical `FxSpotRate` (FR-382). Whitelists ISO currencies; validates pair; canonicalises 8-decimal rate format and ISO 8601 timestamp
`nzfma-client.ts`	NZFMA HTTPS client. 3 attempts, exponential backoff on 5xx/429, fail-fast on 4xx, ValidationError on payload defects
`fx-rates-upsert.ts`	SD04 Postgres upsert. Single transaction per batch, ROLLBACK on row-level failure

Events — `src/modules/MOD-085/events/`¶

market-rates-updated.ts — emits to bus bank-risk-platform-{env}, source bank.risk-platform, detail-type market_rates_updated. Detail payload carries trace_id, refresh_id (idempotency key), refresh_type, provider_id, pairs_updated[], event_time. TransientError on EventBridge FailedEntryCount > 0 so Lambda retries trigger correctly.

Error code enumeration¶

`error_code`	Class	Source	Recovery
`PROVIDER_FIELD_MISSING`	VALIDATION_FAILURE	normalisation	Reject row; ops review
`PROVIDER_BAD_CCY_PAIR`	VALIDATION_FAILURE	normalisation	Reject row; ops review
`PROVIDER_INVALID_ISO`	VALIDATION_FAILURE	normalisation	Reject row; ops review
`PROVIDER_UNSUPPORTED_CCY`	VALIDATION_FAILURE	normalisation	Reject row; ops review
`PROVIDER_BAD_TIMESTAMP`	VALIDATION_FAILURE	normalisation	Reject row; ops review
`PROVIDER_BAD_RATE`	VALIDATION_FAILURE	normalisation	Reject row; ops review
`PROVIDER_NON_POSITIVE_RATE`	VALIDATION_FAILURE	normalisation	Reject row; ops review
`NZFMA_5XX`	PROVIDER_ERROR (retryable)	NZFMA client	Retry; carry-forward after maxAttempts
`NZFMA_4XX`	PROVIDER_ERROR (non-retryable)	NZFMA client	Surface to ops; carry-forward
`NZFMA_RATE_LIMIT`	PROVIDER_ERROR (retryable)	NZFMA client	Backoff retry
`NZFMA_PAYLOAD_INVALID`	VALIDATION_FAILURE	NZFMA client	Surface to ops; do not carry forward
`NZFMA_UNAVAILABLE`	TRANSIENT_INFRA	NZFMA client	Carry-forward
`BKBM_NO_PRIOR_RATE`	TRANSIENT_INFRA	NZFMA fetcher	Manual ops intervention required
`EVENTBRIDGE_PUBLISH_FAILED`	TRANSIENT_INFRA	event publisher	Lambda retry via EB
`SNOWFLAKE_CONNECT_FAILED`	TRANSIENT_INFRA	shared/snowflake	Lambda retry
`SNOWFLAKE_QUERY_FAILED`	TRANSIENT_INFRA	shared/snowflake	Lambda retry
`ENV_MISSING`	TRANSIENT_INFRA	handlers	Deploy / SSM mismatch — ops
`SSM_PARAM_READ_FAILED`	TRANSIENT_INFRA	shared/ssm	Lambda retry

Event type registry (logger event_type values)¶

bkbm_ingestion_completed, nzfma_fetch_succeeded, nzfma_unavailable_carry_forward, nzfma_payload_invalid, bkbm_carry_forward_alert_fired, bkbm_carry_forward_check_ok, fx_spot_writeback_completed, fx_spot_empty, trace_id_missing_from_upstream.

SSM parameter contract¶

Reads (consumed)¶

SSM path	From
`/bank/{env}/network/vpc-id`	MOD-104
`/bank/{env}/network/private-subnet-ids`	MOD-104
`/bank/{env}/kms/financial/arn`	MOD-104
`/bank/{env}/kms/operational/arn`	MOD-104
`/bank/{env}/eventbridge/bank-risk-platform/arn`	MOD-104
`/bank/{env}/eventbridge/bank-risk-platform/dlq-arn`	MOD-104
`/bank/{env}/iam/lambda/bank-risk-platform/arn`	MOD-104
`/bank/{env}/observability/adot-layer-arn`	MOD-076
`/bank/{env}/xray/sampling/arn`	MOD-104
`/bank/{env}/sns/alerts/arn`	MOD-104
`/bank/{env}/secrets/sd04/payments-writeback-arn`	SD04
`/bank/{env}/snowflake/account-locator`	MOD-102 (proposed contract)
`/bank/{env}/snowflake/mod-085/warehouse`	MOD-102 (proposed contract)
`/bank/{env}/snowflake/mod-085/database`	MOD-102 (proposed contract)
`/bank/{env}/snowflake/mod-085/ingest-role`	MOD-102 (proposed contract)
`/bank/{env}/snowflake/mod-085/ingest-secret-arn`	MOD-102 (proposed contract)

Writes (published)¶

SSM path	Value	Consumed by
`/bank/{env}/risk-platform/market/fx-spot-table`	`MARKET.V_FX_SPOT_CURRENT`	MOD-032, MOD-035, MOD-086
`/bank/{env}/risk-platform/market/fx-forward-table`	`MARKET.V_FX_FORWARD_CURVE`	MOD-035
`/bank/{env}/risk-platform/market/swap-curve-table`	`MARKET.V_SWAP_CURVE`	MOD-086
`/bank/{env}/risk-platform/market/ois-curve-table`	`MARKET.V_OIS_CURVE`	MOD-035, MOD-086
`/bank/{env}/risk-platform/market/benchmark-rates-table`	`MARKET.V_BENCHMARK_RATES`	MOD-035
`/bank/{env}/risk-platform/market/event-source-name`	`bank.risk-platform`	SD04 EB rules
`/bank/{env}/risk-platform/market/writeback-lambda-arn`	FX writeback Lambda ARN	Ops
`/bank/{env}/risk/market-rates/swap-curve-mirror-table`	`credit.swap_rates_mirror`	MOD-116, MOD-163 (break-cost calculators)
`/bank/{env}/risk-platform/market/swap-curve-writeback-lambda-arn`	Swap-curve writeback Lambda ARN	Ops

EventBridge contract¶

Direction	Bus	Source	Detail-type	Schema
Publish	`bank-risk-platform-{env}`	`bank.risk-platform`	`market_rates_updated`	`{ trace_id, refresh_id, refresh_type, provider_id, pairs_updated: [{base_currency, quote_currency, mid_rate, ingested_at}], event_time }`
Publish	`bank-risk-platform-{env}`	`bank.risk-platform`	`swap_curve_updated`	`{ trace_id, refresh_id, curve_date, jurisdictions: ["NZ"\\|"AU"], rows_upserted, event_time }`

Acceptance criteria — policy satisfaction¶

REP-005 LOG (`policies_satisfied[0]`)¶

All market data ingestion events are logged with provider, version, and timestamp.

Test: tests/modules/MOD-085/policy-satisfaction/REP-005-log-immutability.test.ts (structural) + tests/modules/MOD-085/integration/rep-005-runtime-immutability.test.ts (runtime, integration).

Positive: every canonical Dynamic Table declares INGESTED_AT and either PROVIDER_ID or SOURCE_VERSION/SOURCE audit columns. Verified by static SQL inspection at unit level and by row read at integration level.
Negative (immutability, mandatory): each canonical artefact is a Dynamic Table — Snowflake structurally rejects UPDATE/DELETE/TRUNCATE against it. Belt-and-braces REVOKE UPDATE, DELETE, TRUNCATE on ALL and FUTURE tables in MARKET for both ingest_role and reader_role. Runtime test attempts UPDATE and DELETE via each granted role and asserts the engine returns an error.

CLQ-002 CALC (`policies_satisfied[1]`)¶

Swap and OIS curves sourced by this module are inputs to LCR and NSFR Dynamic Tables — data quality failures block liquidity ratio computation.

Test: tests/modules/MOD-085/policy-satisfaction/CLQ-002-calc-correctness.test.ts.

Whitelisted tenor enumeration enforced by Dynamic Table SQL WHERE TENOR IN (...); rows outside the whitelist do not appear in the canonical MARKET.SWAP_CURVE / MARKET.OIS_CURVE, blocking propagation to MOD-032's LCR/NSFR computations.
Boundary cases: zero rate rejected; negative rate rejected; malformed numeric rejected — all by normaliseFxSpotRow.
Provider-swap invariance: byte-identical canonical output for two different provider schemas representing the same underlying rate.
Runtime CALC verification: integration test seeds two provider snapshots and asserts MARKET.SWAP_CURVE produces independently-computed values per tenor.

Observability¶

All Lambdas instrumented with the ADOT layer (SSM /bank/{env}/observability/adot-layer-arn).
extract_trace_context called at the top of every handler.
All structured logs use StructuredLogger and emit the mandatory observability fields (trace_id, correlation_id, module_id, jurisdiction, event_type, party_id, level, timestamp).
EMF custom metrics: bkbm_ingestion_total{outcome}, fx_rates_upserted_total, market_rates_updated_emitted_total, bkbm_carry_forward_consecutive_days.
Dashboard provisioned at bank-{env}-MOD-085 per the dashboard standard.

Idempotency¶

BKBM ingestion: row insert keyed on (rate_name='BKBM', rate_date) — duplicate insert is a no-op via the Dynamic Table de-duplication step in MARKET.BENCHMARK_RATES.
FX writeback: SD04 payments.fx_rates upsert is idempotent on (base_currency, quote_currency). Each invocation generates a fresh refresh_id; downstream consumers de-duplicate on this key if needed.
EventBridge: refresh_id is the idempotency key in the event detail.

Out of scope (explicit non-goals)¶

NZ public-holiday calendar — nextNzBusinessDate uses Mon–Fri only; carry-forward alert covers extended outages.
Provider procurement — handled by ADR-039 outside this module.
ECL or LCR computation — consumes MARKET.SWAP_CURVE / MARKET.OIS_CURVE but lives in MOD-031 / MOD-032.
TP rate computation — MOD-086 reads the swap and OIS curves but computes its own grid.

Open items at handoff¶

MOD-102 SSM contract — MOD-102.yaml carries no outputs: block; the SSM paths under /bank/{env}/snowflake/* proposed in this design are currently a draft contract awaiting MOD-102 confirmation. If MOD-102 publishes different paths, update infra/pulumi/modules/MOD-085/lambdas.ts accordingly. Captured in handoff.
NZFMA endpoint — DEFAULT_NZFMA_CONFIG.endpoint is a placeholder URL. Confirm with the NZFMA agreement document and adjust before dev deploy.
NZ public-holiday calendar — flagged for future enhancement when MOD-076 calendar service is available.

v2 capability requests received from sibling repos¶

Property index feed (from bank-credit / MOD-115, 2026-05-08)¶

Source: docs/handoffs/processed/2026-05-08/MOD-085-property-index-feed-from-mod115.handoff.md Status: Acknowledged; deferred to v2. Non-blocking for MOD-115 v1 ship.

MOD-115 (LVR engine, bank-credit) needs a quarterly property price index by suburb/region to drive automatic collateral revaluation. The ask is for MOD-085 to add a new canonical Snowflake table MARKET.PROPERTY_INDICES (jurisdiction × region × suburb × property_subtype × index_period × index_value × index_change_pct) refreshed quarterly, plus a write-back Lambda emitting bank-platform.property_index_updated on the bank-platform bus.

v2 work, when MOD-085 next bumps:

Add a Marketplace provider integration for property indices (or source from RBNZ / Stats NZ + APRA / ABS open data).
Add property_indices.sql dbt model (Dynamic Table) under models/MOD-085-market-rates-ingestion/ with the same FULL refresh shape as the FX/swap dynamic tables.
Publish MARKET.V_PROPERTY_INDICES view contract via SSM at /bank/{env}/risk-platform/market/property-indices-view.
Add a write-back Lambda (clone of fx-spot-writeback) that emits bank-platform.property_index_updated on the bank-platform bus. New SSM path: /bank/{env}/risk-platform/market/property-index-writeback-lambda-arn.
Cross-bus IAM grant required from MOD-104: BankCreditRole needs events:PutEvents on the bank-platform bus AND BankRiskPlatformRole needs events:PutEvents on the bank-platform bus (already has it for FX writeback path — confirm).
MOD-115 then ships its consume-property-index-updated Lambda subscribed cross-bus + revalues collateral via MOD-066.

Until v2 lands: MOD-115 v1 uses MOD-066 manual revaluations only. Property values can stagnate; LVR drift biases conservative in NZ/AU (prices typically rise) so v1 may under-report breaches — acceptable risk per MOD-115 k-3 ruling.

Swap-curve write-back to bank_credit (SHIPPED 2026-05-18 per ADR-046 ruling)¶

Source: docs/handoffs/processed/2026-05-08/MOD-085-swap-rate-mirror.handoff.md (original v2 ask) Ruling: bank-wiki issue #26 (ADR-046 ruling 2026-05-18) — Postgres mirror is the canonical pattern; the SigV4 Function URL alternative MOD-163 had specced was retracted as an ADR-046 violation. Status: SHIPPED in pipeline 14409287903 (commits 7a3746c → 0d1d13d → 7297431). The text below is the original v2 spec, preserved for context — see the as-shipped surface in the SSM/EventBridge tables above.

MOD-116 v1 still uses locked AppConfig defaults pending its own update to read credit.swap_rates_mirror (matching the MOD-163 revision filed under bank-credit/docs/handoffs/mod-163-swap-rate-mirror-revision.handoff.md).

MOD-116 (break-cost engine, bank-credit) needs the canonical wholesale swap rate from MARKET.SWAP_CURVE mirrored into bank_credit.credit.swap_rates_mirror so the FR-526 break-cost calculation can read live curve points at quote time:

break_cost = max(0,
    (contract_rate − reinvestment_rate)
    × outstanding_balance
    × remaining_fixed_days / 365)

v2 work, when MOD-085 next bumps (can land in the same PR as the property-index follow-on if helpful — both are MOD-085 write-back paths):

Add a write-back Lambda (clone of fx-spot-writeback) that runs on each MARKET.SWAP_CURVE refresh (EOD). Reads (jurisdiction × tenor_months × rate_pct × curve_date) and UPSERTs into the new credit.swap_rates_mirror table on bank-credit Postgres.
Curve_date is the natural idempotency key — the upsert is INSERT … ON CONFLICT (jurisdiction, tenor_months, curve_date) DO UPDATE SET rate_pct = EXCLUDED.rate_pct.
Emit bank.risk-platform.swap_curve_updated on the bank-risk-platform bus on each successful upsert. Payload: curve_date + tenors_upserted (count) + standard envelope.
Cross-domain Postgres write: MOD-085's Lambda needs to reach the bank-credit Neon pooler. MOD-104 already provisions the pooler host SSM at /bank/{env}/neon/pooler-host for bank-platform Lambdas; bank-risk-platform Lambdas may need a separate IAM grant + secret reference. Confirm with MOD-104 at v2 time.
New SSM path: /bank/{env}/risk-platform/market/swap-curve-writeback-lambda-arn.
MOD-116 swaps its StaticSwapRateLookup for a PostgresSwapRateLookup on the same PR — small follow-on.

Until v2 lands: MOD-116 v1 uses locked AppConfig defaults (per the v2 capability request's table — NZ 1Y..5Y, AU 1Y..5Y; CFO sign-off via CRE-006 §9). The break_cost calculation works correctly; rates will drift from market over time, with the AppConfig override layer preserved as the per-env adjustment surface.

Combined v2 backlog summary¶

When MOD-085 next bumps, two write-back paths land together:

Capability	Consumer	Source
`MARKET.PROPERTY_INDICES` + property_index_updated EB event	MOD-115 (LVR engine)	`processed/2026-05-08/MOD-085-property-index-feed-from-mod115.handoff.md`
Swap-curve mirror to `credit.swap_rates_mirror` + swap_curve_updated EB event	MOD-116 (break-cost engine)	`processed/2026-05-09/MOD-085-swap-rate-mirror.handoff.md`

Both clone the existing fx-spot-writeback pattern. Same Pulumi infra shape (new Lambda + new SSM output + new EB event archive). Cross-bus / cross-domain IAM grants from MOD-104 are the only shared friction point — file a single combined handoff at v2 build time.