Regulatory Reporting Automation with a Streaming Database

Regulatory reporting automation with a streaming database means maintaining continuously updated capital, liquidity, and exposure metrics as transactions and market data flow through the institution — so reports for Basel III, FRTB, LCR, and Dodd-Frank can be generated on demand from always-current materialized views, not reconstructed from stale batch snapshots.

The Regulatory Reporting Burden

Financial institutions spend enormous resources on regulatory reporting. Large banks employ hundreds of people in regulatory operations, and the infrastructure to produce reports like:

• Basel III capital adequacy (RWA, CET1 ratio)
• LCR/NSFR liquidity ratios
• Dodd-Frank derivatives reporting (swap data repositories)
• FRTB market risk capital
• MiFID II transaction reporting

...typically involves extracting data from dozens of source systems, transforming it through a labyrinth of ETL jobs, and loading into a reporting database once or twice per day.

The risk: if any batch job fails, the report is wrong. If market conditions change intraday, the data is stale. Regulators increasingly expect intraday visibility into systemic risk — a requirement that batch architectures structurally cannot meet.

Streaming Architecture for Regulatory Data

RisingWave addresses this by treating regulatory metrics as continuously maintained materialized views:

• Capital metrics (RWA, capital ratios) update as positions change and risk weights are applied
• Liquidity metrics (LCR components) update as cash flows and HQLA holdings change
• Exposure reports update as trades are booked

The PostgreSQL interface allows regulatory reporting tools to query these views at any time — intraday, EOD, or on demand — and receive results based on the most current data.

Ingesting Position and Trade Data

-- Trade positions from risk system via CDC
CREATE SOURCE trade_positions (
    trade_id         VARCHAR,
    account_id       VARCHAR,
    counterparty_id  VARCHAR,
    instrument_type  VARCHAR,  -- 'equity', 'bond', 'derivative', 'loan'
    asset_class      VARCHAR,
    notional         NUMERIC,
    market_value     NUMERIC,
    currency         VARCHAR,
    risk_weight_pct  NUMERIC,  -- Basel risk weight, pre-computed
    is_hqla          BOOLEAN,  -- High-Quality Liquid Asset flag
    maturity_band    VARCHAR,  -- 'overnight', '1w', '1m', '3m', '6m', '1y', '1y+'
    trade_time       TIMESTAMPTZ
)
WITH (
    connector = 'postgres-cdc',
    hostname = 'risk-system.internal',
    port = '5432',
    username = 'rw_cdc_user',
    password = 'secret',
    database.name = 'riskdb',
    schema.name = 'public',
    table.name = 'trade_positions'
);

-- Cash flow projections for liquidity reporting
CREATE SOURCE cash_flows (
    flow_id         VARCHAR,
    account_id      VARCHAR,
    flow_type       VARCHAR,  -- 'inflow', 'outflow'
    flow_category   VARCHAR,  -- 'retail_deposit', 'wholesale_funding', 'loan_drawdown'
    amount          NUMERIC,
    currency        VARCHAR,
    maturity_date   DATE,
    stress_factor   NUMERIC,  -- LCR run-off rate
    flow_time       TIMESTAMPTZ
)
WITH (
    connector = 'kafka',
    topic = 'treasury.cash.flows',
    properties.bootstrap.server = 'broker:9092',
    scan.startup.mode = 'latest'
)
FORMAT PLAIN ENCODE JSON;

Basel III Risk-Weighted Assets

Compute RWA continuously as positions are booked and updated:

CREATE MATERIALIZED VIEW basel_rwa_by_account AS
SELECT
    account_id,
    asset_class,
    COUNT(DISTINCT trade_id)                          AS position_count,
    SUM(market_value)                                 AS total_exposure,
    SUM(market_value * risk_weight_pct / 100.0)       AS risk_weighted_assets,
    AVG(risk_weight_pct)                              AS avg_risk_weight,
    SUM(market_value) FILTER (
        WHERE instrument_type = 'derivative'
    )                                                 AS derivative_exposure,
    SUM(market_value) FILTER (
        WHERE instrument_type = 'loan'
    )                                                 AS loan_exposure,
    MAX(trade_time)                                   AS last_updated
