How to Migrate from EWE Views to Numera-Core Views

This is the canonical playbook for moving an existing query from fct_ewe_volume_pwr_* to the generic fct_volume_pwr_15m* views.

Migration is active

EWE views remain available during the transition. The generic numera-core views produce row-for-row identical output when the EWE-specific filters are re-applied at the query layer. The deprecation timeline will be communicated separately.

Why migrate

The EWE wrappers embed short-term-desk-specific filters in the data layer. The generic views move those filters to query time, where they belong. Benefits:

• Cross-deployment reusability of the underlying fact layer.
• Direct access to surrogate-key columns (dim_deal_attribute_id, dim_party_*_id, …) for richer joins.
• Cleaner separation between "the data" and "what this team filters it down to."
• Alignment with the ADR-0015 star schema direction.

View-by-view mapping

EWE view	Generic numera-core equivalent	Filters you re-apply
fct_ewe_volume_pwr_by_business_case	fct_volume_pwr_15m	Pattern A
fct_ewe_volume_pwr_by_business_case_deal	fct_volume_pwr_15m_deal	Pattern B
fct_ewe_volume_pwr_floating_by_business_case	fct_volume_pwr_floating_15m	none — PWR Index filter built into the upstream agg
fct_ewe_volume_pwr_floating_by_business_case_deal	fct_volume_pwr_floating_15m_deal	none
fct_ewe_volume_pwr_by_business_case_unfiltered	fct_volume_pwr_15m	none — drop the BU clause entirely
fct_ewe_volume_pwr_by_business_case_deal_unfiltered	fct_volume_pwr_15m_deal	none
fct_ewe_volume_pwr_by_business_case_with_bu_unfiltered	fct_volume_pwr_15m + GROUP BY ... internal_business_unit, external_business_unit	Pattern C
fct_volume_pwr_quarter_fixed_price	fct_volume_pwr_15m + DATE_TRUNC('quarter', delivery_date_utc)	Pattern D
fct_volume_pwr_quarter_fixed_price_deal	fct_volume_pwr_15m_deal + DATE_TRUNC('quarter', ...)	Pattern D

Reusable filter patterns

Pattern A: short-term desk fixed-price BU rule

The business-case-grain wrapper applies an OR-clause: ECC/NORD POOL or EWE-TR-SHORT-TERM netting portfolio.

-- Apply this on top of fct_volume_pwr_15m_deal joined to dim_deal_attribute
-- (because external_portfolio lives on the deal attribute)
with deals_in_scope as (
    select da.dim_deal_attribute_id
    from {{ ref('dim_deal_attribute') }} da
    where da.external_business_unit in ('ECC INTD BU', 'NORD POOL AS BU')
       or (da.external_business_unit = 'EWE TR SHORT TERM BU'
           and da.external_portfolio = 'PWR_ST_PF_Netting')
)

Pattern B: deal-grain BU rule

Tighter than Pattern A — no portfolio carve-out.

with deals_in_scope as (
    select dim_deal_attribute_id
    from {{ ref('dim_deal_attribute') }}
    where external_business_unit in ('ECC INTD BU', 'NORD POOL AS BU')
)

Pattern C: surface BU columns on the generic view

The generic view exposes BU as surrogate keys, not name strings. Join the BU SCDs to materialise the names.

select
    f.delivery_date_utc,
    f.begin_utc,
    f.business_case,
    f.grid,
    pi.business_unit as internal_business_unit,
    pe.business_unit as external_business_unit,
    f.volume_buy_mw,
    f.volume_sell_mw,
    f.buy_vwap_price_eur_per_mwh,
    f.sell_vwap_price_eur_per_mwh
from {{ ref('fct_volume_pwr_15m') }} f
left join {{ ref('sat_party_internal_bu_le_attribute') }} pi
    on f.dim_party_internal_bu_le_attribute_id = pi.dim_party_internal_bu_le_attribute_id
left join {{ ref('sat_party_external_bu_le_attribute') }} pe
    on f.dim_party_external_bu_le_attribute_id = pe.dim_party_external_bu_le_attribute_id
where f.delivery_date_utc between '2026-04-01' and '2026-04-30'

Pattern D: quarterly bucket

Wrap the generic view with DATE_TRUNC to mirror the quarterly variants.

select
    date_trunc('quarter', f.delivery_date_utc) as delivery_quarter,
    f.business_case,
    f.grid,
    da.portfolio,
    sum(f.volume_buy_mw)  as total_buy_mw,
    sum(f.volume_sell_mw) as total_sell_mw
