Skip to content

Tutorial: Migrate Your First Query

This tutorial walks you, hands-on, through migrating a typical short-term-desk query from the EWE wrapper view to the generic numera-core view. Plan for about 20 minutes.

By the end you'll have:

  • Run an existing query against fct_ewe_volume_pwr_by_business_case.
  • Observed that swapping to fct_volume_pwr_15m alone gives diverging output (no BU filter).
  • Re-applied the EWE BU filter explicitly and confirmed output matches.
  • Validated the migration with a side-by-side comparison query.

If you haven't run a basic query yet, do Getting Started first.

Step 1 — Establish a baseline

Pick a date with healthy short-term desk activity. Run the existing-style query:

select
    delivery_date_utc,
    sum(volume_buy_mw)  as total_buy_mw,
    sum(volume_sell_mw) as total_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
order by 1

Note the daily total_buy_mw, total_sell_mw, row_count, and combos. These are your migration baseline.

Step 2 — Naive swap (expect divergence)

Now run the same shape of query against the generic view, swapping the view name only:

select
    delivery_date_utc,
    sum(volume_buy_mw)  as total_buy_mw,
    sum(volume_sell_mw) as total_sell_mw,
    count(*)            as row_count
from fct_volume_pwr_15m
where delivery_date_utc between '2026-04-01' and '2026-04-07'
group by 1
order by 1

You'll see bigger numbers. The EWE wrapper applies a BU/portfolio filter at the data layer:

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')

The generic view doesn't apply this filter — it surfaces all BU activity. To get the baseline back, you have to add the filter yourself.

Step 3 — Re-apply the EWE filter

The external_business_unit and external_portfolio columns live on dim_deal_attribute. The generic view exposes dim_deal_attribute_id, so join through it.

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 total_buy_mw,
    sum(f.volume_sell_mw) as total_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
order by 1

This is Pattern A from the migration how-to.

Daily totals should now match the baseline. Combo counts should match. Row counts may differ slightly because the generic view is at deal-attribute grain (more granular than the EWE wrapper) — see Step 4.

Step 4 — Reconcile the grain difference

The EWE wrapper collapses to (business_case, grid, begin_utc). The generic view is at (business_case, grid, dim_deal_attribute_id, begin_utc) — a finer grain. To match the wrapper exactly, aggregate the generic view to the same grain:

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')
),
collapsed as (
    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
    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, 2, 3, 4
)
select
    delivery_date_utc,
    sum(volume_buy_mw)  as total_buy_mw,
    sum(volume_sell_mw) as total_sell_mw,
    count(*)            as row_count,
    count(distinct (business_case || '|' || grid)) as combos
from collapsed
group by 1
order by 1

Now row_count should match the EWE wrapper too — same combos × same number of periods.

Step 5 — Side-by-side validation

Use the validation harness:

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
    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')
    ),
    collapsed as (
        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
        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, 2, 3, 4
    )
    select
        delivery_date_utc,
        sum(volume_buy_mw)  as buy_mw,
        sum(volume_sell_mw) as sell_mw,
        count(*)            as row_count
    from collapsed
    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
from old_q o
full outer join new_q n using (delivery_date_utc)
order by 1

Expect buy_diff, sell_diff to be 0 (or within floating-point tolerance) on every day. If any row has a non-zero diff, the deal-scope filter doesn't perfectly match the EWE wrapper — check by listing the contributing BU/portfolio combinations on both sides.

Step 6 — Declare it validated

Once the harness returns clean, you can confidently retire the EWE-view dependency in that one query. Move to your next query and repeat. The migration how-to has Before/After pairs and reusable filter patterns for every variant — deal grain, floating, controlling unfiltered, quarterly.

What did you learn?

  • The generic view exposes the same data the EWE wrapper does, just without the BU/portfolio pre-filter.
  • dim_deal_attribute_id is the join point for deal-attribute filters (portfolio, instrument, BU, …).
  • The generic view is at a finer grain than the EWE wrapper; aggregate to collapse.
  • Validate each migration before retiring the wrapper dependency.

What's next