FROM trade_positions
GROUP BY
    account_id,
    asset_class;

LCR Component Tracking

Compute Liquidity Coverage Ratio components in real time:

CREATE MATERIALIZED VIEW lcr_components AS
SELECT
    account_id,
    -- HQLA Stock (numerator)
    SUM(market_value) FILTER (
        WHERE is_hqla = true
    )                                                        AS hqla_stock,

    -- Net Cash Outflows (denominator)
    SUM(amount * stress_factor) FILTER (
        WHERE flow_type = 'outflow'
        AND maturity_date <= CURRENT_DATE + INTERVAL '30 DAYS'
    )                                                        AS stressed_outflows_30d,
    SUM(amount) FILTER (
        WHERE flow_type = 'inflow'
        AND maturity_date <= CURRENT_DATE + INTERVAL '30 DAYS'
        AND flow_category NOT IN ('secured_lending', 'collateral_swaps')
    ) * 0.75                                                 AS capped_inflows_30d,

    -- LCR Ratio
    ROUND(
        SUM(market_value) FILTER (WHERE is_hqla = true)
        / NULLIF(
            SUM(amount * stress_factor) FILTER (
                WHERE flow_type = 'outflow'
                AND maturity_date <= CURRENT_DATE + INTERVAL '30 DAYS'
            )
            - SUM(amount) FILTER (
                WHERE flow_type = 'inflow'
                AND maturity_date <= CURRENT_DATE + INTERVAL '30 DAYS'
            ) * 0.75,
            0
        ) * 100,
        2
    )                                                        AS lcr_ratio_pct,
    NOW()                                                    AS computed_at
FROM trade_positions tp
CROSS JOIN cash_flows cf
WHERE tp.account_id = cf.account_id
GROUP BY
    account_id;

Streaming Reports to Regulatory Systems

CREATE SINK regulatory_metrics_sink
FROM (
    SELECT account_id, asset_class, risk_weighted_assets, last_updated
    FROM basel_rwa_by_account
    UNION ALL
    SELECT account_id, 'LCR' AS asset_class, lcr_ratio_pct, computed_at
    FROM lcr_components
)
WITH (
    connector = 'jdbc',
    jdbc.url = 'jdbc:postgresql://reg-reporting-db.internal:5432/regdb',
    table.name = 'regulatory_metrics',
    user = 'rw_sink',
    password = 'secret'
)
FORMAT PLAIN ENCODE JSON;

Batch Regulatory Reporting vs. Streaming

FAQ

Q: Can RisingWave replace our existing regulatory reporting platform? A: RisingWave is most effective as a real-time data layer feeding into existing regulatory reporting platforms. It can replace batch ETL pipelines and maintain continuously updated metrics tables that existing reporting tools read from.

Q: How do we handle regulatory-required point-in-time snapshots? A: RisingWave can sink materialized view snapshots to immutable storage (Iceberg) on a scheduled or triggered basis. This creates a time-series of regulatory snapshots without requiring batch re-computation.

Q: What about auditability — can we trace how a metric was computed? A: Because metrics are derived from SQL materialized views with known, versioned definitions, the computation logic is fully auditable. Combined with source event history stored in Kafka or Iceberg, regulators can trace any metric back to its source transactions.

Q: How does RisingWave handle multiple jurisdictions with different reporting requirements? A: Each jurisdiction's requirements can be implemented as separate materialized views with their own risk weight tables and rule sets. Reference tables store jurisdiction-specific parameters, and views are parameterized by joining against those tables.

Q: Is RisingWave certified for use in financial regulatory reporting? A: RisingWave is production software used by financial institutions. Regulatory certification of reporting infrastructure is the responsibility of the institution. RisingWave provides the technical substrate; internal validation, model governance, and audit documentation remain with the institution.

Get Started

Automate your regulatory reporting with always-current streaming metrics:

• Try the quickstart: docs.risingwave.com/get-started
• Connect with compliance and data engineering teams: risingwave.com/slack