from {{ ref('fct_volume_pwr_15m') }} f
left join {{ ref('dim_deal_attribute') }} da
    on f.dim_deal_attribute_id = da.dim_deal_attribute_id
where f.delivery_date_utc >= date_trunc('quarter', current_date)
group by 1, 2, 3, 4

---

Before / After SQL

Each pair runs against the same date range. Output is row-for-row identical given the same date bounds and a matching combo key. Validate with the Validation harness at the end.

fct_ewe_volume_pwr_by_business_case → fct_volume_pwr_15m

Before (EWE wrapper)After (numera-core + Pattern A)

select
    delivery_date_utc,
    begin_utc,
    business_case,
    grid,
    volume_buy_mw,
    volume_sell_mw,
    buy_vwap_price_eur_per_mwh,
    sell_vwap_price_eur_per_mwh
from fct_ewe_volume_pwr_by_business_case
where delivery_date_utc between '2026-04-01' and '2026-04-30'

with deals_in_scope as (
    select dim_deal_attribute_id
    from dim_deal_attribute
    where 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')
)
select
    f.delivery_date_utc,
    f.begin_utc,
    f.business_case,
    f.grid,
    sum(f.volume_buy_mw)                                 as volume_buy_mw,
    sum(f.volume_sell_mw)                                as volume_sell_mw,
    sum(f.volume_buy_mw  * f.buy_vwap_price_eur_per_mwh)
        / nullif(sum(f.volume_buy_mw), 0)                as buy_vwap_price_eur_per_mwh,
    sum(f.volume_sell_mw * f.sell_vwap_price_eur_per_mwh)
        / nullif(sum(f.volume_sell_mw), 0)               as sell_vwap_price_eur_per_mwh
from fct_volume_pwr_15m f
join deals_in_scope d on f.dim_deal_attribute_id = d.dim_deal_attribute_id
where f.delivery_date_utc between '2026-04-01' and '2026-04-30'
group by 1, 2, 3, 4

The generic view is at deal-attribute grain after the BU filter; the GROUP BY collapses it back to the business-case grain the EWE wrapper produces.

fct_ewe_volume_pwr_by_business_case_deal → fct_volume_pwr_15m_deal

BeforeAfter (Pattern B)

select
    delivery_date_utc, begin_utc, deal_id, business_case, grid,
    volume_buy_mw, volume_sell_mw,
    buy_vwap_price_eur_per_mwh, sell_vwap_price_eur_per_mwh
from fct_ewe_volume_pwr_by_business_case_deal
where delivery_date_utc between '2026-04-01' and '2026-04-30'

with deals_in_scope as (
    select dim_deal_attribute_id
    from dim_deal_attribute
    where external_business_unit in ('ECC INTD BU', 'NORD POOL AS BU')
)
select
    f.delivery_date_utc, f.begin_utc, f.deal_id, f.business_case, f.grid,
    f.volume_buy_mw, f.volume_sell_mw,
    f.buy_vwap_price_eur_per_mwh, f.sell_vwap_price_eur_per_mwh
from fct_volume_pwr_15m_deal f
join deals_in_scope d on f.dim_deal_attribute_id = d.dim_deal_attribute_id
where f.delivery_date_utc between '2026-04-01' and '2026-04-30'

fct_ewe_volume_pwr_floating_by_business_case → fct_volume_pwr_floating_15m

The floating views need no BU re-filter — the instrument_sub_type = 'PWR Index' filter is structural and applied in the upstream agg.

BeforeAfter

select
    delivery_date_utc, begin_utc,
    business_case, grid,
    internal_business_unit, external_business_unit, power_index_name,
    volume_buy_mw, volume_sell_mw,
    power_index_price_eur_per_mwh
from fct_ewe_volume_pwr_floating_by_business_case
where delivery_date_utc between '2026-04-01' and '2026-04-30'

select
    delivery_date_utc, begin_utc,
    business_case, grid,
    internal_business_unit, external_business_unit, power_index_name,
    volume_buy_mw, volume_sell_mw,
    power_index_price_eur_per_mwh
from fct_volume_pwr_floating_15m
where delivery_date_utc between '2026-04-01' and '2026-04-30'

Pure column-for-column swap. Same for the _deal variant.

fct_ewe_volume_pwr_by_business_case_unfiltered → fct_volume_pwr_15m

