Power Volume Models¶

This page documents the EWE-specific power volume models for short-term power trading analysis.

Overview¶

EWE's short-term power trading desk requires 15-minute granularity volume data for performance analysis. These models aggregate core volume data by business case and grid.

Key Concepts¶

15-Minute Granularity¶

Power markets operate on 15-minute intervals: - 96 periods per day (24 hours x 4 periods) - Period boundaries: :00, :15, :30, :45 - Time zones: Data available in both UTC and CET

Business Case¶

Business cases categorize trading activity by strategy or desk. Examples: - Intraday trading - Day-ahead optimization - Balancing positions

Grid / Control Area¶

German power control areas where electricity is delivered.

Models¶

fct_ewe_volume_pwr_by_business_case¶

Aggregated 15-minute power volumes by business case and grid.

Purpose: Analyze short-term power trading performance across strategies.

Grain: date + 15-minute period + business_case + grid

Column	Type	Description
`delivery_date_utc`	DATE	Delivery date (UTC)
`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	Trading strategy/desk
`volume_buy_mw`	NUMBER	Buy volume in MW
`volume_sell_mw`	NUMBER	Sell volume in MW
`buy_vwap_price_eur_per_mwh`	NUMBER	Buy VWAP (EUR/MWh)
`sell_vwap_price_eur_per_mwh`	NUMBER	Sell VWAP (EUR/MWh)

Aggregation Logic: - Groups across all deals - Filters to tran_status IN ('Validated', 'Matured') - Calculates VWAP (Volume-Weighted Average Price)

Example Queries:

-- Daily summary by business case
SELECT
    delivery_date_utc,
    business_case,
    SUM(volume_buy_mw) AS total_buy_mw,
    SUM(volume_sell_mw) AS total_sell_mw,
    SUM(volume_buy_mw) - SUM(volume_sell_mw) AS net_position_mw
FROM fct_ewe_volume_pwr_by_business_case
WHERE delivery_date_utc = CURRENT_DATE
GROUP BY delivery_date_utc, business_case
ORDER BY business_case

-- Hourly profile for a specific day
SELECT
    DATE_TRUNC('hour', begin_cet) AS hour_cet,
    business_case,
    SUM(volume_buy_mw) AS buy_mw,
    SUM(volume_sell_mw) AS sell_mw,
    SUM(volume_buy_mw) - SUM(volume_sell_mw) AS net_mw
FROM fct_ewe_volume_pwr_by_business_case
WHERE delivery_date_utc = CURRENT_DATE
GROUP BY DATE_TRUNC('hour', begin_cet), business_case
ORDER BY hour_cet, business_case

-- Average prices by business case
SELECT
    business_case,
    AVG(buy_vwap_price_eur_per_mwh) AS avg_buy_price,
    AVG(sell_vwap_price_eur_per_mwh) AS avg_sell_price,
    AVG(sell_vwap_price_eur_per_mwh) - AVG(buy_vwap_price_eur_per_mwh) AS avg_spread
FROM fct_ewe_volume_pwr_by_business_case
WHERE delivery_date_utc = CURRENT_DATE
  AND volume_buy_mw > 0
  AND volume_sell_mw > 0
GROUP BY business_case

fct_ewe_volume_pwr_by_business_case_deal¶

Same as above but retains individual deal information.

Purpose: Drill-down to deal-level performance within business cases.

Grain: date + 15-minute period + deal_id + business_case + grid

Additional Column: | Column | Type | Description | |--------|------|-------------| | deal_id | INTEGER | Individual deal identifier |

Example Query:

-- Top deals by volume for a business case
SELECT
    deal_id,
    business_case,
    SUM(volume_buy_mw) AS buy_mw,
    SUM(volume_sell_mw) AS sell_mw,
    AVG(buy_vwap_price_eur_per_mwh) AS avg_buy_price,
    AVG(sell_vwap_price_eur_per_mwh) AS avg_sell_price
FROM fct_ewe_volume_pwr_by_business_case_deal
WHERE delivery_date_utc = CURRENT_DATE
  AND business_case = 'Intraday'
GROUP BY deal_id, business_case
ORDER BY (SUM(volume_buy_mw) + SUM(volume_sell_mw)) DESC
LIMIT 20

Unfiltered Variants¶

For controlling and reporting purposes, unfiltered versions exist that include all business units (no external BU filter applied).

fct_ewe_volume_pwr_by_business_case_unfiltered¶

Same structure as fct_ewe_volume_pwr_by_business_case but includes all deals regardless of external business unit.

fct_ewe_volume_pwr_by_business_case_deal_unfiltered¶

Same structure as fct_ewe_volume_pwr_by_business_case_deal but includes all deals.

Use Case: Controlling department analysis that requires full visibility across all BUs.

Floating Power Volume Models¶

For index-linked power trades (instrument_sub_type = 'PWR Index'), floating variants provide volumes with the associated power index prices.

fct_ewe_volume_pwr_floating_by_business_case¶

15-minute power volumes for floating/index-linked trades, aggregated by business case.

Purpose: Analyze index-linked power trading with associated index prices.

Grain: date + 15-minute period + business_case + grid + power_index

Column	Type	Description
`delivery_date_utc`	DATE	Delivery date (UTC)
`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	Trading strategy/desk
`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 in MW
`volume_sell_mw`	NUMBER	Sell volume in MW
`power_index_price_eur_per_mwh`	NUMBER	Index price (EUR/MWh)

Filter Applied: Only includes instrument_sub_type = 'PWR Index' deals.

Example Query:

-- Floating volumes with index prices
SELECT
    delivery_date_utc,
    business_case,
    power_index_name,
    SUM(volume_buy_mw) AS total_buy_mw,
    SUM(volume_sell_mw) AS total_sell_mw,
    AVG(power_index_price_eur_per_mwh) AS avg_index_price
FROM fct_ewe_volume_pwr_floating_by_business_case
WHERE delivery_date_utc = CURRENT_DATE
GROUP BY delivery_date_utc, business_case, power_index_name
ORDER BY business_case

fct_ewe_volume_pwr_floating_by_business_case_deal¶

Same as above but retains individual deal information.

Additional Column: | Column | Type | Description | |--------|------|-------------| | deal_id | INTEGER | Individual deal identifier |

Data Pipeline¶

Source¶

Power volume models source from the core numera-core fct_volume_15m model:

fct_volume_15m (numera-core)
    ↓
fct_ewe_volume_pwr_by_business_case
fct_ewe_volume_pwr_by_business_case_deal

Transformation¶

Time conversion: UTC to CET timestamps
Status filter: Only 'Validated' and 'Matured' deals
Aggregation: Group by business_case, grid, time period
VWAP calculation: Volume-weighted average prices

Feature Flag¶

Power volume models are controlled by the time_series.15m feature flag.

When disabled: - Models are not built - Queries will fail with "relation does not exist"

To enable, set in dbt_project.yml:

vars:
  time_series.15m: true

Time Zone Handling¶

UTC vs CET¶

Data is provided in both time zones: - UTC columns: begin_utc, end_utc - Standard reference - CET columns: begin_cet, end_cet - Local market time

Daylight Saving Time¶

CET/CEST transitions: - Spring forward: 02:00 → 03:00 (one less period) - Fall back: 03:00 → 02:00 (one extra period)

Use UTC for consistent 96-period days, CET for market-aligned reporting.

Analysis Patterns¶

Net Position Over Time¶

-- Intraday net position profile
SELECT
    begin_cet,
    SUM(volume_buy_mw) - SUM(volume_sell_mw) AS net_mw
FROM fct_ewe_volume_pwr_by_business_case
WHERE delivery_date_utc = CURRENT_DATE
GROUP BY begin_cet
ORDER BY begin_cet

P&L Approximation¶

-- Simple P&L approximation (sell - buy)
SELECT
    delivery_date_utc,
    business_case,
    SUM(volume_sell_mw * sell_vwap_price_eur_per_mwh) AS sell_revenue,
    SUM(volume_buy_mw * buy_vwap_price_eur_per_mwh) AS buy_cost,
    SUM(volume_sell_mw * sell_vwap_price_eur_per_mwh) -
    SUM(volume_buy_mw * buy_vwap_price_eur_per_mwh) AS gross_pnl
FROM fct_ewe_volume_pwr_by_business_case
WHERE delivery_date_utc >= DATEADD('day', -7, CURRENT_DATE)
GROUP BY delivery_date_utc, business_case
ORDER BY delivery_date_utc DESC, business_case

Volume by Hour of Day¶

-- Average volume by hour across multiple days
SELECT
    EXTRACT(HOUR FROM begin_cet) AS hour_of_day,
    business_case,
    AVG(volume_buy_mw) AS avg_buy_mw,
    AVG(volume_sell_mw) AS avg_sell_mw
FROM fct_ewe_volume_pwr_by_business_case
WHERE delivery_date_utc >= DATEADD('day', -30, CURRENT_DATE)
GROUP BY EXTRACT(HOUR FROM begin_cet), business_case
ORDER BY hour_of_day, business_case

Best Practices¶

Use CET for reporting: Market hours align with CET
Use UTC for calculations: Consistent 96 periods per day
Filter by delivery_date_utc: Primary date filter for performance
Check for DST: Be aware of spring/fall transitions
Use VWAP for average prices: More accurate than simple average

Query Patterns - Power Volumes - SQL examples
Dimensional Models - Core volume models
EWE Extensions - Architecture overview