SD06 — Risk Platform data model¶
Platform: Snowflake (ap-southeast-2, NZ and AU data residency — see ADR-035) Repo: bank-risk-platform ADRs: ADR-035 (Snowflake account configuration), ADR-003 (CDC pipeline from Neon), ADR-036 (decision result publication) Schema owner: SD06 Snowflake Analytics & Risk Platform
This is the authoritative schema reference for all Snowflake objects in bank-risk-platform. SD06 uses Snowflake Dynamic Tables and materialised views, not Postgres. There are no FK constraints — referential integrity is enforced by the CDC pipeline (MOD-042) and transformation logic. Do not invent alternative table or column names.
ADR-048 exemption: ADR-048 (database-enforced invariants) applies to Neon Postgres databases (SD01–SD05, SD07, SD08) only. Snowflake CHECK and NOT NULL column clauses are declarative syntax and are not enforced at the storage layer. Data quality for SD06 tables is enforced by MOD-038 (data quality framework) and transformation-layer validation in the Dynamic Table SQL.
SD06 is the analytical and risk decisioning layer. It is never queried inline during customer-facing requests — Postgres (SD01–SD05, SD08) is the operational system of record. Snowflake receives facts via CDC (MOD-042), computes meaning (risk scores, ECL, capital ratios, AML analytics), and returns approved decisions to Neon via the decision publication pattern (MOD-079, ADR-036).
Modules writing to or consuming Snowflake in SD06: MOD-032 (LCR/NSFR), MOD-033 (RWA/capital), MOD-034 (stress testing), MOD-035 (IRRBB), MOD-036 (prudential returns), MOD-037 (AML reporting), MOD-038 (data quality), MOD-039 (customer risk score), MOD-040 (churn/health score), MOD-041 (transaction categorisation), MOD-055 (onboarding fraud scoring), MOD-056 (compliance visibility engine), MOD-057 (statistical returns), MOD-058 (breach notification), MOD-060 (FATCA/CRS), MOD-080 (statutory financial reporting).
Snowflake Dynamic Table conventions¶
Each table entry below documents:
- Refresh type: INCREMENTAL (Snowflake row-level incremental) or FULL (complete recompute each run)
- Target lag: Maximum staleness acceptable for downstream consumers
- Cluster key: Snowflake clustering column(s) for micro-partition pruning
Snowflake Dynamic Tables use TIMESTAMP_LTZ (equivalent to TIMESTAMPTZ) for all timestamp columns. Money is NUMBER(18,2). Scores and rates are FLOAT unless exact decimal arithmetic is required, in which case NUMBER(18,8) is used. Confidence scores bounded in [0,1] use NUMBER(5,4) (four decimal places, e.g. 0.0000–1.0000) — FLOAT on DT projections with joins trips Snowflake's change-tracking validator (error 002821 0A000), as established by ADR-056 during MOD-032 and MOD-035.
Target lag sizing convention: target_lag values are not fixed defaults. Each module sizes its lag to its FR latency budget and rounds the budget down by 10× to leave headroom (e.g. FR-237 60-second budget → 1-minute lag for MOD-041; MOD-039 10-minute budget → 1-hour lag). Do not assume 30 minutes is a default.
Schema: risk_capital¶
risk_capital.capital_positions¶
Type: Snowflake Dynamic Table Refresh: INCREMENTAL | Target lag: 1 hour | Cluster key: (position_date, jurisdiction)
Daily and intraday capital adequacy positions computed by MOD-033. Sourced from CDC-replicated core banking postings and credit portfolio data.
| Column | Type | Description |
|---|---|---|
| position_id | VARCHAR | Surrogate key (SHA2 of entity + date) |
| position_date | DATE NOT NULL | Business date |
| jurisdiction | VARCHAR(2) NOT NULL | NZ or AU |
| tier1_capital | NUMBER(18,2) NOT NULL | Common Equity Tier 1 + Additional Tier 1 |
| tier2_capital | NUMBER(18,2) NOT NULL | |
| total_capital | NUMBER(18,2) NOT NULL | tier1 + tier2 |
| risk_weighted_assets | NUMBER(18,2) NOT NULL | Total RWA across credit, market, operational risk |
| cet1_ratio | FLOAT NOT NULL | CET1 / RWA as decimal |
| tier1_ratio | FLOAT NOT NULL | |
| total_capital_ratio | FLOAT NOT NULL | |
| minimum_cet1_requirement | FLOAT NOT NULL | Regulatory minimum (RBNZ / APRA) |
| capital_buffer | NUMBER(18,2) NOT NULL | Surplus above minimum requirement |
| breaches_minimum | BOOLEAN NOT NULL | Alert trigger for prudential reporting |
| model_run_id | VARCHAR NOT NULL | Identifies the MOD-033 run that produced this row |
| calculated_at | TIMESTAMP_LTZ NOT NULL | |
| source_snapshot_lsn | VARCHAR | CDC log sequence number at time of computation |
Notes: Consumed by MOD-036 (prudential return builder) and MOD-033 regulatory dashboard. Alert rows where breaches_minimum = TRUE are picked up by MOD-058 (breach notification engine).
risk_capital.capital_config¶
Type: Parameter table (regular table) Refresh: Rare (regulatory or policy change) | Cluster key: (jurisdiction, ratio_type)
Versioned configuration of regulatory minimums and internal management buffers per (jurisdiction × ratio_type). Created and seeded by MOD-033 DCM at greenfield bootstrap. Runtime overrides follow ADR-046 §4: set prior row's effective_to, insert new row with new effective_from. The Snowflake Alert ALERT_CAPITAL_RATIO_BREACH joins this table against V_CAPITAL_CURRENT at each poll interval.
| Column | Type | Description |
|---|---|---|
| id | VARCHAR | Surrogate key (SHA2 of jurisdiction + ratio_type + effective_from) |
| jurisdiction | VARCHAR(2) NOT NULL | NZ | AU |
| ratio_type | VARCHAR NOT NULL | CET1 | TIER1 | TOTAL_CAPITAL |
| regulatory_minimum | FLOAT NOT NULL | Regulatory floor (RBNZ BS2A / APRA APS 110) as decimal e.g. 0.045 for 4.5% |
| internal_buffer | FLOAT NOT NULL | Internal management buffer above regulatory minimum — FR-207 distinguishes this from regulatory_minimum |
| effective_from | TIMESTAMP_LTZ NOT NULL | Row validity start |
| effective_to | TIMESTAMP_LTZ | NULL = currently active row |
| updated_by | VARCHAR NOT NULL | Role or process that applied the change |
Indexes: (jurisdiction, ratio_type) WHERE effective_to IS NULL — active config lookup
Notes: UPSERT pattern per ADR-046 §4. Dev seed covers NZ CET1/Tier1/Total at RBNZ minimums and AU at APRA APS 110 minimums. The alert reads the active row (effective_to IS NULL) at each 5-minute poll cycle.
risk_capital.capital_run_audit¶
Type: Append-only audit log (regular table) Refresh: INSERT-only | Cluster key: (calculated_at)
Per-run audit log satisfying NFR-024 (immutability). One row per capital_positions DT refresh cycle. Named CAPITAL_RUN_AUDIT (not model_runs) to keep MOD-033 and MOD-032 audit surfaces cleanly separable. NFR-024 immutability enforced by absent UPDATE/DELETE/TRUNCATE grants on consumer roles (BANK_DBT_ROLE) — same negative-grant pattern as MOD-032 model_runs, MOD-035 irrbb_runs, and MOD-039 score_history.
| Column | Type | Description |
|---|---|---|
| run_id | VARCHAR NOT NULL | Unique per DT refresh — matches capital_positions.model_run_id |
| started_at | TIMESTAMP_LTZ NOT NULL | When the refresh began |
| snapshot_lsn | VARCHAR | CDC log sequence number at time of computation |
| exposures_processed | NUMBER(10,0) NOT NULL | Count of credit exposure rows ingested this run |
| rwa_total_nz | NUMBER(18,2) | Total NZ RWA computed this run |
| rwa_total_au | NUMBER(18,2) | Total AU RWA computed this run |
| methodology_version | VARCHAR NOT NULL | Basel mapping version string e.g. v1-standardised |
| calculated_at | TIMESTAMP_LTZ NOT NULL |
risk_capital.v_capital_current¶
Type: View (ADR-046 published contract)
Current capital position per (position_date, jurisdiction) — the most recent row per jurisdiction from capital_positions. Published SSM path: /bank/{env}/risk-platform/capital/current-view. Consumed by MOD-036 (prudential return builder), MOD-058 (breach notification), and the regulatory dashboard. The Snowflake Alert ALERT_CAPITAL_RATIO_BREACH reads this view against capital_config at each poll.
Columns: all columns of risk_capital.capital_positions where position_date = current_date() (latest row per jurisdiction). REP-002 CALC assertion: columns must match RBNZ BS2A / APRA APS 110 return-format schema 1:1.
risk_capital.v_capital_by_portfolio¶
Type: View (ADR-046 published contract)
RWA decomposition by (product, rating_bucket, jurisdiction) — the Pillar 3 disclosure surface. Published SSM path: /bank/{env}/risk-platform/capital/portfolio-view. Consumed by MOD-036 (Pillar 3 section), MOD-105 (when unblocked). CLQ-006 assertion: figures returned here are identical to those feeding MOD-036 regulatory returns — single source of truth.
Columns: (position_date, jurisdiction, product_type, rating_bucket, risk_weight, exposure_amount, rwa_contribution, model_run_id, calculated_at).
risk_capital.liquidity_positions¶
Type: Snowflake Dynamic Table Refresh: INCREMENTAL | Target lag: 1 hour | Cluster key: (position_date, ratio_type)
LCR and NSFR calculations produced by MOD-032. Sourced from nostro positions, payment flows, and funding book data via CDC.
| Column | Type | Description |
|---|---|---|
| position_id | VARCHAR | Surrogate key |
| position_date | DATE NOT NULL | |
| ratio_type | VARCHAR NOT NULL | LCR or NSFR |
| jurisdiction | VARCHAR(2) NOT NULL | |
| hqla_amount | NUMBER(18,2) | High-Quality Liquid Assets (LCR only) |
| net_cash_outflows_30d | NUMBER(18,2) | 30-day stressed outflows (LCR only) |
| lcr_ratio | FLOAT | LCR as decimal (1.0 = 100%) |
| available_stable_funding | NUMBER(18,2) | NSFR numerator |
| required_stable_funding | NUMBER(18,2) | NSFR denominator |
| nsfr_ratio | FLOAT | NSFR as decimal |
| regulatory_minimum | FLOAT NOT NULL | RBNZ / APRA minimum |
| breaches_minimum | BOOLEAN NOT NULL | |
| stress_scenario | VARCHAR | Scenario code if a stress run, otherwise NULL |
| model_run_id | VARCHAR NOT NULL | |
| calculated_at | TIMESTAMP_LTZ NOT NULL |
risk_capital.irrbb_sensitivities¶
Type: Snowflake Dynamic Table Refresh: INCREMENTAL | Target lag: 1 hour | Cluster key: (position_date, jurisdiction, scenario_code)
EVE and NII sensitivity per (jurisdiction × scenario × shock_basis) produced by MOD-035 daily. Sourced from rate-sensitive balance-sheet positions (CDC) and yield curves (MOD-085 market views). Consumed by MOD-034 (stress overlay) and MOD-036 (prudential returns) via SSM-published table reference + V_IRRBB_CURRENT view.
| Column | Type | Description |
|---|---|---|
| position_id | VARCHAR | Surrogate key — SHA2 of (jurisdiction, position_date, scenario_code, shock_basis) |
| position_date | DATE NOT NULL | Business date |
| jurisdiction | VARCHAR(2) NOT NULL | NZ | AU |
| scenario_code | VARCHAR NOT NULL | PARALLEL_UP | PARALLEL_DOWN | STEEPENER | FLATTENER | SHORT_UP | SHORT_DOWN |
| shock_basis | VARCHAR NOT NULL | BCBS | RBNZ | APRA |
| eve_baseline | NUMBER(18,2) | EVE at current rates |
| eve_shocked | NUMBER(18,2) | EVE under scenario shock |
| eve_change | NUMBER(18,2) | shocked − baseline |
| tier1_capital | NUMBER(18,2) | Tier 1 snapshot used for outlier-pct calc |
| eve_change_pct_tier1 | FLOAT | |eve_change| / tier1_capital |
| nii_baseline | NUMBER(18,2) | 12-month NII at current rates |
| nii_shocked | NUMBER(18,2) | 12-month NII under shock |
| nii_change | NUMBER(18,2) | shocked − baseline |
| nii_change_pct | FLOAT | |nii_change| / |nii_baseline| |
| eve_outlier | BOOLEAN NOT NULL | TRUE when eve_change_pct_tier1 > 0.15 — drives FR-215 Snowflake Alert → bank.risk.irrbb_outlier_breach |
| model_run_id | VARCHAR NOT NULL | FR-216 audit correlation key |
| tier1_source | VARCHAR | capital_positions | fixture |
| calculated_at | TIMESTAMP_LTZ NOT NULL |
risk_capital.irrbb_runs¶
Type: Append-only audit log (regular table) Refresh: INSERT-only | Cluster key: (calculated_at)
Per-run audit log for FR-216 (7-year retention) and NFR-024 (immutability). One row per irrbb_sensitivities DT refresh. NFR-024 immutability enforced by absent UPDATE/DELETE/TRUNCATE grants on consumer roles (BANK_DBT_ROLE) — same pattern as MOD-032 model_runs and MOD-039 score_history.
| Column | Type | Description |
|---|---|---|
| model_run_id | VARCHAR NOT NULL | Unique per refresh — matches irrbb_sensitivities.model_run_id |
| methodology_version | VARCHAR NOT NULL | Calc methodology version (e.g. v1-bullet-bcbs-linear) |
| source_snapshot_lsn | VARCHAR | CDC log sequence number at calc time |
| cdc_as_of_date | DATE | CDC snapshot date used by stg_repricing_schedule |
| scenarios_run | NUMBER(10,0) NOT NULL | 6 scenarios × 2 jurisdictions = 12 expected |
| breaches_detected | NUMBER(10,0) NOT NULL | Count of eve_outlier = TRUE rows produced this run |
| tier1_capital_nz | NUMBER(18,2) | NZ Tier 1 used (from MOD-033 capital_positions or fixture) |
| tier1_capital_au | NUMBER(18,2) | AU Tier 1 used |
| tier1_source | VARCHAR NOT NULL | capital_positions | fixture |
| calculated_at | TIMESTAMP_LTZ NOT NULL |
risk_capital.irrbb_shock_scenarios¶
Type: Parameter table (regular table) Refresh: Rare (treasury policy change) | Cluster key: (jurisdiction, scenario_code, shock_basis)
BCBS / RBNZ / APRA IRRBB shock magnitudes per (jurisdiction × scenario × basis), plus per-jurisdiction Tier 1 capital fixture (V1 fallback when MOD-033 capital_positions is absent). Created and seeded by dcm/pre-dbt.sql at greenfield bootstrap so int_shocked_curves can read it at dbt build time. Runtime overrides via UPSERT (set prior row's effective_to, insert new row with new effective_from) per ADR-046 §4.
| Column | Type | Description |
|---|---|---|
| jurisdiction | VARCHAR(2) NOT NULL | NZ | AU |
| scenario_code | VARCHAR NOT NULL | PARALLEL_UP | PARALLEL_DOWN | STEEPENER | FLATTENER | SHORT_UP | SHORT_DOWN |
| shock_basis | VARCHAR NOT NULL | BCBS | RBNZ | APRA |
| short_rate_shock_bp | NUMBER(10,2) NOT NULL | Shock applied to ≤12m repricing bucket |
| long_rate_shock_bp | NUMBER(10,2) NOT NULL | Shock applied to >12m repricing bucket |
| tier1_capital_fixture | NUMBER(18,2) NOT NULL | V1 fallback — informational once MOD-033 deploys |
| effective_from | TIMESTAMP_LTZ NOT NULL | |
| effective_to | TIMESTAMP_LTZ | NULL = current active row |
| updated_by | VARCHAR NOT NULL | |
| updated_at | TIMESTAMP_LTZ NOT NULL |
Schema: risk_customer¶
risk_customer.customer_risk_scores¶
Type: Snowflake Dynamic Table Refresh: INCREMENTAL | Target lag: 15 minutes | Cluster key: (customer_id)
Aggregate customer risk rating produced by MOD-039. Consumes signals from KYC (CDD tier, PEP flag, sanctions history), transaction behaviour, and credit performance.
| Column | Type | Description |
|---|---|---|
| party_id | VARCHAR NOT NULL | Matches SD02 party.parties.party_id |
| score_version | VARCHAR NOT NULL | Model version identifier |
| composite_risk_score | FLOAT NOT NULL | 0–100; higher = higher risk |
| risk_tier | VARCHAR NOT NULL | LOW, MEDIUM, HIGH, CRITICAL |
| cdd_tier_input | VARCHAR | CDD tier at score computation |
| pep_flag_input | BOOLEAN | |
| sanctions_hit_count | INTEGER | Historical match count |
| transaction_velocity_score | FLOAT | Derived from 90-day transaction patterns |
| geographic_risk_score | FLOAT | Based on declared and transactional geography |
| adverse_media_score | FLOAT | From external adverse media screening |
| score_reasons | VARIANT | Array of reason code objects (code, label, contribution) |
| previous_risk_tier | VARCHAR | Tier at prior score run |
| tier_changed | BOOLEAN NOT NULL | Whether tier changed vs. previous run |
| scored_at | TIMESTAMP_LTZ NOT NULL | |
| effective_from | TIMESTAMP_LTZ NOT NULL | |
| effective_to | TIMESTAMP_LTZ | NULL = current score |
Notes: Published back to Neon decision_inbox.decision_result_inbox when tier_changed = TRUE via MOD-079 and ADR-036 decision publication pattern. The published payload follows the decision contract in neon-snowflake-storage.md.
risk_customer.churn_scores¶
Type: Snowflake Dynamic Table Refresh: FULL | Target lag: 7 days (weekly) | Cluster key: (scored_date)
Customer churn and financial health predictions from MOD-040. Used by product and marketing for proactive engagement; not used in any compliance or credit decision gate.
| Column | Type | Description |
|---|---|---|
| customer_id | VARCHAR NOT NULL | |
| scored_date | DATE NOT NULL | |
| churn_probability_90d | FLOAT NOT NULL | Probability of account closure within 90 days |
| churn_segment | VARCHAR NOT NULL | STABLE, AT_RISK, HIGH_CHURN |
| financial_health_score | FLOAT NOT NULL | 0–100; higher = healthier |
| health_segment | VARCHAR NOT NULL | HEALTHY, STRETCHED, STRESSED |
| primary_churn_signal | VARCHAR | Top contributing signal code |
| model_version | VARCHAR NOT NULL | |
| scored_at | TIMESTAMP_LTZ NOT NULL |
risk_customer.churn_model_coefficients¶
Type: parameter table (regular table) Refresh: rare (data-science model update) | Cluster key: (model_version, feature_name)
Versioned logistic regression coefficients used by MOD-040's pure-SQL churn model. Effective-from/to versioned per ADR-046 §4. Seeded at first deploy with 7 rows for v1 model (intercept + 6 features) by MOD-040's dcm/pre-dbt.sql. Data-science team overrides via UPSERT setting effective_to on prior rows and inserting a new model_version row.
| Column | Type | Description |
|---|---|---|
| model_version | VARCHAR NOT NULL | e.g. v1-lr-6feat |
| feature_name | VARCHAR NOT NULL | intercept, product_engagement, balance_trend_90d, txn_frequency_per_week, support_contacts_30d, avg_balance_3m, days_since_last_login |
| coefficient | NUMBER(18,8) NOT NULL | Logistic regression β value |
| feature_mean | NUMBER(18,8) | Population mean for z-score normalisation (NULL for intercept) |
| feature_std | NUMBER(18,8) | Population std dev for z-score normalisation (NULL for intercept) |
| effective_from | TIMESTAMP_LTZ NOT NULL | |
| effective_to | TIMESTAMP_LTZ | NULL = current active row |
| updated_by | VARCHAR NOT NULL | |
| updated_at | TIMESTAMP_LTZ NOT NULL |
Notes: UNIQUE on (model_version, feature_name, effective_from). Read by int_customer_features_normalised and int_churn_logits dbt models.
risk_customer.churn_outreach_runs¶
Type: append-only audit log (regular table) Refresh: INSERT-only | Cluster key: (executed_at)
FR-234/FR-236 audit log of outreach Lambda invocations. One row per weekly cron fire. Records how many HIGH_CHURN new-entrant customers were identified and how many EB events were published. NFR-024 immutability via absent UPDATE/DELETE/TRUNCATE grants on consumer roles (same pattern as MOD-032 model_runs, MOD-039 score_history, MOD-086 writeback_runs).
| Column | Type | Description |
|---|---|---|
| run_id | VARCHAR NOT NULL | Unique per Lambda invocation (mod-040-outreach-{ts}-{uuid_short}) |
| scored_date | DATE NOT NULL | churn_scores.scored_date window this run processed |
| customers_evaluated | NUMBER(10,0) NOT NULL | Rows read from v_churn_at_risk_new_entries |
| events_published | NUMBER(10,0) NOT NULL | EB events successfully published |
| status | VARCHAR NOT NULL | CHECK: SUCCESS, PARTIAL_FAILURE, FAILED |
| error_detail | VARCHAR | Error code + message on FAILED/PARTIAL_FAILURE runs (NULL on SUCCESS) |
| executed_at | TIMESTAMP_LTZ NOT NULL |
risk_customer.categorised_transactions¶
Type: Snowflake Dynamic Table Refresh: INCREMENTAL | Target lag: 1 minute | Cluster key: (customer_id, transaction_date)
Transaction categorisation and merchant enrichment produced by MOD-041. Source is CDC-replicated accounts.postings from SD01. Categories follow a standard taxonomy.
FR-237 latency measurement: The 60-second categorisation SLA clock starts at CDC arrival in Snowflake (post-CDC), not from payment_created in Postgres. The integration test in MOD-041-categorisation-model/tests/integration/ asserts the round-trip from CDC arrival to a visible categorised row. The 1-minute target_lag satisfies FR-237 per scope ruling k-1.
| Column | Type | Description |
|---|---|---|
| posting_id | VARCHAR NOT NULL | Matches SD01 accounts.postings.id |
| customer_id | VARCHAR NOT NULL | |
| jurisdiction | VARCHAR(2) NOT NULL | NZ or AU — per scope ruling k-2 |
| transaction_date | DATE NOT NULL | |
| raw_merchant_descriptor | VARCHAR | Original descriptor from the payment record — provenance for model input audit |
| merchant_id | VARCHAR | FK-by-convention to risk_customer.merchant_directory.merchant_id (location-level) |
| merchant_location_id | VARCHAR | Alias of merchant_id — each directory row IS a location |
| merchant_name | VARCHAR | Enriched location name from merchant directory |
| chain_name | VARCHAR | Chain/brand name (e.g. "Caltex") — nullable per scope ruling k-6 |
| logo_url | VARCHAR | Relative path under CDN base URL (SSM: /bank/{env}/risk-platform/merchant-assets/cdn-base-url) — nullable per k-6 |
| merchant_location_map_url | VARCHAR | Per-location static map image relative path — nullable per k-6 |
| mcc | VARCHAR(4) | ISO 18245 Merchant Category Code (renamed from merchant_mcc per scope ruling k-8) |
| category_l1 | VARCHAR NOT NULL | Top-level category (e.g. GROCERIES, TRANSPORT, INCOME) |
| category_l2 | VARCHAR | Sub-category (e.g. SUPERMARKET, FUEL, SALARY) |
| categorisation_confidence | NUMBER(5,4) NOT NULL | 0.0000–1.0000 confidence score — NUMBER(5,4) pre-empts Snowflake change-tracking float issue per ADR-056 precedent from MOD-032/MOD-035 |
| normalisation_confidence | NUMBER(5,4) NOT NULL | 0.0000–1.0000 confidence in the merchant directory match — surfaced via v_categorised_current so consumers can filter high-confidence matches without re-running the normaliser |
| model_version | VARCHAR NOT NULL | |
| categorised_at | TIMESTAMP_LTZ NOT NULL |
Notes: account_id, amount, currency, and merchant_country are intentionally absent from this DT — money and account context are the source-of-truth in SD01 accounts.postings. MOD-041 is a categorisation overlay, not a self-contained transaction view. v_category_summary_daily joins back to SD01 for amount where needed; the bank.transactions.categorised event payload also omits amount (consumers look it up from SD01).
risk_customer.merchant_directory¶
Type: dbt seed table (regular table)
Refresh: Curated — updated via scripts/generate-merchant-maps.ts + dbt seed CSV refresh | Cluster key: (jurisdiction, chain_name)
Location-level merchant reference table used by MOD-041's merchant normaliser. Each row is a physical location (Caltex Parnell ≠ Caltex Takapuna), not a brand — per scope ruling k-4. v1 ships ~30 NZ/AU locations; expands to the top 100–150 via the curation flow documented in docs/design/MOD-041.md §"Curating the merchant directory". Static map images are pre-generated by scripts/generate-merchant-maps.ts and served from CDN; map_image_url is blank for most rows at initial deploy and populated post-deploy by the curation team.
CDN base URL is published to SSM at /bank/{env}/risk-platform/merchant-assets/cdn-base-url. Consumers prefix this onto the relative paths in logo_url and map_image_url.
| Column | Type | Description |
|---|---|---|
| merchant_id | VARCHAR PRIMARY KEY | Per-location surrogate key |
| chain_name | VARCHAR NOT NULL | Brand/chain name (e.g. "Caltex") |
| location_name | VARCHAR NOT NULL | Location-specific name (e.g. "Caltex Parnell") |
| descriptor_patterns | VARCHAR NOT NULL | Pipe-separated regex patterns for normaliser matching |
| mcc | VARCHAR(4) | ISO 18245 Merchant Category Code |
| address | VARCHAR | Street address |
| suburb | VARCHAR | |
| city | VARCHAR | |
| jurisdiction | VARCHAR(2) NOT NULL | NZ or AU |
| lat | NUMBER(9,6) | Latitude |
| lng | NUMBER(9,6) | Longitude |
| logo_url | VARCHAR | Relative path under CDN base URL — chain-shared, nullable per scope ruling k-6 |
| map_image_url | VARCHAR | Relative path under CDN base URL — per-location static map image, nullable per k-6 |
Schema: risk_aml¶
risk_aml.aml_typology_dashboard¶
Type: Snowflake Dynamic Table Refresh: INCREMENTAL | Target lag: 4 hours | Cluster key: (typology_code, alert_date)
Aggregated AML typology analytics, derived from CDC-replicated aml.aml_alerts and aml.typology_matches. Used by MOD-037 for AUSTRAC/RBNZ reporting and by the compliance analytics team.
| Column | Type | Description |
|---|---|---|
| alert_date | DATE NOT NULL | Date of alert generation |
| typology_code | VARCHAR NOT NULL | |
| typology_description | VARCHAR NOT NULL | |
| jurisdiction | VARCHAR(2) NOT NULL | |
| alert_count | INTEGER NOT NULL | Total alerts for this typology on this date |
| open_count | INTEGER NOT NULL | Alerts not yet closed |
| escalated_count | INTEGER NOT NULL | Alerts escalated to cases |
| sar_filed_count | INTEGER NOT NULL | Cases resulting in SAR/SMR |
| dismiss_rate | FLOAT | dismissed / total |
| average_risk_score | FLOAT | |
| total_transaction_value | NUMBER(18,2) NOT NULL | Aggregate value of transactions in alerts |
| currency | VARCHAR(3) NOT NULL DEFAULT 'NZD' | Reporting currency |
| computed_at | TIMESTAMP_LTZ NOT NULL |
risk_aml.behavioural_model_scores¶
Type: Snowflake Dynamic Table Refresh: INCREMENTAL | Target lag: 15 minutes | Cluster key: (party_id)
ML behavioural risk scores produced by MOD-017 (Snowflake Cortex model). Source is CDC-replicated aml.posting_history_cache, aml.aml_alerts, and risk_customer.customer_risk_scores (MOD-039 signal). Each row is the latest scored output per customer per model version. The write-back Lambda (MOD-017, ADR-047 pattern) delivers champion scores from this table to SD03 Neon aml.behavioural_scores after each refresh.
| Column | Type | Description |
|---|---|---|
| party_id | VARCHAR NOT NULL | Matches SD02 party.parties.party_id |
| model_version | VARCHAR NOT NULL | Cortex model version identifier |
| model_role | VARCHAR NOT NULL | CHAMPION or CHALLENGER |
| score | NUMBER(5,0) NOT NULL | Behavioural risk score 0–1000 (FR-109) |
| risk_tier | VARCHAR NOT NULL | LOW, MEDIUM, HIGH, CRITICAL |
| feature_vector_hash | VARCHAR NOT NULL | SHA256 of feature vector (FR-111) |
| score_reasons | VARIANT NOT NULL | Top contributing features and weights |
| scored_at | TIMESTAMP_LTZ NOT NULL | When Cortex inference ran |
| valid_until | TIMESTAMP_LTZ NOT NULL | Freshness boundary (26h for SCHEDULED, 15min for EVENT) |
| triggered_by | VARCHAR NOT NULL | SCHEDULED or EVENT |
| source_event_id | VARCHAR | Posting event ID for EVENT-triggered re-scores |
Notes: Write-back triggers when scored_at advances for CHAMPION rows. The Snowflake Task triggers the write-back Lambda after each Dynamic Table refresh. FR-112 rollback: reconfigure the Task to the previous model_version and trigger one full refresh — achievable within 30 minutes.
Schema: governance_meta¶
The governance_meta schema holds contract versions, reason-code catalogues, and publication metadata as defined in Neon and Snowflake physical storage and data contracts. See those documents for the full table definitions. Key tables:
governance_meta.reason_code_catalog— canonical reason codes for decision publicationgovernance_meta.contract_versions— semantic versions for each CDC and decision contractgovernance_meta.data_quality_log— MOD-038 data quality check results
Cross-domain data flows¶
SD06 does not hold FK relationships to operational Postgres schemas. Data arrives via CDC (MOD-042) and is consumed in analytical form. The flows are:
| Source | Mechanism | Destination | Consumer module |
|---|---|---|---|
SD01 accounts.postings |
CDC via MOD-042 | raw_cdc_core.* → risk_capital.* |
MOD-033, MOD-032, MOD-080 |
SD02 party.parties, kyc.kyc_checks, kyc.sanctions_results |
CDC via MOD-042 | raw_cdc_kyc.* → risk_customer.customer_risk_scores |
MOD-039 |
SD03 aml.aml_alerts, typology_matches |
CDC via MOD-042 | raw_cdc_aml.* → risk_aml.aml_typology_dashboard |
MOD-037 |
SD04 payments.payments |
CDC via MOD-042 | raw_cdc_payments.* → risk_customer.categorised_transactions |
MOD-041 |
SD05 credit.loan_accounts, ecl_provisions |
CDC via MOD-042 | raw_cdc_credit.* → risk_capital.capital_positions |
MOD-033, MOD-036 |
SD06 risk_customer.customer_risk_scores (when tier changes) |
Decision publication via MOD-079 | Neon decision_inbox.decision_result_inbox |
MOD-079 → SD07 |
SD06 risk_aml.behavioural_model_scores (on each refresh) |
Write-back Lambda via ADR-047 | SD03 Neon aml.behavioural_scores |
MOD-017 |
Schema: market¶
Market reference data, sourced from Snowflake Marketplace provider shares and the NZFMA direct feed. Owned by MOD-085. All tables are Snowflake Dynamic Tables that normalise provider-specific schemas into the canonical market.* form. Analytical consumers (ECL, LCR/NSFR, IRRBB, FTP) read from these tables — they are never coupled to the upstream provider schema. See ADR-039.
market.fx_spot_current¶
Type: Snowflake Dynamic Table Refresh: INCREMENTAL | Target lag: 15 minutes | Cluster key: (currency_pair)
Latest mid-market FX spot rate per currency pair. Written on each Marketplace refresh. Consumed by MOD-085 write-back Lambda (→ SD04 Postgres payments.fx_rates).
| Column | Type | Description |
|---|---|---|
| pair_id | VARCHAR | Surrogate key (SHA2 of base_currency + quote_currency) |
| base_currency | CHAR(3) NOT NULL | ISO 4217 base currency |
| quote_currency | CHAR(3) NOT NULL | ISO 4217 quote currency |
| mid_rate | NUMBER(18,8) NOT NULL | Mid-market spot rate (quote per base) |
| bid_rate | NUMBER(18,8) | Bid rate if provided by supplier |
| ask_rate | NUMBER(18,8) | Ask rate if provided by supplier |
| provider_timestamp | TIMESTAMP_LTZ NOT NULL | Timestamp of rate at source |
| ingested_at | TIMESTAMP_LTZ NOT NULL DEFAULT CURRENT_TIMESTAMP() | |
| provider_id | VARCHAR NOT NULL | Marketplace provider identifier |
market.fx_forward_curve¶
Type: Snowflake Dynamic Table Refresh: FULL | Target lag: 1 hour (EOD) | Cluster key: (currency_pair, rate_date)
FX forward curve tenor points per currency pair. Used by IRRBB (MOD-035) and FX risk mark-to-market.
| Column | Type | Description |
|---|---|---|
| curve_id | VARCHAR | SHA2 of currency_pair + tenor + rate_date |
| base_currency | CHAR(3) NOT NULL | |
| quote_currency | CHAR(3) NOT NULL | |
| tenor | VARCHAR NOT NULL | CHECK: ON, TN, 1W, 1M, 3M, 6M, 1Y |
| forward_points | NUMBER(18,8) NOT NULL | Forward points (add to spot to get forward outright) |
| outright_rate | NUMBER(18,8) NOT NULL | Spot + forward points |
| rate_date | DATE NOT NULL | Business date of curve |
| provider_id | VARCHAR NOT NULL | |
| ingested_at | TIMESTAMP_LTZ NOT NULL DEFAULT CURRENT_TIMESTAMP() |
market.swap_curve¶
Type: Snowflake Dynamic Table Refresh: FULL | Target lag: 2 hours (EOD) | Cluster key: (jurisdiction, tenor, rate_date)
AUD (BBSW-linked) and NZD (BKBM-linked) par swap rates. Primary input to the FTP engine (MOD-086) and IFRS 9 ECL discount curves.
| Column | Type | Description |
|---|---|---|
| curve_id | VARCHAR | SHA2 of jurisdiction + tenor + rate_date |
| jurisdiction | CHAR(2) NOT NULL | NZ or AU |
| currency | CHAR(3) NOT NULL | NZD or AUD |
| tenor | VARCHAR NOT NULL | CHECK: 3M, 6M, 1Y, 2Y, 3Y, 5Y, 7Y, 10Y |
| par_swap_rate | NUMBER(18,8) NOT NULL | Decimal fraction (not percentage) |
| rate_date | DATE NOT NULL | |
| source_version | VARCHAR NOT NULL | Provider data version identifier |
| ingested_at | TIMESTAMP_LTZ NOT NULL DEFAULT CURRENT_TIMESTAMP() |
market.ois_curve¶
Type: Snowflake Dynamic Table Refresh: FULL | Target lag: 2 hours (EOD) | Cluster key: (curve_name, tenor, rate_date)
Overnight Index Swap rates: SOFR (USD), SONIA (GBP), BBSW (AUD), BKBM (NZD). Used by MOD-035 (IRRBB) and MOD-086 (FTP) for risk-free rate discounting.
| Column | Type | Description |
|---|---|---|
| curve_id | VARCHAR | SHA2 of curve_name + tenor + rate_date |
| curve_name | VARCHAR NOT NULL | CHECK: SOFR, SONIA, BBSW, BKBM |
| currency | CHAR(3) NOT NULL | |
| tenor | VARCHAR NOT NULL | CHECK: ON, 1W, 1M, 3M, 6M, 1Y, 2Y, 5Y, 10Y |
| ois_rate | NUMBER(18,8) NOT NULL | Decimal fraction |
| rate_date | DATE NOT NULL | |
| source | VARCHAR NOT NULL | 'marketplace' or 'nzfma_direct' (BKBM only) |
| source_version | VARCHAR NOT NULL | |
| ingested_at | TIMESTAMP_LTZ NOT NULL DEFAULT CURRENT_TIMESTAMP() |
market.benchmark_rates¶
Type: Snowflake Dynamic Table Refresh: FULL | Target lag: 4 hours | Cluster key: (rate_name, rate_date)
Daily benchmark rate fixes: RBA cash rate (OCR), RBNZ Official Cash Rate, BBSW (1M, 3M, 6M), and BKBM. BKBM is sourced via NZFMA direct feed; all others via Marketplace.
| Column | Type | Description |
|---|---|---|
| benchmark_id | VARCHAR | SHA2 of rate_name + rate_date |
| rate_name | VARCHAR NOT NULL | CHECK: RBA_OCR, RBNZ_OCR, BBSW_1M, BBSW_3M, BBSW_6M, BKBM |
| currency | CHAR(3) NOT NULL | |
| jurisdiction | CHAR(2) NOT NULL | |
| rate | NUMBER(18,8) NOT NULL | Decimal fraction |
| rate_date | DATE NOT NULL | Publication date |
| is_carry_forward | BOOLEAN NOT NULL DEFAULT FALSE | TRUE if NZFMA feed unavailable; triggers alert |
| source | VARCHAR NOT NULL | |
| ingested_at | TIMESTAMP_LTZ NOT NULL DEFAULT CURRENT_TIMESTAMP() |
Schema: ftp¶
Funds transfer pricing outputs, produced by MOD-086. Read by management accounts reporting, MOD-080 (statutory financial reporting), and via write-back by SD01 and SD05.
ftp.transfer_prices¶
Type: Snowflake Dynamic Table
Refresh: FULL | Target lag: 30 minutes (EOD, after market.swap_curve updates) | Cluster key: (rate_date, jurisdiction)
Daily TP rate grid across nine tenor buckets per jurisdiction. This is the output of the FTP engine computation. The same grid is written back to SD01 Postgres treasury.tp_rates by the write-back Lambda.
| Column | Type | Description |
|---|---|---|
| tp_id | VARCHAR | SHA2 of rate_date + tenor_bucket + jurisdiction |
| rate_date | DATE NOT NULL | Business date |
| jurisdiction | CHAR(2) NOT NULL | NZ or AU |
| tenor_bucket | VARCHAR NOT NULL | CHECK: ON, 1M, 3M, 6M, 1Y, 2Y, 3Y, 5Y, 10Y |
| tp_rate_bps | NUMBER(6,0) NOT NULL | TP rate in basis points |
| base_curve_bps | NUMBER(6,0) NOT NULL | Underlying swap/OIS rate before premium |
| liquidity_premium_bps | NUMBER(6,0) NOT NULL | Treasury-configured premium for this tenor |
| curve_source_version | VARCHAR NOT NULL | References market.swap_curve source_version |
| computed_at | TIMESTAMP_LTZ NOT NULL | When MOD-086 engine ran |
ftp.nim_attribution¶
Type: Snowflake Dynamic Table Refresh: FULL | Target lag: 4 hours (EOD) | Cluster key: (attribution_date, business_line)
Daily NIM attribution by product segment and business line. Allocates the TP cost/benefit of each active balance to its originating product. Primary source for management accounts and product P&L reporting.
| Column | Type | Description |
|---|---|---|
| attribution_id | VARCHAR | SHA2 of attribution_date + product_code + business_line + jurisdiction |
| attribution_date | DATE NOT NULL | Business date |
| product_code | TEXT NOT NULL | References SD01 account_products.product_code |
| product_type | VARCHAR NOT NULL | CHECK: SAVINGS, TRANSACTION, TERM_DEPOSIT, MORTGAGE, PERSONAL_LOAN, OVERDRAFT |
| business_line | VARCHAR NOT NULL | e.g. RETAIL_NZ, RETAIL_AU, SME |
| jurisdiction | CHAR(2) NOT NULL | |
| average_balance | NUMBER(18,2) NOT NULL | Average balance for the day |
| customer_rate_bps | NUMBER(6,0) NOT NULL | Rate paid/received by customer |
| tp_rate_bps | NUMBER(6,0) NOT NULL | TP rate applied (from ftp.transfer_prices, matched tenor) |
| margin_bps | NUMBER(6,0) NOT NULL | customer_rate_bps - tp_rate_bps (negative = cost to bank for loans) |
| nim_amount | NUMBER(18,2) NOT NULL | average_balance × (margin_bps / 10000) / 365 |
| position_type | VARCHAR NOT NULL | CHECK: ASSET (loans), LIABILITY (deposits) |
| computed_at | TIMESTAMP_LTZ NOT NULL |
ftp.liquidity_premium_config¶
Type: parameter table (regular table) Refresh: rare (Treasury policy review) | Cluster key: (jurisdiction, tenor_bucket)
Treasury-configured liquidity premium overlay per (jurisdiction × tenor_bucket). Effective-from/to versioned per ADR-046 §4. Seeded at first deploy with 18 rows (9 tenors × NZ/AU at 50 bps default) by MOD-086's dcm/pre-dbt.sql. Treasury overrides via UPSERT setting effective_to on the prior row and inserting a new row with effective_from.
| Column | Type | Description |
|---|---|---|
| jurisdiction | CHAR(2) NOT NULL | NZ | AU |
| tenor_bucket | VARCHAR NOT NULL | CHECK: ON, 1M, 3M, 6M, 1Y, 2Y, 3Y, 5Y, 10Y |
| premium_bps | NUMBER(6,0) NOT NULL | Treasury-configured liquidity premium for this tenor |
| effective_from | TIMESTAMP_LTZ NOT NULL | When this premium became active |
| effective_to | TIMESTAMP_LTZ | NULL = current row |
| updated_by | VARCHAR NOT NULL | |
| updated_at | TIMESTAMP_LTZ NOT NULL |
Notes: UNIQUE on (jurisdiction, tenor_bucket, effective_from). Read by MOD-086's int_tp_rates model — the single SQL expression tp_rate_bps = base_curve_bps + premium_bps is the CLQ-003 policy compliance evidence (every TP rate embeds the premium).
ftp.writeback_runs¶
Type: append-only audit log (regular table) Refresh: INSERT-only | Cluster key: (executed_at)
FR-388 audit log of write-back Lambda invocations. One row per Lambda fire (every cron schedule + every retry). Duplicate runs on the same rate_date produce a row with rows_upserted = 0 (UPSERT no-op via ON CONFLICT) — informative audit, not skipped. The idempotency_key = SHA256(rate_date | run_id) lets ops dedupe across retries when needed.
| Column | Type | Description |
|---|---|---|
| run_id | VARCHAR NOT NULL | Unique per Lambda invocation (mod-086-{ts}-{uuid_short}) |
| rate_date | DATE NOT NULL | TP grid rate_date this run wrote |
| idempotency_key | VARCHAR NOT NULL | SHA256(rate_date | run_id) for cross-retry dedupe |
| rows_upserted | NUMBER(10,0) NOT NULL | Postgres rowCount from the UPSERT (0 on no-op) |
| curve_source_version | VARCHAR NOT NULL | From MOD-085's source_version on the TP grid rows used |
| pg_duration_ms | NUMBER(10,0) | UPSERT duration; NULL when failure occurred before Postgres call |
| status | VARCHAR NOT NULL | CHECK: SUCCESS, FAILED |
| error_detail | VARCHAR | Error code + message on FAILED runs (NULL on SUCCESS) |
| executed_at | TIMESTAMP_LTZ NOT NULL |
Notes: NFR-024 immutability via absent UPDATE/DELETE/TRUNCATE grants on consumer roles (BANK_DBT_ROLE has SELECT only). The OWNER role (project_owner = ingest_role) has intrinsic privileges via ownership but is deploy-time-only by convention. Same negative-grant pattern as MOD-032 model_runs, MOD-035 irrbb_runs, and MOD-039 score_history. The status='FAILED' rows include the TABLE_NOT_FOUND case for when SD01's treasury.tp_rates migration hasn't yet shipped.
Schema: metering¶
Platform usage and cost attribution data, owned by MOD-098. Provides the source data for infrastructure cost reporting (MOD-099, BANK_{ENV}_PLATFORM.REPORTS) and gross margin reporting (MOD-080). Raw tables are append-only; Dynamic Tables are derived views.
Raw tables¶
| Table | Type | Owner | Description |
|---|---|---|---|
metering.aws_cost_daily |
Table (append) | MOD-098 | AWS Cost Explorer daily snapshots tagged by tenant_id and module_id. NULL tenant rows feed unattributed_costs. Clustered by (cost_date, tenant_id). |
metering.snowflake_credit_daily |
Table (append) | MOD-098 | Snowflake credit usage attributed to module and warehouse from account_usage.query_history. |
metering.cost_rates |
Table (append-history) | MOD-098 | Versioned rate card with effective_from / effective_to. Ops-managed; append-only. Defines unit prices for all billable resource types. |
metering.usage_events |
Table (append) | MOD-097 | Canonical usage event landing from S3 Iceberg via Firehose. Rows with event_type = 'ENRICHMENT_CALL' are the source for external API cost attribution — no separate external_api_costs table. Read-only from MOD-098's perspective. |
Dynamic Tables¶
| Table | Materialization | Target lag | Description |
|---|---|---|---|
metering.daily_tenant_summary |
INCREMENTAL | 1 hour | Rolled-up cost per tenant / module / resource_type / day. Primary input for billing period calculations. |
metering.billing_period_summary |
FULL | 4 hours | Three-part tariff (customer levy + facility fee + variable consumption) per tenant per billing period. FR-395. Consumed by MOD-099 and MOD-080. |
metering.unit_economics |
FULL | 24 hours | Internal gross margin per tenant. Finance reporting use. |
metering.unattributed_costs |
INCREMENTAL | 1 hour | Unattributed share of daily costs. FR-396 — when share > 2%, unattributed_cost_threshold_exceeded event is emitted. |
Schema: billing¶
Tenant configuration tables for SaaS billing. Provisioned by MOD-098; ownership will transfer to a dedicated billing module when the billing model is defined. MOD-099 does not take ownership of this schema — MOD-099 is a read-only reporting layer in BANK_{ENV}_PLATFORM.
| Table | Type | Description |
|---|---|---|
billing.tenant_modules |
Table (append-history) | Tenant → activated module timeline. Defines which modules each licensee has activated and when. deactivated_at NULL = currently active. Facility fee is computed from active rows. |
billing.tenant_tiers |
Table (append-history) | Tenant → subscription tier with included-unit thresholds. Used by billing_period_summary to apply the correct rate card tier. |
Schema: statutory¶
Statutory financial reporting tables and views. Owned exclusively by MOD-080. Resides in the BANK_{ENV}_RISK Snowflake database (same database as other SD06 schemas). Produces IFRS-format trial balance, P&L, balance sheet, and cash flow statement from the SD01 CDC feed via MOD-042.
DCM-managed tables (append-only, NFR-024)¶
| Table | Description |
|---|---|
statutory.report_runs |
Audit log of every period-close run: period_id, started_at, completed_at, outcome, s3_archive_path, erp_push_log_id. Append-only — no UPDATE/DELETE/TRUNCATE grants. |
statutory.erp_push_log |
One row per ERP push attempt. Lineage columns source_period_id + source_trial_balance_run_id satisfy GOV-006. Append-only; 7-year retention. |
statutory.reconciliation_runs |
Per-period reconciliation result: Snowflake trial balance vs SD01 accounts.balance per GL line; variance_dollars, has_variance flag. Append-only. |
statutory.eb_publish_cursor |
Single-row cursor tracking the last successfully published period_id for the ERP-push EB publisher Lambda. |
dbt seeds¶
| Seed | Rows (v1) | Description |
|---|---|---|
statutory.chart_of_accounts |
~80 | GL classification: (product_code OR internal_account_purpose) → gl_account_code, gl_classification (ASSET/LIABILITY/EQUITY/INCOME/EXPENSE), statement_section (P&L/B&S/CF), ifrs_line_item. Pending CFO review before production use. |
statutory.reporting_periods |
~36 | 12 monthly + 4 quarterly + 1 annual periods for next 3 years; re-rolled annually. |
statutory.internal_account_purposes |
~15 | Maps accounts.accounts.is_internal = true accounts to GL classification. |
Dynamic Tables¶
| Table | Materialization | Target lag | Description |
|---|---|---|
| statutory.trial_balance_period | INCREMENTAL | 15 min | Per (period_id × gl_account_code): opening_balance, period_debits, period_credits, closing_balance. Clustered on (period_id, gl_account_code). Source: stg_postings × stg_accounts × chart_of_accounts. |
| statutory.profit_and_loss_period | INCREMENTAL | 15 min | Per period: income lines, expense lines, net_income. IFRS P&L structure. |
| statutory.balance_sheet_period | INCREMENTAL | 15 min | Per period_end: asset / liability / equity sections; total_assets, total_liabilities, total_equity. Balance sheet equation assets = liabilities + equity enforced in post-test. |
| statutory.cash_flow_statement_period | FULL | 1 hour | Indirect-method cash flow per IFRS: operating / investing / financing sections. FULL refresh — joins opening + closing balance sheet rows per period. |
| statutory.reconciliation_status_current | INCREMENTAL | 5 min | Per (period_id × gl_account_code): trial_balance.closing_balance vs SD01 accounts.balance sum; variance_dollars, has_variance. Tolerance: 0.10 per GL line (rounding artifact threshold); postings within 2 minutes of period_end excluded from variance check to compensate for CDC replication lag. |
Published contract views¶
| View | Consumers | Description |
|---|---|---|
v_trial_balance_period |
MOD-036 (prudential returns), MOD-080 ERP push | Published trial balance contract |
v_pnl_period |
MOD-036, audit | IFRS P&L structure |
v_balance_sheet_period |
MOD-036, audit | IFRS balance sheet structure |
v_cashflow_period |
MOD-036, audit | IFRS cash flow statement |
v_reconciliation_status_current |
MOD-080 reconciliation Lambda, MOD-076 ops dashboard | Current reconciliation status + variance flag |
v_period_close_metrics |
MOD-076 ops dashboard | NFR-010 unattended-success-rate per period |
Numeric conventions: all money columns NUMBER(18,2); confidence/ratio columns NUMBER(18,8); percentages NUMBER(5,4). No FLOAT columns (ADR-056).
S3 immutable archive: bank-{env}-statutory-reports — S3 Object Lock COMPLIANCE mode, 7-year default retention, SSE-KMS, versioning. Every produced statement is archived before the ERP push. FR-264.
Schema: regulatory¶
Compliance visibility tables and views. Owned exclusively by MOD-056 (compliance
visibility engine). Resides in the BANK_{ENV}_RISK Snowflake database.
The WIKI_* tables are populated by the wiki-import Lambda — they mirror the
bank-wiki's compiled obligation chain (regulations → policies → modules →
policies_satisfied entries) into Snowflake. COMPLIANCE_EVIDENCE is the runtime
evidence layer populated by the TASK_AUTO_EVIDENCE_LINKER Snowflake Task.
Wiki-sourced tables (import Lambda, daily sync)¶
| Table | Description |
|---|---|
regulatory.wiki_policies |
All bank-wiki policy entities: policy_code, title, domain, risk_domain, jurisdiction, owner, status, due_date (for time-bound obligations), recurrence (ONE_OFF / MONTHLY / QUARTERLY / ANNUAL). Append-mostly; UPDATE allowed for status transitions by ingest_role. |
regulatory.wiki_regulations |
All bank-wiki regulation entities: reg_id, title, short_title, jurisdiction, regulator, status. |
regulatory.wiki_policy_regulations |
Many-to-many: policy_code × reg_id. Append-only. |
regulatory.wiki_modules |
All bank-wiki module entities: module_id, title, system_id, repo, build_status, deployed. Refreshed on each import run. |
regulatory.wiki_policy_satisfaction |
Layer 1 evidence — each row is one policies_satisfied entry from a module YAML: module_id, policy_code, mode (AUTO/GATE/LOG/ALERT/CALC), description. This is the structural proof of compliance. |
regulatory.wiki_import_log |
Append-only run history: run_id, synced_at, policies_imported, modules_imported, satisfaction_entries_imported, status (SUCCEEDED/FAILED). NFR-024 audit table. |
Runtime evidence and audit tables (append-only, NFR-024)¶
| Table | Description |
|---|---|
regulatory.compliance_evidence |
Layer 2 evidence: obligation_id (references wiki_policies.policy_code), module_id, evidence_type (e.g. REPORT_RUN), evidence_id, period_end_date, inserted_at. Append-only; BANK_DBT_ROLE: SELECT only. |
regulatory.obligation_events |
One row per obligation state transition (CREATED, EVIDENCED, DEADLINE_APPROACHING, OVERDUE). Append-only. |
Dynamic Tables¶
| Table | Materialization | Target lag | Description |
|---|---|---|---|
regulatory.policy_compliance_status |
INCREMENTAL | 1 hour | Per policy_code: satisfying_module_count, built_module_count, deployed_module_count, evidence_count_trailing_12m, last_evidence_at. Primary feed for Streamlit dashboards. |
Published contract views¶
| View | Description |
|---|---|
v_policy_coverage |
FR-248 traceability view — all wiki policies × satisfying modules × mode × build_status × deployed × evidence_count. Policy-first (LEFT JOIN from wiki_policies). Consumer: Streamlit compliance dashboard. |
v_policies_without_satisfaction |
NFR-011 — all wiki_policies rows with no matching row in wiki_policy_satisfaction. Zero-row threshold. Consumer: CCO ops dashboard, ALERT_POLICY_WITHOUT_SATISFACTION. |
v_obligation_auto_evidence_rate |
NFR-010 — evidenced obligations / total active policies per month. Consumer: MOD-076 ops dashboard. |
v_compliance_by_risk_domain |
Compliance coverage grouped by risk_domain: % of policies with ≥1 satisfying module built. Consumer: Streamlit risk domain dashboard. |
v_compliance_by_jurisdiction |
NZ vs AU breakdown: policy count, satisfied count, deployed count. Consumer: Streamlit jurisdiction dashboard. |
v_obligations_due_soon |
FR-247 source: wiki_policies where due_date within OBLIGATION_DEADLINE_ALERT_DAYS (default 90) and no compliance_evidence row with period_end_date in the current obligation period. Consumer: deadline-alerter Lambda. |
Schema: RISK_MGMT — Model risk register (MOD-173)¶
Owner: MOD-173 (model risk register). New schema; no other SD06 module writes here. Declared by MOD-173's DCM project.
ADR-048 note: MODEL_REGISTER_AUDIT is immutable (no UPDATE/DELETE/TRUNCATE grants to any role). MODEL_REGISTER is mutable (UPDATE allowed for lifecycle changes, no DELETE). MODEL_EVIDENCE_PACKS is append-only (INSERT only).
Tables¶
RISK_MGMT.MODEL_REGISTER¶
Mutable current-state inventory of every quantitative model in production. One row per model-bearing module. Curated by the compliance team; DT-013 policy gate checks completeness against MODEL_EVIDENCE_PACKS.
| Column | Type | Notes |
|---|---|---|
| model_id | VARCHAR(20) | PK e.g. MOD-028; no DEFAULT — caller-supplied |
| model_name | VARCHAR | NOT NULL |
| model_type | VARCHAR | CHECK IN ('CREDIT','MARKET','LIQUIDITY','OPERATIONAL','AML','OTHER') |
| tier | NUMBER(1) | CHECK IN (1, 2, 3) — 1=highest supervisory scrutiny |
| owner_role | VARCHAR | Snowflake role or named owner |
| validator_role | VARCHAR | Must differ from owner_role (ADR-059 three-line) |
| regulator | VARCHAR | CHECK IN ('RBNZ','APRA','BOTH','INTERNAL') |
| first_deployed_at | TIMESTAMP_LTZ | |
| last_validated_at | TIMESTAMP_LTZ | |
| next_validation_due | DATE | |
| status | VARCHAR | DEFAULT 'ACTIVE' CHECK IN ('ACTIVE','RETIRED','SUSPENDED','PENDING_VALIDATION') |
| nz_rbnz_approval_required | BOOLEAN | DEFAULT FALSE |
| au_apra_approval_required | BOOLEAN | DEFAULT FALSE |
| updated_at | TIMESTAMP_LTZ | DEFAULT CURRENT_TIMESTAMP() |
GRANTs: SELECT, INSERT, UPDATE to BANK_${ENV}_RISK_ROLE. No DELETE or TRUNCATE to any role. Logical retirement via status = 'RETIRED'.
RISK_MGMT.MODEL_EVIDENCE_PACKS¶
Append-only evidence artefact log. Up to 10 required artefact types per DT-013 per model. The view V_MODEL_REGISTER_CURRENT.evidence_pack_complete is the DT-013 gate surface.
| Column | Type | Notes |
|---|---|---|
| evidence_id | VARCHAR(36) | PK — client-side UUID (no DEFAULT; Snowflake DCM v2 pattern) |
| model_id | VARCHAR(20) | NOT NULL; application-layer FK to MODEL_REGISTER.model_id |
| artefact_type | VARCHAR | CHECK IN ('RECONSTRUCTION_DOC','VALIDATION_REPORT','THIRD_PARTY_PREVALIDATION','BACKTESTING_RESULTS','SENSITIVITY_ANALYSIS','MODEL_LIMITATIONS','DATA_LINEAGE','GOVERNANCE_SIGN_OFF','REGISTER_ENTRY','MONITORING_PLAN') |
| artefact_url | VARCHAR | Link to document store |
| submitted_by | VARCHAR | NOT NULL |
| submitted_at | TIMESTAMP_LTZ | DEFAULT CURRENT_TIMESTAMP() |
| valid_until | DATE |
GRANTs: SELECT, INSERT only to BANK_${ENV}_RISK_ROLE. No UPDATE, DELETE, or TRUNCATE.
RISK_MGMT.MODEL_REGISTER_AUDIT¶
Immutable audit trail of every change to MODEL_REGISTER. ADR-048 Cat 1. No UPDATE, DELETE, or TRUNCATE grants to any role. Append-only by design.
| Column | Type | Notes |
|---|---|---|
| audit_id | VARCHAR(36) | PK — client-side UUID (no DEFAULT; Snowflake DCM v2 pattern) |
| model_id | VARCHAR(20) | NOT NULL |
| changed_by | VARCHAR | NOT NULL DEFAULT CURRENT_USER() |
| changed_at | TIMESTAMP_LTZ | NOT NULL DEFAULT CURRENT_TIMESTAMP() |
| prev_state | VARIANT | Full JSON snapshot of prior register row |
| new_state | VARIANT | Full JSON snapshot of updated register row |
| change_reason | VARCHAR | NOT NULL |
GRANTs: SELECT, INSERT only. No UPDATE, DELETE, or TRUNCATE to any role (enforced at Snowflake grant layer — no trigger possible in DCM; immutability is grant-enforced).
Published views¶
| View | Description |
|---|---|
V_MODEL_REGISTER_CURRENT |
All non-RETIRED models with evidence_artefacts_present count and evidence_pack_complete boolean (COUNT(ep.evidence_id) >= 10). This is the DT-013 gate surface. |
V_TIER1_MODELS |
SELECT * FROM V_MODEL_REGISTER_CURRENT WHERE tier = 1 — mandatory independent pre-validation cohort. |
V_EVIDENCE_PACK_STATUS |
Per-model artefact rollup: which of the 10 artefact types are present / missing. |
V_OVERDUE_VALIDATION |
Models where next_validation_due < CURRENT_DATE() — feeds the DCM ALERT_VALIDATION_OVERDUE alert and bank.risk-platform.model_validation_due_soon EB event. |
Stages¶
| Stage | Purpose |
|---|---|
RISK_MGMT.SEMANTIC_MODELS |
Cortex Analyst semantic model YAML files for MOD-173 |
RISK_MGMT.MOD_173_STREAMLIT_STAGE |
Streamlit app source (ADR-054 pattern) |
Streamlit¶
RISK_MGMT.STREAMLIT_MODEL_REGISTER — internal analyst view. Read-only. Pages: model inventory (all active), evidence pack status per model, overdue validation list. Authorised roles: RISK_INTELLIGENCE_ROLE, COMPLIANCE_ROLE.