Resource Assessment¶

The performance database stores resource assessment data — pre-operational and operational studies that predict the long-term behaviour of a resource (wind speed, solar irradiance) and of the expected energy output for each SPE. These assessments are the statistical baseline for dashboards, reports, and all pxx (exceedance probability) time series used across the platform.

The main outputs consumed by external code are the pXX materialized views, refreshed weekly. If you only need to read the numbers, start with those — the table-level detail below is what feeds them.

Tables¶

`resource_assessments`¶

The catalog of individual assessment studies. One row per named study. Relevant columns:

name: human-readable study name (e.g. Echo PosOp 2025 - SDM).
description: free text.
resource_assessment_type_id → resource_assessment_types.
company_id → companies.

`resource_assessment_types`¶

Classification of assessments (e.g. Wind - Post Operational, Solar - Pre Operational).

`resource_types`¶

The physical resources being measured. Examples: wind_speed, solar_irradiance_poa, average_power. Relevant columns:

name: lowercase identifier.
display_name: human-readable name.
group_aggregation_type: SUM or AVERAGE. Defines how values combine across SPEs when computing a group result:
- SUM — values are additive (e.g. average_power).
- AVERAGE — values are averaged, weighted by SPE nominal power (e.g. wind_speed, solar_irradiance_poa).

`resource_assessment_values`¶

The core table. One row per (resource_assessment_id, spe_id, resource_type_id). Relevant columns:

p50: 12-element monthly array of the median expected value (one number per calendar month).
std_1month: 12-element monthly array of the 1-month standard deviation (range 0–1).
std_1day: 12-element monthly array of the 1-day standard deviation (range 0–1).
std_1year: scalar — 1-year standard deviation (range 0–1).
std_longterm: scalar — long-term standard deviation (range 0–1).
p50_24h: 12×24 array of hourly modulation patterns (months × hours).
reference_date: the January 1^st anchor for the assessment.
is_default: boolean — marks the default assessment for this (spe, resource_type). Enforced uniquely by a partial unique index on (spe_id, resource_type_id) WHERE is_default = TRUE, so code that needs "the default" can safely pick the one row without tie-breaking.

Note

The CHECK constraints on p50 / std_1month / std_1day claim a 12×1 2D shape but, due to NULL-tolerant CHECK semantics, they also accept 1D 12-element arrays. The actual stored shape is 1D. Consumers index as p50[month], not p50[month][1].

`resource_assessment_losses_types`¶

Named categories of losses used in assessments — Module Degradation, Blade Degradation, Asset Curtailment, Park Curtailment, Availability BoP, Electrical, etc.

`resource_assessment_losses_values`¶

Per (resource_assessment_id, loss_type_id, spe_id): monthly loss fractions and an optional per-year degradation factor.

value: 12-element monthly array of loss fractions (range -1 to 1). Already baked into the p50 above — it's stored for traceability but NOT re-applied by downstream queries.
yearly_degradation: optional 1D array of per-year degradation factors. In practice only the Module Degradation loss type carries a non-null value here; all others are NULL.

Materialized Views¶

All pXX time series are precomputed in a cascade of materialized views. They are refreshed weekly by the Airflow DAG pg-mv-refresher-slow.

Refresh chain (in order)¶

Text Only

mv_resource_assessment_pxx_spe_daily      ← base layer (reads raw tables)
    └── mv_resource_assessment_pxx_daily  ← per-group daily pXX
            ├── mv_resource_assessment_pxx_monthly      ← DATE_TRUNC + AVG
            │       ├── mv_resource_assessment_pxx_monthly_ytd   ← window over monthly
            │       └── mv_resource_assessment_pxx_monthly_12m   ← window over monthly
            ├── mv_resource_assessment_pxx_quarterly    ← DATE_TRUNC + AVG
            ├── mv_resource_assessment_pxx_yearly       ← DATE_TRUNC + AVG
            ├── mv_resource_assessment_pxx_daily_mtd    ← window over daily
            ├── mv_resource_assessment_pxx_daily_ytd    ← window over daily
            └── mv_resource_assessment_pxx_daily_12m    ← window over daily

