BI Lead Journey: Regulatory Reporting and Portfolio Analytics

Persona: Rachel, BI Lead at Meridian Bank (Finance and Regulatory Reporting team, 8 years experience) Objective: Build the regulatory reporting suite and portfolio analytics dashboards, ensuring 100% accuracy for Basel III submissions and enabling self-service analytics for relationship managers Timeline: Quarterly regulatory cycle with daily portfolio monitoring Datasets: accounts (500K), transactions (50M), market_data (9M), regulatory_reports (2,400), credit_applications (200K)

Stage 1: Ingestion

Rachel needs comprehensive, audit-trailed data ingestion to satisfy regulatory traceability requirements. Every data element in a regulatory report must be traceable to its source.

Configuring Audit-Logged Ingestion

Rachel configures data sources with full lineage tracking enabled:

{
  "sources": [
    {
      "name": "core-banking-regulatory",
      "type": "postgres",
      "tables": [
        "accounts", "loans", "deposits", "off_balance_sheet",
        "capital_instruments", "collateral", "provisions"
      ],
      "sync_schedule": {"frequency": "daily", "time": "01:00"},
      "lineage": {
        "enabled": true,
        "track_row_level": true,
        "audit_table": "audit.ingestion_lineage"
      }
    },
    {
      "name": "risk-systems",
      "type": "postgres",
      "tables": [
        "risk_weights", "credit_ratings", "pd_scores",
        "lgd_estimates", "ead_calculations"
      ],
      "sync_schedule": {"frequency": "daily", "time": "01:30"},
      "lineage": {"enabled": true, "track_row_level": true}
    },
    {
      "name": "market-data-feed",
      "type": "rest_api",
      "endpoint": "bloomberg-market-data",
      "instruments": ["equities", "bonds", "fx", "commodities"],
      "sync_schedule": {"frequency": "daily", "time": "18:00", "timezone": "America/New_York"},
      "lineage": {"enabled": true}
    }
  ]
}

Data Lineage for Regulatory Traceability

Every synced record carries lineage metadata:

┌──────────────┐   ┌──────────────┐   ┌──────────────┐   ┌──────────────┐
│ Source System │──▶│  Ingestion   │──▶│  Transform   │──▶│  Regulatory  │
│              │   │  (timestamped│   │  (versioned  │   │  Report      │
│ Core Banking │   │   audit log) │   │   SQL logic) │   │  (signed)    │
│ Record ID:   │   │  Sync ID:    │   │  Pipeline    │   │  Report ID:  │
│  LN-00482    │   │  SYN-29481   │   │  Run ID:     │   │  REG-Q1-2026 │
│              │   │  Row Hash:   │   │  PL-8841     │   │  Line Item:  │
│              │   │  a3f2c...    │   │  Version: 14 │   │  CET1-42     │
└──────────────┘   └──────────────┘   └──────────────┘   └──────────────┘

This end-to-end lineage means any number in a regulatory report can be traced back to the exact source record, sync event, and transformation logic that produced it -- a requirement for Basel III submissions.

Stage 2: Discovery

Rachel maps all data elements required for regulatory reporting and tags sensitive columns.

Regulatory Data Element Mapping

Using the Data Catalog, Rachel searches for tables needed for Basel III capital calculations:

Regulatory Requirement	Data Elements Needed	Source Tables	Completeness
CET1 Capital	Retained earnings, AOCI, share capital	`capital_instruments`, `gl_balances`	100%
Risk-Weighted Assets	Exposure, risk weight, collateral	`loans`, `risk_weights`, `collateral`	99.8%
Liquidity Coverage Ratio	HQLA, net cash outflows (30d)	`deposits`, `market_data`, `off_balance_sheet`	99.2%
Net Stable Funding Ratio	Available/required stable funding	`deposits`, `loans`, `capital_instruments`	99.5%
NPL Ratio	Non-performing loans, total loans	`loans`, `provisions`	100%

PII Tagging and Masking

Rachel tags columns that contain personally identifiable information. The Governance Service enforces masking policies automatically:

