Model Reference¶

This reference documents all customer-facing tables in the EWE Numera data platform. For interactive exploration including column details and lineage, see the dbt Documentation.

Quick Reference¶

Core Numera Models¶

Category	Tables
Deal Dimensions	`dim_deal_attribute`, `dim_deal_sub_attribute`, `dim_tran_info`
Party Dimensions	`dim_party_internal`, `dim_party_external`, `dim_trader`
Location Dimensions	`dim_delivery_location`, `dim_point_of_delivery_location`, `dim_point_of_receipt_location`
Date Dimensions	`dim_date`, `dim_metric_date`, `dim_trade_date`, `dim_delivery_start_date`
Reference Dimensions	`dim_tran_status`, `dim_volume_type`, `dim_portfolio`, `dim_currency`
P&L Facts	`fct_pnl`, `fct_pnl_deal`, `fct_deal_pnl_current`
Volume Facts	`fct_volume`, `fct_volume_daily`, `fct_schedule`
Risk Facts	`fct_delta`, `fct_var_pfolio`

EWE-Specific Models¶

Category	Tables
Credit Exposure	`fct_credit_exposure`, `fct_credit_exposure_month`, `fct_credit_pex_factor`
Clearing Statements	`fct_clearing_statement_open_position`
Power Volumes	`fct_ewe_volume_pwr_by_business_case`, `fct_ewe_volume_pwr_by_business_case_deal`
Power Volumes (Floating)	`fct_ewe_volume_pwr_floating_by_business_case`, `fct_ewe_volume_pwr_floating_by_business_case_deal`
Party Extensions	`dim_party_agreement`, `dim_party_agreement_attribute`

Deal Dimensions¶

dim_deal_attribute¶

Core deal attributes for reporting and analysis.

Column	Type	Description
`dim_deal_attribute_id`	INTEGER	Surrogate key (0 = Unknown)
`deal_id`	INTEGER	Persistent deal identifier
`portfolio`	VARCHAR	Portfolio name
`instrument`	VARCHAR	Product type (GAS-PHYS, POWER-FIN, etc.)
`buy_sell`	VARCHAR	Direction: 'Buy' or 'Sell'
`commodity`	VARCHAR	Commodity type
`market`	VARCHAR	Market classification
`asset_type`	VARCHAR	Asset classification
`tran_status`	VARCHAR	Current transaction status

dim_deal_sub_attribute¶

Side-level deal attributes for multi-leg deals.

Column	Type	Description
`dim_deal_sub_attribute_id`	INTEGER	Surrogate key
`param_seq_id`	INTEGER	Side identifier within deal
`market`	VARCHAR	Market for this side
`commodity`	VARCHAR	Commodity for this side
`proj_index`	VARCHAR	Projection index
`fix_float`	VARCHAR	Fixed or floating pricing
`settlement_type`	VARCHAR	Settlement method

dim_tran_info¶

Transaction version information.

Column	Type	Description
`dim_tran_info_id`	INTEGER	Surrogate key
`deal_id`	INTEGER	Persistent deal identifier
`tran_id`	INTEGER	Transaction identifier
`tran_version`	INTEGER	Amendment version number
`ins_num`	INTEGER	Endur internal number

Party Dimensions¶

dim_party_internal¶

Internal business units and legal entities.

Column	Type	Description
`dim_party_internal_bu_le_attribute_id`	INTEGER	Surrogate key
`bunit_id`	INTEGER	Business unit ID
`bunit_name`	VARCHAR	Business unit name
`lentity_id`	INTEGER	Legal entity ID
`lentity_name`	VARCHAR	Legal entity name

dim_party_external¶

External counterparties (business unit and legal entity pairs).

Column	Type	Description
`dim_party_external_bu_le_attribute_id`	INTEGER	Surrogate key
`bunit_id`	INTEGER	Business unit ID
`bunit_name`	VARCHAR	Business unit name
`lentity_id`	INTEGER	Legal entity ID
`lentity_name`	VARCHAR	Legal entity name

dim_party_agreement (EWE)¶

Party agreement attributes and netting terms.

Column	Type	Description
`dim_party_agreement_attribute_id`	INTEGER	Surrogate key
`party_id`	INTEGER	Party identifier
`party_agreement_id`	INTEGER	Agreement identifier
`party_agreement_name`	VARCHAR	Agreement name
`has_close_out_netting`	BOOLEAN	Close-out netting enabled
`has_payment_netting`	BOOLEAN	Payment netting enabled
`collateral_agreement_id`	INTEGER	Linked collateral agreement
`has_cross_commodity_netting`	BOOLEAN	Cross-commodity netting enabled

