Live Portfolio Risk Monitoring with RisingWave

Live portfolio risk monitoring means computing P&L, Value-at-Risk, and exposure limits continuously as market prices and trade data arrive — not in end-of-day batch runs. With RisingWave's streaming SQL, risk teams maintain always-fresh materialized views over live market feeds and position data, enabling intraday risk decisions instead of reactive post-close analysis.

The Problem with End-of-Day Risk Calculations

For decades, risk management in asset management and trading operated on a T+1 or intraday-batch model: positions were snapshotted, risk metrics computed overnight, and reports distributed the next morning. This worked when markets moved slowly and regulation was light.

Today, the model is broken. Intraday volatility events, regulatory requirements like FRTB and Basel IV, and the sheer complexity of multi-asset portfolios demand continuous risk visibility. A desk running large equity and derivatives positions cannot afford to discover a limit breach at end of day — by then, the firm's capital is already at risk.

Streaming databases change this equation. By maintaining continuously updated aggregations over position changes and price ticks, risk systems can surface breaches in real time.

Architecture for Streaming Risk

A streaming risk platform built on RisingWave typically integrates:

• Market data feeds (prices, rates, volatility surfaces) via Kafka
• Trade and position data from order management or core banking systems via CDC
• Reference data (instruments, counterparties, limits) in RisingWave tables
• Risk outputs materialized as views and sunk to dashboards or alert systems

Ingesting Market Data and Positions

Start by creating sources for live price ticks and position events:

-- Live market price feed from Kafka
CREATE SOURCE market_prices (
    instrument_id  VARCHAR,
    price          NUMERIC,
    bid            NUMERIC,
    ask            NUMERIC,
    price_time     TIMESTAMPTZ
)
WITH (
    connector = 'kafka',
    topic = 'market.prices',
    properties.bootstrap.server = 'broker:9092',
    scan.startup.mode = 'latest'
)
FORMAT PLAIN ENCODE JSON;

-- Position updates from OMS via CDC
CREATE SOURCE positions (
    position_id    VARCHAR,
    account_id     VARCHAR,
    instrument_id  VARCHAR,
    quantity       NUMERIC,
    cost_basis     NUMERIC,
    position_time  TIMESTAMPTZ
)
WITH (
    connector = 'postgres-cdc',
    hostname = 'oms-db.internal',
    port = '5432',
    username = 'rw_user',
    password = 'secret',
    database.name = 'oms',
    schema.name = 'public',
    table.name = 'positions'
);

Real-Time P&L Materialized View

Join live prices against current positions to compute unrealized P&L continuously:

CREATE MATERIALIZED VIEW realtime_pnl AS
SELECT
    p.account_id,
    p.instrument_id,
    p.quantity,
    p.cost_basis,
    mp.price                                            AS current_price,
    mp.price_time,
    (mp.price - p.cost_basis) * p.quantity              AS unrealized_pnl,
    (mp.price - p.cost_basis) / NULLIF(p.cost_basis, 0) AS pct_return,
    SUM((mp.price - p.cost_basis) * p.quantity)
        OVER (PARTITION BY p.account_id)                AS portfolio_pnl
FROM positions p
JOIN market_prices FOR SYSTEM_TIME AS OF p.position_time AS mp
    ON p.instrument_id = mp.instrument_id;

This temporal join ensures that each position is priced at the most recent market data available at query time — critical for accuracy when prices and positions update at different frequencies.

Exposure and Limit Monitoring

Track gross and net exposure by asset class in real-time windows:

CREATE MATERIALIZED VIEW exposure_by_asset_class AS
SELECT
    p.account_id,
    inst.asset_class,
    SUM(p.quantity * mp.price)                           AS gross_exposure,
    SUM(p.quantity * mp.price * SIGN(p.quantity::FLOAT)) AS net_exposure,
    COUNT(DISTINCT p.instrument_id)                      AS position_count,
    MAX(mp.price_time)                                   AS last_price_time,
    window_start,
    window_end
FROM TUMBLE(
    positions,
    position_time,
    INTERVAL '1 MINUTE'
) p
JOIN market_prices mp ON p.instrument_id = mp.instrument_id
JOIN instrument_reference inst ON p.instrument_id = inst.instrument_id
GROUP BY
    p.account_id,
    inst.asset_class,
    window_start,
    window_end;

Limit breach alerts can be built as a separate materialized view reading from exposure_by_asset_class, filtering against a limits reference table.

Streaming Risk Alerts to Dashboards

Push limit breaches to a downstream Kafka topic consumed by the risk dashboard:

CREATE SINK risk_limit_alerts_sink
FROM (
    SELECT
        e.account_id,
        e.asset_class,
        e.gross_exposure,
        l.exposure_limit,
        e.gross_exposure / l.exposure_limit AS utilization_pct,
        NOW() AS alert_time
    FROM exposure_by_asset_class e
    JOIN exposure_limits l
        ON e.account_id = l.account_id
        AND e.asset_class = l.asset_class
    WHERE e.gross_exposure > l.exposure_limit * 0.9
)
WITH (
    connector = 'kafka',
    topic = 'risk.alerts',
    properties.bootstrap.server = 'broker:9092'
)
FORMAT PLAIN ENCODE JSON;

Batch vs. Streaming Risk Monitoring

FAQ

Q: Can RisingWave handle the volume of a full exchange-level market data feed? A: RisingWave is designed for high-throughput streaming ingestion. It handles millions of price ticks per second with sub-second latency on standard cloud hardware. Horizontal scaling is available for larger workloads.

Q: How do we model complex derivatives positions, like options greeks? A: Greeks (delta, gamma, vega) are typically pre-computed in a pricing engine. The resulting risk sensitivities can be ingested as a Kafka stream or via CDC and then aggregated in RisingWave materialized views alongside spot positions.

Q: What happens to materialized views during market data gaps or feed outages? A: RisingWave materialized views retain the last computed state during source gaps. Watermark configurations control how long the system waits before advancing time, preventing premature window closes during data interruptions.

Q: Can we run historical scenario analysis in the same system? A: Yes. Historical price data can be replayed through the same materialized view definitions via a bounded Kafka source. This allows consistent stress testing and backtesting using the same SQL logic as production.

Q: How does RisingWave integrate with existing risk systems like Murex or Calypso? A: RisingWave exposes a PostgreSQL-compatible interface. Existing tools that connect to PostgreSQL can query RisingWave materialized views directly, enabling incremental integration without replacing existing infrastructure.

Get Started

Streaming portfolio risk is one of the most impactful modernization opportunities in capital markets technology. Start building with RisingWave today:

• Follow the quickstart guide: docs.risingwave.com/get-started
• Connect with the community: risingwave.com/slack