{
  "governance_tags": [
    {
      "table": "accounts",
      "columns": ["customer_name", "ssn", "date_of_birth", "address"],
      "classification": "PII",
      "masking_policy": "hash_sha256",
      "visible_to": ["compliance_officer", "regulatory_reporting"]
    },
    {
      "table": "loans",
      "columns": ["borrower_name", "tax_id"],
      "classification": "PII",
      "masking_policy": "partial_mask",
      "visible_to": ["compliance_officer"]
    }
  ]
}

Data Profiling for Capital Calculations

Rachel profiles the risk_weights table to validate Basel III risk weight assignments:

Asset Class	Count	Risk Weight Range	Mean	Issues
Sovereign	1,204	0% - 150%	12.3%	None
Corporate	28,419	20% - 150%	78.2%	14 unrated (assigned 100%)
Retail	312,847	35% - 75%	62.1%	None
Mortgage	89,201	35% - 100%	41.8%	None
Equity	2,104	100% - 300%	142.5%	3 missing classification

Stage 3: Query

Rachel defines semantic layer metrics that exactly match Basel III regulatory formulas. These metrics become the single source of truth for all regulatory reporting.

Semantic Layer Metric Definitions

{
  "semantic_layer": {
    "domain": "regulatory_reporting",
    "metrics": [
      {
        "name": "cet1_ratio",
        "display_name": "CET1 Capital Ratio",
        "description": "Common Equity Tier 1 capital as percentage of risk-weighted assets (Basel III Art. 92)",
        "formula": "cet1_capital / risk_weighted_assets",
        "unit": "percentage",
        "components": {
          "cet1_capital": "SUM(share_capital + retained_earnings + aoci - regulatory_deductions)",
          "risk_weighted_assets": "SUM(exposure_amount * risk_weight)"
        },
        "regulatory_reference": "CRR Article 92(1)(a)",
        "minimum_threshold": 4.5,
        "target_threshold": 10.5
      },
      {
        "name": "lcr",
        "display_name": "Liquidity Coverage Ratio",
        "description": "High quality liquid assets / total net cash outflows over 30-day stress scenario",
        "formula": "hqla / net_cash_outflows_30d",
        "unit": "percentage",
        "regulatory_reference": "CRR Article 412",
        "minimum_threshold": 100.0
      },
      {
        "name": "nsfr",
        "display_name": "Net Stable Funding Ratio",
        "description": "Available stable funding / required stable funding",
        "formula": "available_stable_funding / required_stable_funding",
        "unit": "percentage",
        "regulatory_reference": "CRR Article 428a",
        "minimum_threshold": 100.0
      },
      {
        "name": "npl_ratio",
        "display_name": "Non-Performing Loan Ratio",
        "description": "Non-performing loans as percentage of total gross loans",
        "formula": "non_performing_loans / total_gross_loans",
        "unit": "percentage",
        "target_threshold": 3.0
      }
    ]
  }
}

Validated Regulatory SQL

Rachel writes SQL that mirrors the Basel III capital adequacy calculation. This SQL is version-controlled and approved by the compliance team:

-- Basel III CET1 Capital Ratio Calculation
-- Regulatory Reference: CRR Article 92(1)(a)
-- Version: 14 | Approved by: Compliance Committee 2026-01-15
-- Audit: Every execution logged to audit.regulatory_calculations
 
WITH cet1_components AS (
    SELECT
        reporting_date,
        SUM(CASE WHEN instrument_type = 'COMMON_SHARES' THEN amount ELSE 0 END)
            AS share_capital,
        SUM(CASE WHEN instrument_type = 'RETAINED_EARNINGS' THEN amount ELSE 0 END)
            AS retained_earnings,
        SUM(CASE WHEN instrument_type = 'AOCI' THEN amount ELSE 0 END)
            AS aoci,
        SUM(CASE WHEN instrument_type IN ('GOODWILL', 'INTANGIBLES', 'DTA_DEDUCTION')
            THEN amount ELSE 0 END)
            AS regulatory_deductions
    FROM capital_instruments
    WHERE reporting_date = :report_date
    GROUP BY reporting_date
),
rwa_calculation AS (
    SELECT
        reporting_date,
        SUM(l.exposure_amount * rw.risk_weight / 100.0)
            AS risk_weighted_assets
    FROM loans l
    JOIN risk_weights rw ON l.asset_class = rw.asset_class
        AND l.credit_rating = rw.credit_rating
    WHERE l.reporting_date = :report_date
    GROUP BY reporting_date
)
 