Order matters. The DAG runs them top-down; changes to assessments surface only after the full cascade completes.

`mv_resource_assessment_pxx_spe_daily`¶

The foundation. One row per (spe_id, resource_type_id, date). Holds everything that depends only on the assessment + COD + degradation, so downstream MVs never have to redo that work:

spe_id, spe_name, resource_type_id, resource_type_name
group_aggregation_type: carried forward so the group-aggregation MV can pick SUM vs weighted AVG without re-joining resource_types.
nominal_power_mw: the SPE's nominal power, used as weight for AVERAGE aggregations.
date: daily grain.
p50_adj: p50[month] × degradation_factor(year) — already multiplied.
std_longterm, std_1year: scalar std values (same for every day of the year).
std_1month, std_1day: per-day scalars — the 12-element array has been resolved down to the month that date belongs to.

All expensive work (array unnesting, daily generate_series, degradation EXP(SUM(LN(...))) product, COD lookup through the heavy v_object_attributes view) happens once per refresh here. Every downstream MV is then a simple JOIN + aggregation.

Year range is fixed to [2018, 2030] in the MV DDL. Extending it requires editing the literals and rebuilding.

`mv_resource_assessment_pxx_daily`¶

Per-group daily pXX. Built directly as one set-based query over mv_resource_assessment_pxx_spe_daily — it does not call any plpgsql function. One row per (group_type, group, resource_type, evaluation_period, pxx, date).

Currently computes pxx ∈ {0.5, 0.9} × evaluation_period ∈ {longterm, 1year, 1month, 1day}. See "P50 is longterm-only" in Gotchas below.

Time-bucketed MVs (derived from daily)¶

mv_resource_assessment_pxx_monthly: DATE_TRUNC('month', date) + AVG(value).
mv_resource_assessment_pxx_quarterly: DATE_TRUNC('quarter', date) + AVG(value).
mv_resource_assessment_pxx_yearly: DATE_TRUNC('year', date) + AVG(value).

Rolling-window MVs (derived via window functions)¶

mv_resource_assessment_pxx_daily_mtd: cumulative average from the 1^st of the month to the current day.
mv_resource_assessment_pxx_daily_ytd: cumulative average from Jan 1 to the current day.
mv_resource_assessment_pxx_daily_12m: trailing 12-month average (daily grain).
mv_resource_assessment_pxx_monthly_ytd: cumulative average of monthly values within the year.
mv_resource_assessment_pxx_monthly_12m: trailing 12-month average over the monthly values.

MV columns¶

All of the output MVs share the same schema:

group_type_id / group_type_name: e.g. SPE, Geographical, or bespoke group types.
group_id / group_name: the specific group (for group_type_name = 'SPE', group_name is the SPE name itself).
resource_type_id / resource_type_name: e.g. wind_speed, average_power.
evaluation_period: longterm, 1year, 1month, 1day — which standard-deviation column is applied to p50.
pxx: 0.5 or 0.9 — the exceedance probability.
date: first day of the bucket at the MV's time resolution.
value: the pXX value in kWavg. For average_power this is power; for wind_speed / solar_irradiance_poa it is the resource itself (m/s, W/m², etc.) — see Gotchas.

How pXX is calculated¶

For a given (spe, resource_type, date, evaluation_period, pxx):

Text Only

p50_adj(spe, date)
    = p50[month_of(date)] × degradation_factor(spe, year_of(date))

pxx_value(spe, date, period, pxx)
    = p50_adj × ( 1 − z(pxx) × std(period, month_of(date)) )

where:

