Skip to content

Architecture and Lineage

How a 15-minute power volume row gets from Endur to your query result.

End-to-end lineage

┌─── Endur source (raw schedules, deals, indices)
│
├─► core_endur_volume_deal_daily         (event-driven; one row per deal/day with 96-slot volume_shape)
├─► core_endur_volume_15m                (event-driven; aggregates deal-day shapes into 96-slot volume arrays per dim combo)
├─► core_endur_index_pricing_15m_daily   (table; 96-slot price_vector per index per day)
│
├─► fct_volume_15m                       (view; joins core_endur_volume_15m + dims; surfaces 96-slot volume + price arrays)
├─► fct_volume_15m_deal                  (view; deal grain)
│
├─► fct_volume_pwr_15m_agg               (incremental; pivots fct_volume_15m to BC+grid grain, retains arrays)
├─► fct_volume_pwr_floating_15m_agg      (incremental; same for floating, joins index pricing arrays)
│
├─► fct_volume_pwr_15m                   (view; unrolls fct_volume_pwr_15m_agg arrays → 96 rows/day; gap-fills)
├─► fct_volume_pwr_15m_deal              (view; deal grain)
├─► fct_volume_pwr_floating_15m          (view; floating)
├─► fct_volume_pwr_floating_15m_deal     (view; floating, deal grain)
│        ↑ generic, no EWE filters       │
│                                         │
└─► EWE wrapper layer (dbt/models/dimensional/):
    │
    ├── short_term_desk/
    │   ├── core_ewe_volume_pwr_by_business_case__real            (incremental; EWE BU/portfolio filters applied)
    │   ├── core_ewe_volume_pwr_by_business_case_deal__real
    │   ├── core_ewe_volume_pwr_floating_by_business_case__real
    │   ├── core_ewe_volume_pwr_floating_by_business_case_deal__real
    │   ├── fct_ewe_volume_pwr_by_business_case                   (view; unrolls __real arrays + gap-fills)
    │   ├── fct_ewe_volume_pwr_by_business_case_deal
    │   ├── fct_ewe_volume_pwr_floating_by_business_case
    │   └── fct_ewe_volume_pwr_floating_by_business_case_deal
    │
    ├── controlling/
    │   ├── core_ewe_volume_pwr_by_business_case_unfiltered__real
    │   ├── core_ewe_volume_pwr_by_business_case_deal_unfiltered__real
    │   ├── core_ewe_volume_pwr_by_business_case_with_bu_unfiltered__real
    │   ├── core_volume_pwr_quarter_fixed_price__real
    │   ├── core_volume_pwr_quarter_fixed_price_deal__real
    │   ├── fct_ewe_volume_pwr_by_business_case_unfiltered        (view)
    │   ├── fct_ewe_volume_pwr_by_business_case_deal_unfiltered
    │   ├── fct_ewe_volume_pwr_by_business_case_with_bu_unfiltered
    │   ├── fct_volume_pwr_quarter_fixed_price
    │   └── fct_volume_pwr_quarter_fixed_price_deal
    │
    └── semantic_views/
        ├── sv_power_volumes              (wraps fct_ewe_volume_pwr_by_business_case_deal)
        └── sv_power_volumes_floating     (wraps fct_ewe_volume_pwr_floating_by_business_case_deal)

Layer responsibilities

Endur source → core

The core_endur_* models translate raw Endur deal and pricing data into 96-slot daily vectors. Volume is captured as volume_shape (a 96-element array of slot volumes) per deal-day; index pricing as price_vector_transformed.

This layer is event-driven incremental — runs trigger off Endur changefeed events, not a cron clock.

core → fct_volume_15m

fct_volume_15m and fct_volume_15m_deal are thin views that surface the core arrays alongside dimensional surrogate keys (dim_deal_attribute_id, dim_point_of_*_location_id, dim_party_*_bu_le_attribute_id). At this layer the arrays are still 96-element vectors per (combo, day), not unrolled rows.

fct_volume_15m → fct_volume_pwr_*_agg

The *_agg models filter to power deals with a non-empty business case, join the per-deal financial price array, and aggregate to the customer-relevant grain (business case + grid + BU + index, depending on variant). They remain array-shaped — one row per (combo, delivery_date_utc) with 96-slot arrays, not 96 rows per combo per day.

These are incremental tables with a 7-day lookback to bound build cost.

fct_volume_pwr_*_agg → customer view (numera-core layer)

The customer-facing views (fct_volume_pwr_15m, fct_volume_pwr_floating_15m, and _deal variants) do the per-period unroll:

  1. Cross-join dim_period (96 periods × N days) with dim_combos (distinct combos from the agg).
  2. Left-join the agg on (combo, delivery_date_utc).
  3. Index into the volume and price arrays by period_idx = minutes_since_midnight / 15.
  4. Compute VWAP per period from the parallel volume and weighted-price arrays.

This is the gap-fill step that guarantees 96 rows per (combo, day). See Gap-filling for the SQL pattern.

Numera-core → EWE wrapper

The EWE wrapper layer recapitulates the agg-and-view pattern but with EWE-specific filters embedded:

  • Filtered short-term desk wrappers: apply external_business_unit IN ('ECC INTD BU', 'NORD POOL AS BU') + (EWE TR SHORT TERM BU + PWR_ST_PF_Netting) rules at the core_ewe_*__real layer.
  • Controlling unfiltered wrappers: omit the BU filter; otherwise identical to the filtered variant.
  • Quarterly wrappers: thin select * over their core_*__real intermediate; no separate unroll step.

The wrapper view-form gap-fill happens against core_ewe_*__real, mirroring the numera-core agg-and-view split.

Semantic views

sv_power_volumes and sv_power_volumes_floating are Snowflake semantic views that wrap the deal-grain EWE wrappers and define pre-built metrics for natural-language access via Cortex Analyst. They ref() the deal-grain wrappers, so when time_series.15m is off they fail to compile and must be excluded explicitly.

Why arrays, not rows?

Storing 96 rows per (combo, day) at every intermediate would multiply the row count by 96× at every layer. Snowflake arrays let the storage stay one row per (combo, day) and the row-explosion happen exactly once, at the customer view boundary, only for the date range the customer asks for. This was the design decision that made the model build at all.

Incremental strategy

The *_agg and core_*__real tables use a 7-day rolling window incremental strategy:

  • Unique key: delivery_date_utc (plus combo columns where the grain is finer).
  • Lookback: 7 days. Late-arriving corrections to Endur deals within the last 7 calendar days get picked up; older corrections require a full refresh.
  • Strategy: delete+insert over the 7-day window.

Feature flag

The entire 15m pipeline — core_endur_volume_15m, fct_volume_15m, the _agg tables, both customer-view layers, and the semantic views — is gated by the time_series.15m feature flag. See Feature flag & deployment.