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 (Short-term, fixed) | fct_ewe_volume_pwr_by_business_case, fct_ewe_volume_pwr_by_business_case_deal |
| Power Volumes (Short-term, floating) | fct_ewe_volume_pwr_floating_by_business_case, fct_ewe_volume_pwr_floating_by_business_case_deal |
| Power Volumes (Controlling, unfiltered) | fct_ewe_volume_pwr_by_business_case_unfiltered, fct_ewe_volume_pwr_by_business_case_deal_unfiltered, fct_ewe_volume_pwr_by_business_case_with_bu_unfiltered |
| Power Volumes (Controlling, quarterly) | fct_volume_pwr_quarter_fixed_price, fct_volume_pwr_quarter_fixed_price_deal |
| Power Volumes (Semantic views) | sv_power_volumes, sv_power_volumes_floating |
| 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 fixed-price power volumes aggregated by business case for the short-term desk.
Grain: business_case + grid + begin_utc.
Filter: tran_status IN ('Validated','Matured') AND (external_business_unit IN ('ECC INTD BU','NORD POOL AS BU') OR (external_business_unit = 'EWE TR SHORT TERM BU' AND external_portfolio = 'PWR_ST_PF_Netting')).
| 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) — 0 on gap rows |
volume_sell_mw |
NUMBER | Sell volume (MW) — 0 on gap rows |
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_by_business_case_deal (EWE)¶
Deal-grain drill-down of fct_ewe_volume_pwr_by_business_case.
Grain: business_case + grid + deal_id + dim_deal_attribute_id + begin_utc.
Filter: same as parent but BU clause restricted to external_business_unit IN ('ECC INTD BU','NORD POOL AS BU').
Adds: deal_id (INTEGER), dim_deal_attribute_id (INTEGER, FK to dim_deal_attribute).
fct_ewe_volume_pwr_floating_by_business_case (EWE)¶
15-minute power volumes for floating/index-linked trades (instrument_sub_type = 'PWR Index').
Grain: business_case + grid + internal_business_unit + external_business_unit + power_index_name + begin_utc.
| 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., PWR EPEX_DE_INTRADAY 15min) |
volume_buy_mw |
NUMBER | Buy volume (MW) — 0 on gap rows |
volume_sell_mw |
NUMBER | Sell volume (MW) — 0 on gap rows |
power_index_price_eur_per_mwh |
NUMBER | Index price (EUR/MWh) |
fct_ewe_volume_pwr_floating_by_business_case_deal (EWE)¶
Deal-grain drill-down of the floating fact. Adds deal_id and dim_deal_attribute_id.
Controlling unfiltered variants (EWE)¶
Same shape as the short-term desk parents but without the BU/portfolio filter. All retain tran_status IN ('Validated','Matured').
| Model | Grain |
|---|---|
fct_ewe_volume_pwr_by_business_case_unfiltered |
business_case + grid + begin_utc |
fct_ewe_volume_pwr_by_business_case_deal_unfiltered |
business_case + grid + deal_id + dim_deal_attribute_id + begin_utc |
fct_ewe_volume_pwr_by_business_case_with_bu_unfiltered |
business_case + grid + internal_business_unit + external_business_unit + begin_utc |
fct_volume_pwr_quarter_fixed_price / fct_volume_pwr_quarter_fixed_price_deal (EWE)¶
Quarterly controlling variant of the fixed-price 15-minute fact. Same column shape as the short-term desk fixed-price models, with dim_deal_attribute_id retained for portfolio/instrument joins.
Grain (parent): business_case + grid + dim_deal_attribute_id + begin_utc.
Grain (deal): parent grain + deal_id.
Filter: tran_status IN ('Validated','Matured') only.
sv_power_volumes / sv_power_volumes_floating (EWE)¶
Snowflake SEMANTIC_VIEW wrappers over the deal-grain short-term desk facts. Designed for Snowflake Cortex Analyst natural-language queries. Both views join dim_deal_attribute to expose portfolio, commodity, buy_sell and tran_status as dimensions, and ship pre-built metrics (e.g. total_fixed_buy_mw, net_volume_mw, avg_power_index_price).
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
See Also¶
- dbt Documentation - Interactive lineage and column details
- Query Patterns - SQL examples
- Business Glossary - Term definitions