P&L Fact Tables¶

fct_pnl¶

Aggregated P&L across all dimensions.

Column	Type	Description
`metric_date`	DATE	Valuation date
`dim_deal_attribute_id`	INTEGER	FK to dim_deal_attribute
`dim_party_internal_bu_le_attribute_id`	INTEGER	FK to dim_party_internal
`dim_party_external_bu_le_attribute_id`	INTEGER	FK to dim_party_external
`delivery_month`	DATE	Delivery period
`pnl_realized_ltd`	NUMBER	Realized P&L (life-to-date)
`pnl_unrealized_ltd`	NUMBER	Unrealized P&L (life-to-date)
`pnl_ltd`	NUMBER	Total P&L (life-to-date)
`pnl_ltd_base`	NUMBER	P&L in base currency

fct_pnl_deal¶

Deal-level P&L detail.

Column	Type	Description
`metric_date`	DATE	Valuation date
`deal_id`	INTEGER	Deal identifier
`delivery_start_date`	DATE	Delivery period start
`delivery_end_date`	DATE	Delivery period end
`pnl_amount`	NUMBER	P&L amount

Credit Exposure Models (EWE)¶

fct_credit_exposure¶

Daily credit exposure metrics by counterparty.

Column	Type	Description
`metric_date`	DATE	Valuation date
`dim_party_external_bu_le_attribute_id`	INTEGER	FK to dim_party_external
`exp_line_id`	INTEGER	Exposure line identifier
`current_exposure`	NUMBER(38,8)	Mark-to-market exposure
`exposure_limit`	NUMBER(38,8)	Credit limit
`performance_exposure`	NUMBER(38,8)	Expected performance exposure
`pot_performance_exposure`	NUMBER(38,8)	Potential performance exposure
`potential_exposure`	NUMBER(38,8)	Potential (worst-case) exposure
`settlement_exposure`	NUMBER(38,8)	Settlement exposure

fct_credit_exposure_month¶

Monthly credit exposure projections.

Column	Type	Description
`metric_date`	DATE	Valuation date
`effective_month`	DATE	Projection month
`dim_party_external_bu_le_attribute_id`	INTEGER	FK to dim_party_external
`exp_line_id`	INTEGER	Exposure line identifier
`potential_exposure_high`	NUMBER(38,8)	High scenario potential exposure
`potential_exposure_low`	NUMBER(38,8)	Low scenario potential exposure
`pot_performance_exposure_high`	NUMBER(38,8)	High scenario performance exposure
`pot_performance_exposure_low`	NUMBER(38,8)	Low scenario performance exposure

fct_credit_pex_factor¶

PEX factors by commodity.

Column	Type	Description
`metric_date`	DATE	Valuation date
`commodity`	VARCHAR	Commodity type
`factor`	NUMBER	PEX factor value

Clearing Statement Models (EWE)¶

fct_clearing_statement_open_position¶

Unified clearing bank open positions (ABN AMRO and BNP Paribas).

Column	Type	Description
`metric_date`	DATE	Statement date
`dim_party_external_bu_le_attribute_id`	INTEGER	FK to dim_party_external
`account`	VARCHAR	Clearing account
`exchange`	VARCHAR	Exchange code
`symbol`	VARCHAR	Contract symbol
`isin`	VARCHAR	ISIN identifier
`buy_sell`	VARCHAR	Direction: 'Buy' or 'Sell'
`contract_size`	NUMBER	Contract size
`quantity`	NUMBER	Number of contracts
`volume`	NUMBER	Total volume
`delivery_start_date`	DATE	Delivery start
`delivery_end_date`	DATE	Delivery end
`ote`	NUMBER	Open trade equity
`trade_price`	NUMBER	Original trade price
`settlement_price`	NUMBER	Current settlement price
`previous_settlement_price`	NUMBER	Prior day settlement price
`variation_margin`	NUMBER	Daily margin movement

Volume Fact Tables¶

fct_volume¶

Aggregated volumes by period and deal.

Column	Type	Description
`dim_deal_attribute_id`	INTEGER	FK to dim_deal_attribute
`dim_delivery_location_id`	INTEGER	FK to dim_delivery_location
`start_time_utc`	TIMESTAMP	Period start (UTC)
`end_time_utc`	TIMESTAMP	Period end (UTC)
`volume_mwh`	NUMBER	Volume in MWh

fct_schedule¶

Operational delivery schedules.