SELECT
    c.reporting_date,
    c.share_capital,
    c.retained_earnings,
    c.aoci,
    c.regulatory_deductions,
    (c.share_capital + c.retained_earnings + c.aoci - c.regulatory_deductions)
        AS cet1_capital,
    r.risk_weighted_assets,
    ROUND(100.0 *
        (c.share_capital + c.retained_earnings + c.aoci - c.regulatory_deductions)
        / r.risk_weighted_assets,
    2) AS cet1_ratio_pct,
    CASE
        WHEN (c.share_capital + c.retained_earnings + c.aoci - c.regulatory_deductions)
            / r.risk_weighted_assets >= 0.105 THEN 'ABOVE_TARGET'
        WHEN (c.share_capital + c.retained_earnings + c.aoci - c.regulatory_deductions)
            / r.risk_weighted_assets >= 0.045 THEN 'ABOVE_MINIMUM'
        ELSE 'BELOW_MINIMUM'
    END AS compliance_status
FROM cet1_components c
JOIN rwa_calculation r ON c.reporting_date = r.reporting_date;

Component	Amount ($M)	Notes
Share Capital	$1,240	--
Retained Earnings	$890	--
AOCI	-$45	Unrealized losses on AFS securities
Regulatory Deductions	-$112	Goodwill + intangibles
CET1 Capital	$1,973	--
Risk-Weighted Assets	$16,041	--
CET1 Ratio	12.3%	Target: >10.5%, Minimum: >4.5%

Stage 4: Orchestration

Rachel builds regulated reporting pipelines with approval gates. Regulatory reports cannot be submitted without sign-off.

Monthly Basel III Reporting Pipeline

┌──────────┐   ┌──────────┐   ┌──────────┐   ┌──────────┐   ┌──────────┐
│ Extract  │──▶│ Validate │──▶│ Calculate│──▶│ Review   │──▶│ Submit   │
│          │   │          │   │          │   │          │   │          │
│ All      │   │ Quality  │   │ Run      │   │ Human    │   │ Generate │
│ regulated│   │ gates    │   │ Basel    │   │ sign-off │   │ PDF,     │
│ sources  │   │ (zero    │   │ formulas │   │ required │   │ XBRL     │
│          │   │ tolerance)│   │          │   │          │   │          │
└──────────┘   └──────────┘   └──────────┘   └──────────┘   └──────────┘
    T+1            T+2            T+3          T+4 to T+8      T+10
                                                              (deadline)

Pipeline Configuration

{
  "pipeline": {
    "name": "basel-iii-monthly-report",
    "schedule": "0 1 2 * *",
    "owner": "rachel@meridian.bank",
    "tags": ["regulatory", "basel-iii", "monthly", "compliance"],
    "sla_deadline_business_days": 10,
    "stages": [
      {
        "name": "extract",
        "type": "sync_trigger",
        "sources": ["core-banking-regulatory", "risk-systems", "market-data-feed"],
        "timeout_minutes": 60,
        "audit_log": true
      },
      {
        "name": "data_quality",
        "type": "data_quality",
        "checks": [
          {
            "table": "capital_instruments",
            "expectation": "expect_column_values_to_not_be_null",
            "column": "instrument_type",
            "severity": "critical",
            "tolerance": 0.0
          },
          {
            "table": "loans",
            "expectation": "expect_column_values_to_not_be_null",
            "column": "risk_weight",
            "severity": "critical",
            "tolerance": 0.0
          },
          {
            "table": "risk_weights",
            "expectation": "expect_column_values_to_be_between",
            "column": "risk_weight",
            "min_value": 0,
            "max_value": 1250,
            "severity": "critical"
          },
          {
            "table": "capital_instruments",
            "expectation": "expect_table_row_count_to_be_between",
            "min_value": 10,
            "severity": "critical"
          }
        ],
        "on_failure": "halt_pipeline_and_alert",
        "alert_channels": ["email:rachel@meridian.bank", "slack:#reg-reporting"]
      },
      {
        "name": "calculate",
        "type": "sql_transform",
        "queries": [
          "regulatory/cet1_calculation.sql",
          "regulatory/lcr_calculation.sql",
          "regulatory/nsfr_calculation.sql",
          "regulatory/npl_calculation.sql",
          "regulatory/leverage_ratio.sql"
        ],
        "output_schema": "regulatory_output",
        "version_tracking": true,
        "depends_on": ["data_quality"]
      },
      {
        "name": "review_gate",
        "type": "manual_approval",
        "approvers": ["rachel@meridian.bank", "cfo@meridian.bank"],
        "required_approvals": 2,
        "timeout_hours": 48,
        "depends_on": ["calculate"]
      },
      {
        "name": "generate_reports",
        "type": "report_generation",
        "formats": ["pdf", "xbrl", "csv"],
        "template": "basel-iii-template-v3",
        "output_path": "regulatory/submissions/",
        "versioned": true,
        "depends_on": ["review_gate"]
      }
    ]
  }
}