z(pxx) is the inverse standard-normal CDF (public.normsinv). z(0.5) = 0, z(0.9) ≈ 1.2816.
std(period, month) is one of std_longterm, std_1year, std_1month[month], std_1day[month] depending on evaluation_period.
degradation_factor is the product over loss-type rows with non-null yearly_degradation of (1 − yearly_degradation[year_index]), with year_index = year − COD_year + 1 clamped to the array length. Equals 1 when no loss row carries a yearly_degradation.

Group aggregation¶

Once per-SPE daily pXX values exist, aggregation across SPEs inside a group follows resource_types.group_aggregation_type:

SUM → plain SUM(pxx_val) over SPEs in the group.
AVERAGE → capacity-weighted average: SUM(pxx_val × nominal_power_mw) / SUM(nominal_power_mw).

Single-SPE "groups" reduce to the SPE's own value.

Main points / gotchas¶

Weekly refresh cadence. Values can be up to 7 days stale relative to the underlying assessment data. The pg-mv-refresher-slow DAG runs the full cascade. If you update an assessment and need the new values immediately, trigger the DAG (or REFRESH MATERIALIZED VIEW manually in the order shown above).
Default assessment uniqueness is enforced at the DB level. A partial unique index on (spe_id, resource_type_id) WHERE is_default = TRUE prevents two defaults for the same (spe, resource_type). Consumers can always safely treat "the default" as a single row.
Monthly value in resource_assessment_losses_values is already baked into p50 — it is NOT applied a second time. Only yearly_degradation layers on top of p50. If a new loss needs to affect p50, it must be applied when regenerating the assessment's p50, not at query time.
Degradation is applied only for average_power. Wind speed and solar irradiance do not degrade with module age, so their pXX is unchanged by yearly_degradation even if a loss row has one set. This is by design.
P50 is computed only for longterm. For any (resource, pxx=0.5) combination you will only find rows with evaluation_period = 'longterm'. This is because z(0.5) = 0, so the pxx formula collapses to p50 regardless of std — computing it for other periods would just duplicate rows.
Year range is fixed to [2018, 2030]. Extending requires updating mv_resource_assessment_pxx_spe_daily (and therefore the whole cascade).
COD filter. An SPE contributes rows only starting from the first full month after its commercial operation date (COD). If an SPE has no COD set in v_object_attributes, it will not appear in the MVs at all. Check COD first if an SPE is unexpectedly missing.
Refresh order is not optional. Building a downstream MV from a stale parent silently produces stale results with no warning. Always refresh top-down.
Wind speed / solar irradiance pXX is a resource forecast, not an energy forecast. The "value" column for those resource types is in the resource's natural units (m/s, W/m²). Only average_power pXX is directly a production forecast in kWavg.
Group membership for SPE-type groups is implicit. A group of type SPE has exactly one member — the SPE whose name matches the group name. All other group types expand through object_groups_spes_mapping. The daily MV handles both shapes; downstream consumers don't need to care.

Debugging¶

The base MV is directly queryable — great for spot-checks without running the whole pipeline:

SQL

SELECT *
FROM performance.mv_resource_assessment_pxx_spe_daily
WHERE spe_name = 'SDM1-VRN1'
  AND resource_type_name = 'wind_speed'
  AND date = '2026-01-15';

The output MVs support the same style of filter, e.g.:

SQL

SELECT "date", "value"
FROM performance.mv_resource_assessment_pxx_daily
WHERE group_name = 'Portfolio'
  AND resource_type_name = 'average_power'
  AND evaluation_period = 'longterm'
  AND pxx = 0.9
  AND "date" BETWEEN '2026-01-01' AND '2026-12-31'
ORDER BY "date";

If a reported number looks off, the usual order of investigation is:

Query mv_resource_assessment_pxx_spe_daily for the SPE-day and verify p50_adj and the relevant std_* column against the raw resource_assessment_values row.
If the base MV is correct but the daily MV is wrong, check that the group's SPE membership is complete (object_groups_spes_mapping) and that group_aggregation_type on the resource is what you expect.
If both look correct but the monthly/quarterly/yearly MV disagrees, make sure the cascade has been refreshed in order after the last assessment change.