Skip to content

Gap-Filling: Why Every (Combo, Day) Has 96 Rows

All customer-facing 15-minute power volume views guarantee exactly 96 rows per day per combination key, regardless of whether underlying trades exist for that day. This page explains why the contract exists, how it's implemented, and what it means for your queries.

The contract

For every (combo, delivery_date_utc) pair where the combo has ever appeared in the data:

  • The view emits 96 rows (one per 15-minute UTC period: 00:00, 00:15, …, 23:45).
  • Rows with no underlying trade data return 0 for all volume and price columns.
  • Period boundaries are continuous: each period's end_utc equals the next period's begin_utc.

This is enforced by two custom dbt tests on every customer-facing view:

Test What it asserts
period_coverage_96 Each (combo, delivery_date_utc) has exactly 96 rows. Detects missing periods.
period_continuity Each day's 96 rows span 00:00 → 23:45 UTC in unbroken 15-minute increments. Detects DST or array-index alignment errors.

The combo key varies by view — business_case + grid for the business-case grain, plus deal_id + dim_deal_attribute_id on deal-grain variants, plus BU and index name on floating variants.

What "0" means

In a gap-filled row, every numeric column is 0, not NULL:

delivery_date_utc | begin_utc           | grid    | business_case | volume_buy_mw | volume_sell_mw | buy_vwap_price_eur_per_mwh
2026-05-15        | 2026-05-15 02:15 UTC| TenneT  | Intraday      | 0             | 0              | 0
2026-05-15        | 2026-05-15 02:30 UTC| TenneT  | Intraday      | 45.2          | 0              | 87.3
2026-05-15        | 2026-05-15 02:45 UTC| TenneT  | Intraday      | 0             | 0              | 0

0 is gap data, not 'zero trading'

A 0 in volume_buy_mw can mean two things and the view does not distinguish them:

  1. The combo traded that day, just not in this 15-minute slot.
  2. There was no trade at all for this combo on this day.

If you care about the distinction, count rows with non-zero volume, or compare against a known-active combo list.

The same rule applies to buy_vwap_price_eur_per_mwh / sell_vwap_price_eur_per_mwh / power_index_price_eur_per_mwh — they're 0 on gap rows.

How it's implemented

The customer-facing view is never the storage layer. The data lives in a paired core_*__real incremental table; the view does the gap-fill on top.

Two-layer architecture

core_ewe_volume_pwr_by_business_case__real  ← INCREMENTAL TABLE (sparse 96-element arrays)
    │
    │ unroll arrays per period + cross-join dim_period
    ▼
fct_ewe_volume_pwr_by_business_case         ← VIEW (dense, 96 rows/day/combo)

The __real intermediate stores one row per (combo, delivery_date_utc) with a 96-element Snowflake ARRAY(NUMBER) for volumes and a parallel array for volume-weighted prices. Element [i] holds the value for period i (where i = minutes_since_midnight / 15, so [0] is 00:00-UTC, [95] is 23:45-UTC).

The view materialises the dense (96 row) form at query time via a CROSS JOIN against dim_period from numera-core, indexed into the array by period_idx:

-- Excerpt from fct_ewe_volume_pwr_by_business_case
with periods as (
    select
        start_date,
        start_date_time_utc,
        (datediff('minute', start_date::timestamp_ntz, start_date_time_utc::timestamp_ntz) / 15)::int as period_idx
    from {{ numera_core.ref('dim_period') }}
),
dim_combos as (
    select distinct business_case, power_control_area
    from {{ numera_core.ref('core_ewe_volume_pwr_by_business_case__real') }}
)
select
    dp.start_date as delivery_date_utc,
    ...
    NVL(SUM(IFF(f.buy_sell = 'Buy',  ABS(f.volume_array[dp.period_idx]), NULL)), 0) * 4 as volume_buy_mw,
    ...
from periods dp
cross join dim_combos dc
left join core_ewe_volume_pwr_by_business_case__real f
    on  f.delivery_date_utc  = dp.start_date
    and f.business_case      = dc.business_case
    and f.power_control_area = dc.power_control_area
group by dp.start_date, dp.start_date_time_utc, dp.period_idx, dc.business_case, dc.power_control_area

Three things make this work:

  1. dim_combos distinct-selects every combo the data has ever seen. This pins the combo dimension to actual values.
  2. cross join dim_combos pairs every period with every known combo — producing the dense grid.
  3. left join on __real brings in real volumes where they exist, leaves NULLs where they don't. NVL(…, 0) collapses NULLs to 0.

Why we can't materialise the view

We tried. The gap-filled views were promoted to materialised tables. The first-build query plan exploded:

  • fct_ewe_volume_pwr_by_business_case_unfiltered: ~60 M rows projected, ~80% zero-fill.
  • fct_ewe_volume_pwr_by_business_case: ~11.79 M rows projected, ~88% zero-fill.

First-build queries timed out and the deployment was cancelled mid-flight. The view-form contract is now stable: the sparse __real intermediate is materialised and physically scanned cheaply; the dense view-form is recomputed per query but is only ever computed for the date range the user asks for.

This is why filtering on delivery_date_utc or begin_utc is mandatory — without it, the view computes the dense grid for every active combo over the full dim_period range (2020-01-01 onward).

Where the same pattern lives

Layer View(s) Intermediate (storage)
EWE wrappers fct_ewe_volume_pwr_by_business_case[_deal][_unfiltered][_with_bu_unfiltered], fct_ewe_volume_pwr_floating_by_business_case[_deal], fct_volume_pwr_quarter_fixed_price[_deal] core_ewe_volume_pwr_*__real, core_volume_pwr_quarter_*__real
Numera-core generic fct_volume_pwr_15m[_deal], fct_volume_pwr_floating_15m[_deal] fct_volume_pwr_15m_agg, fct_volume_pwr_floating_15m_agg (the agg is the sparse 96-array store; the view does the unroll)

The gap-fill semantic is identical across both layers. Migrating from EWE views to numera-core views preserves the row count and 96-period contract.