Reporting Calendar

Report	Frequency	Deadline	Pipeline	Status
Basel III Capital Adequacy	Monthly	T+10 business days	`basel-iii-monthly-report`	Active
Liquidity Coverage Ratio	Daily	T+1	`lcr-daily-report`	Active
Net Stable Funding Ratio	Quarterly	T+15 business days	`nsfr-quarterly-report`	Active
Stress Test Data	Quarterly	T+30 business days	`stress-test-pipeline`	Active
Large Exposures	Quarterly	T+20 business days	`large-exposures-report`	Active

Stage 5: Analysis

Rachel validates regulatory calculations against manually computed results to ensure zero errors.

Reconciliation Against Manual Calculations

The compliance team maintains a parallel spreadsheet for key calculations. Rachel runs automated reconciliation:

-- Reconcile automated CET1 calculation vs manual spreadsheet values
SELECT
    auto.reporting_date,
    auto.cet1_capital AS automated_cet1,
    manual.cet1_capital AS manual_cet1,
    ABS(auto.cet1_capital - manual.cet1_capital) AS difference,
    ROUND(100.0 * ABS(auto.cet1_capital - manual.cet1_capital)
        / manual.cet1_capital, 6) AS pct_difference,
    CASE
        WHEN ABS(auto.cet1_capital - manual.cet1_capital) < 0.01
        THEN 'MATCH'
        ELSE 'DISCREPANCY'
    END AS status
FROM regulatory_output.cet1_results auto
JOIN regulatory_output.manual_validation manual
    ON auto.reporting_date = manual.reporting_date
WHERE auto.reporting_date = '2026-01-31';

Metric	Automated	Manual	Difference	Status
CET1 Capital	$1,973.42M	$1,973.42M	$0.00	Match
RWA	$16,041.18M	$16,041.18M	$0.00	Match
CET1 Ratio	12.302%	12.302%	0.000%	Match
LCR	138.7%	138.7%	0.0%	Match
NSFR	112.4%	112.4%	0.0%	Match

Data Quality Gates

Rachel enforces zero-tolerance quality checks specific to regulatory data:

Check	Table	Rule	Tolerance	Last Result
No null risk weights	`loans`	All active loans must have risk_weight	0.0%	Pass (0 nulls)
Valid capital instruments	`capital_instruments`	instrument_type in defined set	0.0%	Pass
Balance sheet balances	`gl_balances`	total_assets = total_liabilities + equity	$0.01	Pass ($0.00)
Completeness	All regulated tables	No missing reporting periods	0 gaps	Pass
Timeliness	All sources	Synced within 24h of reporting date	0	Pass

Stage 6: Productionization

Rachel publishes the compliance dashboard suite in the BI Workbench.

Dashboard Suite

┌─────────────────────────────────────────────────────────────────┐
│                    REGULATORY DASHBOARD SUITE                    │
├────────────────┬────────────────┬────────────────┬──────────────┤
│ Capital        │ Liquidity      │ Credit         │ Market       │
│ Adequacy       │ Risk           │ Portfolio      │ Risk         │
│                │                │                │              │
│ CET1: 12.3%   │ LCR: 138.7%   │ NPL: 2.1%     │ VaR: 1.8%   │
│ T1:   14.1%   │ NSFR: 112.4%  │ Coverage: 68%  │ ES:  2.4%   │
│ Total: 16.8%  │ HQLA: $4.2B   │ Conc.: OK     │ Stress: OK   │
│                │                │                │              │
│ [Trend Chart]  │ [Waterfall]    │ [Heatmap]      │ [Histogram]  │
│ [Details...]   │ [Details...]   │ [Details...]   │ [Details...] │
└────────────────┴────────────────┴────────────────┴──────────────┘

