Multi-table materialized views for post-trade analysis

[bluestreak01]

Summary

Enhanced materialized views that can be triggered by multiple tables, enabling continuous enrichment of core data (trade records) with changing market data, reference data, or other metadata. Supports window joins, markouts, and ASOF semantics.

Current Limitations

• Materialized views triggered by single table only
• Must contain SAMPLE BY clause
• Cannot join with other tables that trigger updates

Enhanced Capabilities

Feature	Description
Multi-table triggers	View updates when any source table changes
Continuous enrichment	Augment core records with latest market data
Join support	ASOF JOIN, HORIZON JOIN, window joins in view definitions
No SAMPLE BY requirement	Flexible view definitions for non-aggregated enrichment

Example

-- Enrich trades with market data and calculate slippage
CREATE MATERIALIZED VIEW enriched_trades AS
SELECT 
    t.ts,
    t.symbol,
    t.side,
    t.fill_price,
    t.quantity,
    q.mid AS market_mid,
    t.fill_price - q.mid AS slippage,
    r.sector,
    r.currency
FROM trades t
ASOF JOIN quotes q ON (t.symbol = q.symbol)
LEFT JOIN ref_data r ON (t.symbol = r.symbol);
-- Updates when trades, quotes, OR ref_data change

Use Cases

• Post-trade analysis — Enrich trades with market conditions at time of execution
• Slippage monitoring — Continuously calculate execution quality metrics
• Markout curves — Maintain rolling markout calculations as new data arrives
• Reference data enrichment — Augment time-series with slowly-changing dimensions

Benefits

• Always fresh — View updates automatically as any source changes
• Reduced query complexity — Pre-computed enrichment, no runtime joins
• Real-time analytics — Post-trade metrics available immediately
• Single source of truth — Consistent enrichment logic defined once