Column	Type	Description
`dim_deal_attribute_id`	INTEGER	FK to dim_deal_attribute
`dim_point_of_delivery_location_id`	INTEGER	FK to dim_point_of_delivery_location
`dim_point_of_receipt_location_id`	INTEGER	FK to dim_point_of_receipt_location
`start_time_utc`	TIMESTAMP	Period start (UTC)
`end_time_utc`	TIMESTAMP	Period end (UTC)
`start_time_local`	TIMESTAMP	Period start (local)
`end_time_local`	TIMESTAMP	Period end (local)
`volume_abs`	NUMBER	Absolute volume
`is_best_available`	BOOLEAN	Best available data flag

fct_ewe_volume_pwr_by_business_case (EWE)¶

15-minute power volumes aggregated by business case.

Column	Type	Description
`delivery_date_utc`	DATE	Delivery date
`begin_utc`	TIMESTAMP	Period start (UTC)
`end_utc`	TIMESTAMP	Period end (UTC)
`begin_cet`	TIMESTAMP	Period start (CET)
`end_cet`	TIMESTAMP	Period end (CET)
`grid`	VARCHAR	Power control area
`business_case`	VARCHAR	Business case/strategy
`volume_buy_mw`	NUMBER	Buy volume (MW)
`volume_sell_mw`	NUMBER	Sell volume (MW)
`buy_vwap_price_eur_per_mwh`	NUMBER	Buy VWAP (EUR/MWh)
`sell_vwap_price_eur_per_mwh`	NUMBER	Sell VWAP (EUR/MWh)

fct_ewe_volume_pwr_floating_by_business_case (EWE)¶

15-minute power volumes for floating/index-linked trades (PWR Index instrument type).

Column	Type	Description
`delivery_date_utc`	DATE	Delivery date
`begin_utc`	TIMESTAMP	Period start (UTC)
`end_utc`	TIMESTAMP	Period end (UTC)
`begin_cet`	TIMESTAMP	Period start (CET)
`end_cet`	TIMESTAMP	Period end (CET)
`grid`	VARCHAR	Power control area
`business_case`	VARCHAR	Business case/strategy
`internal_business_unit`	VARCHAR	Internal BU name
`external_business_unit`	VARCHAR	External BU name
`power_index_name`	VARCHAR	Index name (e.g., EPEX Spot)
`volume_buy_mw`	NUMBER	Buy volume (MW)
`volume_sell_mw`	NUMBER	Sell volume (MW)
`power_index_price_eur_per_mwh`	NUMBER	Index price (EUR/MWh)

Risk Fact Tables¶

fct_delta¶

Delta exposure by index and delivery period.

Column	Type	Description
`metric_date`	DATE	Valuation date
`dim_index_attribute_id`	INTEGER	FK to dim_index_attribute
`dim_deal_attribute_id`	INTEGER	FK to dim_deal_attribute
`delivery_month`	DATE	Delivery period
`delta_mwh`	NUMBER	Delta in MWh
`gamma`	NUMBER	Gamma (second derivative)

fct_var_pfolio¶

Value at Risk by portfolio.

Column	Type	Description
`metric_date`	DATE	Valuation date
`dim_portfolio_id`	INTEGER	FK to dim_portfolio
`var_95`	NUMBER	95% confidence VaR
`var_99`	NUMBER	99% confidence VaR

Join Patterns¶

Basic Star Schema Join¶

SELECT
    d.portfolio,
    d.instrument,
    pi.bunit_name AS internal_bu,
    pe.lentity_name AS counterparty,
    SUM(f.pnl_ltd) AS total_pnl
FROM fct_pnl f
LEFT JOIN dim_deal_attribute d
    ON f.dim_deal_attribute_id = d.dim_deal_attribute_id
LEFT JOIN dim_party_internal pi
    ON f.dim_party_internal_bu_le_attribute_id = pi.dim_party_internal_bu_le_attribute_id
LEFT JOIN dim_party_external pe
    ON f.dim_party_external_bu_le_attribute_id = pe.dim_party_external_bu_le_attribute_id
WHERE f.metric_date = CURRENT_DATE - 1
  AND d.dim_deal_attribute_id != 0  -- Exclude unknown
GROUP BY 1, 2, 3, 4

Credit Exposure with Party¶

SELECT
    f.metric_date,
    pe.lentity_name AS counterparty,
    f.current_exposure,
    f.potential_exposure,
    f.exposure_limit
FROM fct_credit_exposure f
LEFT JOIN dim_party_external pe
    ON f.dim_party_external_bu_le_attribute_id = pe.dim_party_external_bu_le_attribute_id
WHERE f.metric_date = CURRENT_DATE - 1
ORDER BY f.current_exposure DESC