Dashboard Configuration

{
  "dashboard_suite": {
    "name": "Meridian Bank Regulatory Suite",
    "owner": "rachel@meridian.bank",
    "access": {
      "viewers": ["regulatory-reporting", "senior-management", "board-risk-committee"],
      "editors": ["rachel@meridian.bank", "regulatory-bi-team"],
      "governance": "row_level_security_by_business_unit"
    },
    "dashboards": [
      {
        "name": "Capital Adequacy",
        "refresh": "daily",
        "metrics": ["cet1_ratio", "tier1_ratio", "total_capital_ratio", "leverage_ratio"],
        "charts": [
          {"type": "kpi_card", "metric": "cet1_ratio", "thresholds": [4.5, 10.5]},
          {"type": "trend_line", "metric": "cet1_ratio", "period": "24_months"},
          {"type": "waterfall", "metric": "cet1_capital", "breakdown": "components"},
          {"type": "table", "metric": "rwa_by_asset_class", "sortable": true}
        ]
      },
      {
        "name": "Liquidity Risk",
        "refresh": "daily",
        "metrics": ["lcr", "nsfr", "hqla_total"],
        "charts": [
          {"type": "kpi_card", "metric": "lcr", "thresholds": [100.0]},
          {"type": "stacked_bar", "metric": "hqla_by_level", "period": "12_months"},
          {"type": "line", "metric": "net_cash_outflows_30d", "period": "6_months"}
        ]
      },
      {
        "name": "Credit Portfolio",
        "refresh": "daily",
        "metrics": ["npl_ratio", "provision_coverage", "concentration_index"],
        "charts": [
          {"type": "heatmap", "metric": "exposure_by_sector_rating"},
          {"type": "treemap", "metric": "portfolio_concentration"},
          {"type": "trend_line", "metric": "npl_ratio", "period": "36_months"}
        ]
      },
      {
        "name": "Market Risk",
        "refresh": "daily",
        "metrics": ["portfolio_var", "expected_shortfall"],
        "charts": [
          {"type": "histogram", "metric": "daily_pnl_distribution"},
          {"type": "line", "metric": "var_vs_actual_pnl", "period": "12_months"},
          {"type": "scatter", "metric": "backtesting_exceptions"}
        ]
      }
    ]
  }
}

Automated Report Generation

Rachel configures scheduled PDF report generation with versioning:

Report	Schedule	Format	Recipients	Retention
Capital Adequacy Monthly	2nd business day	PDF + XBRL	Regulator, CFO, CRO	7 years
Daily LCR	T+1 09:00	PDF	Treasury, CFO	5 years
Credit Portfolio Review	Weekly (Monday)	PDF	CRO, Credit Committee	5 years
Board Risk Report	Monthly	PDF	Board Risk Committee	Permanent
Stress Test Results	Quarterly	PDF + CSV	Regulator, Board	7 years

Stage 7: Feedback

Rachel monitors data freshness and report accuracy to ensure regulatory deadlines are never missed.

Data Freshness SLA Monitoring

{
  "sla_monitors": [
    {
      "name": "daily-lcr-data-freshness",
      "metric": "source_last_sync_timestamp",
      "sources": ["core-banking-regulatory", "market-data-feed"],
      "threshold_hours": 6,
      "alert_channels": ["pagerduty", "email:rachel@meridian.bank"],
      "escalation": {
        "after_hours": 12,
        "escalate_to": "cfo@meridian.bank"
      }
    },
    {
      "name": "monthly-report-deadline",
      "metric": "business_days_until_deadline",
      "threshold_days": 3,
      "alert_channels": ["slack:#reg-reporting", "email:rachel@meridian.bank"]
    }
  ]
}

Report Accuracy Tracking

Rachel tracks restatements and corrections over time:

Quarter	Reports Submitted	Restatements	Accuracy Rate	Root Cause
Q1 2025	14	2	85.7%	Manual spreadsheet errors (pre-MATIH)
Q2 2025	14	1	92.9%	Data feed delay mishandled
Q3 2025	14	0	100%	Automated pipeline live
Q4 2025	14	0	100%	--
Q1 2026	14	0	100%	--

