Skip to content

Reference: Semantic Views

Two Snowflake semantic views wrap the deal-grain EWE wrappers for natural-language access via Snowflake Cortex Analyst.

Semantic view Wraps Use for
sv_power_volumes fct_ewe_volume_pwr_by_business_case_deal Fixed-price natural-language queries
sv_power_volumes_floating fct_ewe_volume_pwr_floating_by_business_case_deal Floating / index-priced natural-language queries

Both semantic views join dim_deal_attribute so portfolio, commodity, buy/sell, and tran_status are queryable as dimensions through Cortex Analyst.

sv_power_volumes (fixed price)

Path: dbt/models/semantic_views/sv_power_volumes.sql

Backing fact: fct_ewe_volume_pwr_by_business_case_deal — short-term desk fixed-price, deal-grain, EWE BU rules applied (ECC INTD BU / NORD POOL AS BU).

Pre-built metrics:

Metric Definition
total_fixed_buy_mw SUM(volume_buy_mw)
total_fixed_sell_mw SUM(volume_sell_mw)
net_volume_mw SUM(volume_buy_mw) - SUM(volume_sell_mw)
avg_buy_vwap_eur_mwh Volume-weighted average of buy_vwap_price_eur_per_mwh
avg_sell_vwap_eur_mwh Volume-weighted average of sell_vwap_price_eur_per_mwh

Pre-built dimensions (from dim_deal_attribute join):

  • portfolio
  • commodity
  • buy_sell
  • tran_status (will always be Validated or Matured given the upstream filter)
  • Plus all columns from the backing fact: delivery_date_utc, begin_utc, business_case, grid, deal_id.

Example Cortex Analyst prompts:

  • "Total buy MW for portfolio PWR_ST_PF_Netting last week"
  • "Net volume by business case for the past 30 days"
  • "Top 10 deals by buy volume yesterday on TenneT"

sv_power_volumes_floating (index price)

Path: dbt/models/semantic_views/sv_power_volumes_floating.sql

Backing fact: fct_ewe_volume_pwr_floating_by_business_case_deal — floating-price short-term desk view, instrument_sub_type = 'PWR Index' applied.

Pre-built metrics:

Metric Definition
total_floating_buy_mw SUM(volume_buy_mw)
total_floating_sell_mw SUM(volume_sell_mw)
net_floating_volume_mw SUM(volume_buy_mw) - SUM(volume_sell_mw)
avg_power_index_price Average of power_index_price_eur_per_mwh

Pre-built dimensions:

  • Backing fact columns: delivery_date_utc, begin_utc, business_case, grid, internal_business_unit, external_business_unit, power_index_name, deal_id.
  • From dim_deal_attribute: portfolio, commodity, buy_sell, tran_status.

Deployment caveat

Not directly feature-flag-gated

The semantic views ref() the deal-grain EWE wrappers. When time_series.15m is off, the wrappers are disabled, and the semantic views fail to compile.

On stacks where the flag is off, exclude them explicitly:

./run_dbt.sh build --exclude sv_power_volumes sv_power_volumes_floating

Or use a tag-based exclusion if your project tags them.

When to use semantic views vs raw views

Use case Use semantic view Use raw fact view
Natural-language Cortex Analyst query ✓ —
SQL analyst writing ad-hoc queries — ✓
Dashboard with pre-built metric definitions ✓ (if metrics match) —
Cross-BU / unfiltered controlling analysis — ✓ (use unfiltered or generic)
Need to drill below deal grain (multiple tran_versions, etc.) — ✓ (use raw fact)

The semantic views are convenience layers — they don't expose any data the underlying fact views don't already expose.