The unfiltered controlling variant has no BU filter — the migration is a straight swap.

BeforeAfter

select
    delivery_date_utc, begin_utc, business_case, grid,
    volume_buy_mw, volume_sell_mw,
    buy_vwap_price_eur_per_mwh, sell_vwap_price_eur_per_mwh
from fct_ewe_volume_pwr_by_business_case_unfiltered
where delivery_date_utc between '2026-04-01' and '2026-04-30'

select
    delivery_date_utc, begin_utc, business_case, grid,
    sum(volume_buy_mw)                                 as volume_buy_mw,
    sum(volume_sell_mw)                                as volume_sell_mw,
    sum(volume_buy_mw  * buy_vwap_price_eur_per_mwh)
        / nullif(sum(volume_buy_mw), 0)                as buy_vwap_price_eur_per_mwh,
    sum(volume_sell_mw * sell_vwap_price_eur_per_mwh)
        / nullif(sum(volume_sell_mw), 0)               as sell_vwap_price_eur_per_mwh
from fct_volume_pwr_15m
where delivery_date_utc between '2026-04-01' and '2026-04-30'
group by 1, 2, 3, 4

The GROUP BY collapses across the deal-attribute grain — the unfiltered EWE wrapper aggregates over all dim_deal_attribute_id values, so the generic equivalent must too.

The deal-level variant (fct_ewe_volume_pwr_by_business_case_deal_unfiltered → fct_volume_pwr_15m_deal) is a pure column swap with no GROUP BY.

fct_ewe_volume_pwr_by_business_case_with_bu_unfiltered → fct_volume_pwr_15m + Pattern C

BeforeAfter (Pattern C)

select
    delivery_date_utc, begin_utc,
    business_case, grid,
    internal_business_unit, external_business_unit,
    volume_buy_mw, volume_sell_mw,
    buy_vwap_price_eur_per_mwh, sell_vwap_price_eur_per_mwh
from fct_ewe_volume_pwr_by_business_case_with_bu_unfiltered
where delivery_date_utc between '2026-04-01' and '2026-04-30'

select
    f.delivery_date_utc, f.begin_utc,
    f.business_case, f.grid,
    pi.business_unit as internal_business_unit,
    pe.business_unit as external_business_unit,
    sum(f.volume_buy_mw)                                 as volume_buy_mw,
    sum(f.volume_sell_mw)                                as volume_sell_mw,
    sum(f.volume_buy_mw  * f.buy_vwap_price_eur_per_mwh)
        / nullif(sum(f.volume_buy_mw), 0)                as buy_vwap_price_eur_per_mwh,
    sum(f.volume_sell_mw * f.sell_vwap_price_eur_per_mwh)
        / nullif(sum(f.volume_sell_mw), 0)               as sell_vwap_price_eur_per_mwh
from fct_volume_pwr_15m f
left join sat_party_internal_bu_le_attribute pi
    on f.dim_party_internal_bu_le_attribute_id = pi.dim_party_internal_bu_le_attribute_id
left join sat_party_external_bu_le_attribute pe
    on f.dim_party_external_bu_le_attribute_id = pe.dim_party_external_bu_le_attribute_id
where f.delivery_date_utc between '2026-04-01' and '2026-04-30'
group by 1, 2, 3, 4, 5, 6

fct_volume_pwr_quarter_fixed_price → fct_volume_pwr_15m + Pattern D

The quarterly variants retain dim_deal_attribute_id so consumers can join dim_deal_attribute. The generic view already exposes that surrogate key directly — no special handling needed.

BeforeAfter

select
    date_trunc('quarter', f.delivery_date_utc) as delivery_quarter,
    f.business_case,
    f.grid,
    da.portfolio,
    sum(f.volume_buy_mw)  as total_buy_mw,
    sum(f.volume_sell_mw) as total_sell_mw
from fct_volume_pwr_quarter_fixed_price f
left join dim_deal_attribute da
    on f.dim_deal_attribute_id = da.dim_deal_attribute_id
where f.delivery_date_utc >= date_trunc('quarter', current_date)
group by 1, 2, 3, 4

select
    date_trunc('quarter', f.delivery_date_utc) as delivery_quarter,
    f.business_case,
    f.grid,
    da.portfolio,
    sum(f.volume_buy_mw)  as total_buy_mw,
    sum(f.volume_sell_mw) as total_sell_mw