Since migrating to automated pipelines in Q3 2025, zero restatements.

Missing Data Feed Alerts

When a source feed fails to arrive, the monitoring system alerts immediately:

Alert	Trigger	Response Time Target	Escalation
Source feed missing	No data within expected window	30 minutes	Data engineering on-call
Calculation variance	Auto vs manual > $1M	1 hour	Rachel + compliance
Deadline approaching	< 3 business days remaining	Immediate	Rachel + CFO
Quality check failure	Any critical check fails	15 minutes	Rachel + data quality team

Stage 8: Experimentation

Rachel tests new visualization approaches and pilots self-service analytics for business users.

Testing New Visualizations for Risk Committees

Rachel creates experimental dashboards to improve how risk information is communicated:

Experiment	Current Approach	New Approach	Test Group	Duration
Portfolio concentration	Static table	Interactive treemap with drill-down	Board Risk Committee	2 meetings
CET1 trend	Simple line chart	Waterfall decomposition showing drivers	CFO + Treasurer	1 month
Credit quality	Tabular NPL report	Heatmap (sector x rating) with color coding	Credit Committee	2 meetings
Stress test results	40-page PDF	Interactive scenario explorer	CRO + Risk team	1 quarter

Piloting Self-Service Analytics

Rachel pilots self-service analytics for relationship managers (RMs), with governance guardrails:

{
  "self_service_pilot": {
    "name": "rm-portfolio-analytics",
    "target_users": "relationship_managers",
    "pilot_size": 25,
    "duration_weeks": 8,
    "capabilities": [
      "View assigned portfolio metrics",
      "Filter by client, sector, product",
      "Export client-level reports",
      "Ask natural language questions about their portfolio"
    ],
    "governance_guardrails": [
      {
        "type": "row_level_security",
        "rule": "RMs can only see their assigned clients",
        "enforcement": "automatic"
      },
      {
        "type": "column_masking",
        "rule": "SSN, tax_id, full account numbers masked",
        "enforcement": "automatic"
      },
      {
        "type": "export_controls",
        "rule": "Max 500 rows per export, no PII columns",
        "enforcement": "automatic"
      },
      {
        "type": "query_limits",
        "rule": "Queries limited to assigned portfolio scope",
        "enforcement": "automatic"
      }
    ],
    "success_metrics": [
      "Reduction in ad-hoc report requests to BI team",
      "RM satisfaction score",
      "Time to answer client questions",
      "Zero governance violations"
    ]
  }
}

Pilot Results (Week 4 of 8)

Metric	Before Pilot	During Pilot	Change
Ad-hoc report requests / week	47	18	-62%
Avg time to answer client question	4.2 hours	12 minutes	-95%
RM satisfaction (1-10)	5.2	8.4	+62%
Governance violations	N/A	0	Target met
Reports exported	N/A	142	All within guardrails

Based on these results, Rachel plans to expand self-service access to all 180 relationship managers in Q2 2026.

Key Takeaways

Stage	Key Action	Platform Component
Ingestion	Audit-logged ingestion with row-level lineage tracking	Airbyte connectors, Governance Service
Discovery	Mapped regulatory data elements, tagged PII columns	Catalog Service, Governance Service
Query	Defined semantic layer metrics matching Basel III formulas	Semantic Layer, Query Engine
Orchestration	Regulated pipeline with manual approval gates, zero-tolerance quality checks	Pipeline Service (Temporal)
Analysis	Reconciled automated calculations against manual spreadsheets	Data Quality Service
Productionization	Published 4-dashboard compliance suite, automated PDF/XBRL generation	BI Workbench, BI Service
Feedback	Data freshness SLA monitoring, regulatory deadline tracking	Alerting, monitoring
Experimentation	Tested new visualizations for risk committees, piloted RM self-service	BI Workbench, Governance Service

Related Walkthroughs

Data Scientist Journey: Credit Risk Scoring -- Amir builds the PD model whose outputs feed Rachel's credit portfolio dashboard
ML Engineer Journey: Real-Time Fraud Detection -- Kenji's fraud metrics appear in Rachel's risk reports
Executive Journey: Strategic Risk Analytics -- Elena consumes Rachel's dashboards for strategic decisions
Financial Services Overview -- Industry context and sample datasets

ML Engineer Journey Executive Leadership Journey