Credit Exposure Models

This page documents the EWE-specific credit exposure models for counterparty risk management.

Overview

Credit exposure models track potential financial loss from counterparty default. These models source from Endur's credit risk management module via UDSR exports.

Fact Tables

fct_credit_exposure

Daily credit exposure metrics by counterparty and exposure line.

Purpose: Monitor credit utilization against limits on a daily basis.

Grain: metric_date + exp_line_id + party

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 for this line
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

Key Metrics Explained:

• Current Exposure: What would be lost if counterparty defaulted today (MTM value)
• Performance Exposure: Expected exposure assuming counterparty continues to perform
• Potential Exposure (PEX): Worst-case future exposure based on statistical analysis
• Settlement Exposure: Exposure during settlement window

Example Queries:

-- Top counterparties by current exposure
SELECT
    pe.lentity_name AS counterparty,
    SUM(f.current_exposure) AS total_current_exposure,
    SUM(f.potential_exposure) AS total_potential_exposure,
    SUM(f.exposure_limit) AS total_limit
FROM fct_credit_exposure f
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
GROUP BY pe.lentity_name
ORDER BY total_current_exposure DESC
LIMIT 20

-- Credit utilization by exposure line
SELECT
    f.exp_line_id,
    pe.lentity_name AS counterparty,
    f.current_exposure,
    f.exposure_limit,
    ROUND(f.current_exposure / NULLIF(f.exposure_limit, 0) * 100, 2) AS utilization_pct
FROM fct_credit_exposure f
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 f.exposure_limit > 0
ORDER BY utilization_pct DESC

---

fct_credit_exposure_month

Monthly forward projections of credit exposure with high/low scenarios.

Purpose: Plan for future credit utilization, collateral requirements, and limit reviews.

Grain: metric_date + exp_line_id + party + effective_month

Column	Type	Description
metric_date	DATE	Calculation 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
legal_entity_id	INTEGER	Legal entity identifier
current_exposure	NUMBER(38,8)	Current exposure
potential_exposure	NUMBER(38,8)	Base potential exposure
potential_exposure_high	NUMBER(38,8)	High scenario PEX
potential_exposure_low	NUMBER(38,8)	Low scenario PEX
pot_performance_exposure	NUMBER(38,8)	Base performance exposure
pot_performance_exposure_high	NUMBER(38,8)	High scenario performance
pot_performance_exposure_low	NUMBER(38,8)	Low scenario performance

Scenario Explanations:

• High Scenario: Adverse market conditions (higher volatility)
• Low Scenario: Favorable market conditions (lower volatility)
• Base: Expected case scenario

Example Query:

-- 12-month exposure projection for a counterparty
SELECT
    f.effective_month,
    pe.lentity_name AS counterparty,
    f.potential_exposure_low AS pex_low,
    f.potential_exposure AS pex_base,
    f.potential_exposure_high AS pex_high
FROM fct_credit_exposure_month f
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 pe.lentity_name = 'Example Counterparty'
  AND f.effective_month BETWEEN CURRENT_DATE AND DATEADD('month', 12, CURRENT_DATE)
ORDER BY f.effective_month

---

fct_credit_pex_factor

PEX (Potential Exposure) factors by commodity.

Purpose: Understand commodity-specific volatility used in exposure calculations.

Grain: metric_date + commodity

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

Example Query:

-- Current PEX factors
SELECT
    metric_date,
    commodity,
    factor AS pex_factor
FROM fct_credit_pex_factor
WHERE metric_date = CURRENT_DATE - 1
ORDER BY commodity

-- PEX factor trend
SELECT
    metric_date,
    commodity,
    factor
FROM fct_credit_pex_factor
WHERE commodity = 'GAS'
  AND metric_date >= DATEADD('day', -30, CURRENT_DATE)
ORDER BY metric_date

---

Dimension Tables

dim_party_agreement

Party agreement attributes including netting terms.

Purpose: Track contractual netting arrangements that affect net exposure calculations.

Column	Type	Description
dim_party_agreement_attribute_id	INTEGER	Surrogate key
dim_party_agreement_attribute_key	VARCHAR	Hash 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
collateral_agreement_name	VARCHAR	Collateral agreement name
has_cross_commodity_netting	BOOLEAN	Cross-commodity netting

Netting Types:

• Close-out Netting: In default, all contracts terminate and settle net
• Payment Netting: Payment obligations on same date are netted
• Cross-Commodity Netting: Netting across different commodity types

Example Query:

-- Parties with netting agreements
SELECT
    party_agreement_name,
    has_close_out_netting,
    has_payment_netting,
    has_cross_commodity_netting,
    collateral_agreement_name
FROM dim_party_agreement
WHERE has_close_out_netting = TRUE
   OR has_payment_netting = TRUE
ORDER BY party_agreement_name

---

Data Pipeline

Source

Credit exposure data originates from Endur's credit risk management module:

1. Endur Simulation: Daily batch simulation calculates exposures
2. UDSR Export: Results exported as User-Defined Stored Results
3. Snowpipe Ingestion: Data loaded to Snowflake

Transformation Layers

sat_endur_sim_result_udsr_credit_exposures (full history)
    ↓
stg_endur_sim_result_udsr_credit_exposures (schema applied)
    ↓
pit_endur_credit_exposure (latest per metric_date)
    ↓
map_endur_exposure_party_attribute (party dimension linkage)
    ↓
fct_credit_exposure (customer-facing)

Key Logic

Simulation Selection: PIT layer selects the latest simulation run for each metric_date:

QUALIFY ROW_NUMBER() OVER (
    PARTITION BY metric_date, exp_line_id
    ORDER BY sim_run_id DESC, sim_version DESC
) = 1

Party Mapping: Exposures are linked to the party dimension via map_endur_exposure_party_attribute, which creates dim_party_external_bu_le_attribute_key.

---

EWE-Specific Logic

Legal Entity Exclusion

EWE has specific business rules where some exposures involving two legal entities are measured against only one. The mapping layer excludes legal_entity_id = 20684 for this purpose.

Exposure Line vs Counterparty

Exposures are tracked by exposure line, not just counterparty. A counterparty may have multiple exposure lines with different limits for different purposes.

---

Best Practices

1. Always filter by metric_date: Exposure data is date-specific
2. Use latest data: Typically metric_date = CURRENT_DATE - 1
3. Check exposure limits: Compare current exposure to limits
4. Consider netting: Net exposure may differ significantly from gross
5. Use projections for planning: Monthly projections help anticipate needs

---

Related Documentation

• Credit Exposure Concepts - Term definitions
• Query Patterns - Credit Exposure - SQL examples
• Business Glossary - Credit Terms - Terminology