from fct_volume_pwr_15m f
left join dim_deal_attribute da
    on f.dim_deal_attribute_id = da.dim_deal_attribute_id
where f.delivery_date_utc >= date_trunc('quarter', current_date)
group by 1, 2, 3, 4

Only the view name changes. The deal-grain variant is the same pattern with fct_volume_pwr_15m_deal and deal_id added to the projection / GROUP BY.

---

Column name mapping

Most columns are identical across both layers:

Column	EWE	Numera-core	Notes
delivery_date_utc	✓	✓
begin_utc / end_utc	✓	✓
begin_cet / end_cet	✓	✓
business_case	✓	✓
grid	✓	✓
deal_id	✓ (deal grain)	✓ (deal grain)
volume_buy_mw / volume_sell_mw	✓	✓
buy_vwap_price_eur_per_mwh	✓ (fixed)	✓ (fixed)
sell_vwap_price_eur_per_mwh	✓ (fixed)	✓ (fixed)
power_index_price_eur_per_mwh	✓ (floating)	✓ (floating)
internal_business_unit / external_business_unit	✓ (floating, *_with_bu_*)	—	Join sat_party_*_bu_le_attribute to surface
power_index_name	✓ (floating)	✓ (floating)
dim_deal_attribute_id	only on *_deal variants	✓ on all	More accessible on the generic layer
dim_tran_info_id, dim_party_*_id, dim_point_of_*_location_id, dim_deal_financial_id	—	✓	New on the generic layer
volume_x_price	—	✓ (fixed)	Per-period volume_mwh × price in EUR. Watch units — see VWAP roll-up.

Output-shape promise

Across both layers:

• 96 rows per (combo, day) — guaranteed.
• Gap rows return 0 for all volume and price columns.
• tran_status IN ('Validated', 'Matured') applied upstream.
• VWAP semantics unchanged. When you re-aggregate across periods, weight by volume_buy_mw / volume_sell_mw (see Daily VWAP roll-up).

See Gap-filling for the full contract.

---

Validation harness

Before retiring a query against an EWE view, run both versions over the same date and compare:

with old_q as (
    select
        delivery_date_utc,
        sum(volume_buy_mw)  as buy_mw,
        sum(volume_sell_mw) as sell_mw,
        count(*)            as row_count,
        count(distinct (business_case || '|' || grid)) as combos
    from fct_ewe_volume_pwr_by_business_case
    where delivery_date_utc between '2026-04-01' and '2026-04-07'
    group by 1
),
new_q as (
    with deals_in_scope as (
        select dim_deal_attribute_id
        from dim_deal_attribute
        where 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')
    )
    select
        f.delivery_date_utc,
        sum(f.volume_buy_mw)  as buy_mw,
        sum(f.volume_sell_mw) as sell_mw,
        count(*)              as row_count,
        count(distinct (f.business_case || '|' || f.grid)) as combos
    from fct_volume_pwr_15m f
    join deals_in_scope d on f.dim_deal_attribute_id = d.dim_deal_attribute_id
    where f.delivery_date_utc between '2026-04-01' and '2026-04-07'
    group by 1
)
select
    coalesce(o.delivery_date_utc, n.delivery_date_utc) as delivery_date_utc,
    o.buy_mw       as old_buy_mw,
    n.buy_mw       as new_buy_mw,
    o.buy_mw - n.buy_mw   as buy_diff,
    o.sell_mw      as old_sell_mw,
    n.sell_mw      as new_sell_mw,
    o.sell_mw - n.sell_mw as sell_diff,
    o.row_count    as old_rows,
    n.row_count    as new_rows,
    o.combos       as old_combos,
    n.combos       as new_combos
from old_q o
full outer join new_q n using (delivery_date_utc)
order by 1

Expect: buy_diff and sell_diff are 0 (or within floating-point tolerance), row counts match per day, combo counts match. Any non-zero diff means the filter pattern doesn't yet match the EWE wrapper's filter exactly — investigate by joining dim_deal_attribute and listing the BU/portfolio combinations on both sides.

---

Timeline

EWE views remain available during the transition. Once the migration is complete and validated, deprecation will be communicated through the regular release notes channel. No automatic disablement is planned without prior notice.

Related

• Migrate Your First Query (tutorial) — hands-on, step-by-step walkthrough
• EWE views (reference)
• Numera-core views (reference)
• Join dimensional views — building richer filters on the generic layer
• EWE vs numera-core layers (explanation)